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 on 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.