SQL Server Performance

Use of CASE

Discussion in 'T-SQL Performance Tuning for Developers' started by nitingulati7, May 12, 2004.

  1. nitingulati7 New Member

    Does the use of CASE degrade the performance of the query.
  2. Luis Martin Moderator

    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.

  3. FrankKalis Moderator

    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 />
  4. satya Moderator

    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.
  5. derrickleggett New Member

    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
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  6. x002548 New Member

    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:
  7. bambola New Member

    Tested CASE in WHERE and ORDER BY. In both cases it did index seek <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  8. derrickleggett New Member

    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
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page