Use of CASE | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Use of CASE

Does the use of CASE degrade the performance of the query.
If CASE is not used for a large number of cases, I think no. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Why should CASE degrade performance? CASE is a very handy and useful keyword. In the majority of cases [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />] it helps avoiding otherwise pathetically hilarious programming constructs that REALLY negatively affect performance. <br />More people should learn to use it.<br /><br />Just my $0.02 cents<br /><br /><br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
I agree with Frank, CASE is far better than using IF… THEN in the code and we never had any issues with CASE specifically. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
It can degrade if used in the WHERE clause. In the SELECT clause I don’t know of any examples where it does though. It’s a big time and performance saver. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Hey derrick….you get around… In a WHERE Clause it would most likely cause a scan…. What about in an ORDER BY though? Brett :cool:
Tested CASE in WHERE and ORDER BY. In both cases it did index seek <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />
The bigger problem will be the recompiles or inefficiency of the plans caused by using the CASE in the WHERE or ORDER BY clauses. In the SELECT statement, CASE will not cause this.
MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
]]>