SQL WHERE clauses: Avoid CASE, use Boolean logic As some of you may know, I recommend to avoid using CASE expressions in the WHERE clause of a query to express conditional logic. I prefer to have everything translated to simple ANDs, ORs and NOTs to keep things a) portable, b) easier to read and c) efficient. ------------ Apparently Coalesce() in the Where clause removes the queries ability to use an index for evaluating that column. Makes the code a tad bit messier but really good to know when to use boolean logic.
A collection of problems and discoveries usually related to the tech industry in some way.
Thursday, September 27, 2007
SQL Where clauses: Avoid Case, use Boolean logic
SQL WHERE clauses: Avoid CASE, use Boolean logic As some of you may know, I recommend to avoid using CASE expressions in the WHERE clause of a query to express conditional logic. I prefer to have everything translated to simple ANDs, ORs and NOTs to keep things a) portable, b) easier to read and c) efficient. ------------ Apparently Coalesce() in the Where clause removes the queries ability to use an index for evaluating that column. Makes the code a tad bit messier but really good to know when to use boolean logic. 
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment