Software Vendor Mistakes With SQL Server: IF Branching In Stored Procedures

What Goes Wrong

I still see people calling into this trap when writing stored procedures. What no one seems to realize until it’s too late data grows past a toy data base size, is that SQL Server’s query optimizer doesn’t respect IF branches as boundaries in the way you’d expect.

Sure, the control-flow logic boundaries are respected, but the big surprise to most people comes from how the optimizer treats query plans in IF branches: It compiles them all no matter what.

In the video below, I’ll show you exactly what that means, and how it can really screw up performance. This video is a small part of my paid training offering, and if you enjoy it, there’s a link at the end of the post to get 75% off.

IF Branching Video!

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

3 thoughts on “Software Vendor Mistakes With SQL Server: IF Branching In Stored Procedures

  1. Very nice work!. I have 2 questions:

    1) I heard another trick: create dummy variables on top of the stored procedure which gets the value of the parameters. Instead of “WHERE xx = @parameter” it will be “WHERE xx = @var” is this a good replacement for dynamic SQL?
    2) On which SQL version are you running these tests? I thought these “issues” were fixed a little in SQL2019

      1. sorry to hurt your feelings. About question 1, I forgot to add “a friend of my said …”

Comments are closed.