CODEWITHSUNDEEP
sql-serversql-server-2012case
Dwight T
Dwight T

Reputation: 1487

Why CASE statement in Where clause for MS Sql server

Doing some performance tuning on a 3rd party vendor system in MS SQL Server 2012.

They do a lot of where clauses like this:

WHERE 
(
   CASE
       WHEN  @searchID = '' THEN 1
       WHEN ((C.SAPCustomerNumber = @searchID ) OR (C.SAPCustomerNumber = @SapID)) THEN 1
    ELSE 0
    END = 1
                )

Other than confusing the query plan, what advantage if any would there be to Case 1 or 0 in the where clause?

Thanks

Upvotes: 2

Views: 805

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

The most common, by far, cause for this sort of code to appear is someone who is new to SQL, CASE expressions or both. They somehow become fixated on CASE and decide that it should be used for all conditional evaluations.

This is usually a mistake and can be replaced with simpler boolean logical, as @Bogdan suggested in the comments:

((C.SAPCustomerNumber = @searchID ) OR (C.SAPCustomerNumber = @SapID) OR (@searchID = '' ))

The second reason this can be done is if someone is attempting to enforce the order of evaluation of predicates1. CASE is documented to evaluate its WHEN conditions in order (provided that they are scalar expressions, not aggregates). I'd seriously not recommend that anyone actually writes code like this though - it's easily mistaken for the first form instead. And even if a particular evaluation order is best today, with today's data, who's to say whether it will still be correct tomorrow, or in a month or years time?

1SQL does not guarantee any evaluation order for WHERE clause predicates in general, nor any form of short-circuiting evaluation. The optimizer is generally free to re-order predicates - both within the WHERE clause and in JOIN/ON clauses - to try to achieve the overall result as cheaply as possible. You shouldn't, generally, try to prevent it from doing so.

If it's not picking the most efficient plan, this is far better fixed by updating/creating indexes and statistics or actually forcing a specific plan, rather than using CASE.

Upvotes: 4

Related Questions