0

Looking at other questions here on SO and google seems to indicate MS Sql Server does not guarantee to short circuit the expressions in the WHERE clause.

Does that mean that a WHERE expression like this this one cannot be trusted to work...?

... WHERE (@include_voided = 1 OR mytable.void = 0) AND ...

I frequently use this kind of expression in my stored procedures. Having researched short-circuiting for another reason I am now wondering if this should be replaced with ...

... WHERE mytable.void = case when @include_voided=1 then mytable.void else 0 END

...as all of the articles seem to indicate that CASE statements are the only ones guaranteed to short-circuit.

I am hoping that the first expression is fine just because it is more readable and easier to type.

Community
  • 1
  • 1
Seth Spearman
  • 6,710
  • 16
  • 60
  • 105
  • Btw, you'll get different results on `@include_voided = NULL`. Also there is no way you can make these search conditions sargable (i.e. no way for index seek here) so use whichever is more convenient to write. – wqw Oct 18 '16 at 19:50

2 Answers2

3

Whether or not SQL Server short circuits your particular expression isn't relevant here, it will generate the correct result either way. In this case, short circuiting is merely an optimization.

And as for optimizations, it's up to how your index is built. These are (ideally) index seeks.

Blindy
  • 65,249
  • 10
  • 91
  • 131
2

Your question really makes no sense at all. Your case statement is actually more complicated than the original or. What does the original or have to do to evaluate both expressions? Something like:

  • Compare a parameter to 0
  • Fetch a value from the record
  • Compare it to 0

Only one of these really has any time associated with it -- fetching the value from the record probably involves multiple hardware operations.

What does the case need to do:

  • Fetch a value from the record.
  • Compare a parameter to 1
  • Compare the value to 0

I don't see how this is simpler in any way.

In any case, your real problem is not such a micro-optimization. The real performance issue is whether or not the SQL engine can use indexes for the query. The use of or makes indexes problematic. For this reason, you might consider dynamic SQL, if performance is a main concern and you have appropriate indexes for the different possible conditions.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786