108

I recently came to know about the availability of IIF function in SQL Server 2012. I always use nested CASE in my queries. I want to know the exact purpose of the IIF statement and when should we prefer using IIF over CASE Statement in the query. I mostly use nested CASE in my queries.

Thanks for all the inputs.

Vadim Ovchinnikov
  • 13,327
  • 5
  • 62
  • 90
neophyte
  • 1,726
  • 3
  • 15
  • 21
  • 9
    IIF is just a shorthand for a simple CASE statement. It is even translated into a case statement for query optimization and execution: http://technet.microsoft.com/en-us/library/hh213574.aspx – Sam DeHaan Apr 03 '14 at 13:33
  • 2
    It's a shame the shorthand isn't part of the standard (what is it these days? ANSISQL?) as it's much easier to read. But then T-SQL has always been a bit..."wordy". – Neil Barnwell Feb 21 '17 at 12:40

2 Answers2

169

IIF is the same as CASE WHEN <Condition> THEN <true part> ELSE <false part> END. The query plan will be the same. It is, perhaps, "syntactical sugar" as initially implemented.

CASE is portable across all SQL platforms whereas IIF is SQL SERVER 2012+ specific.

Karl Kieninger
  • 8,841
  • 2
  • 33
  • 49
25

IIF is a non-standard T-SQL function. It was added to SQL SERVER 2012, so that Access could migrate to SQL Server without refactoring the IIF's to CASE before hand. Once the Access db is fully migrated into SQL Server, you can refactor.

user2505560
  • 251
  • 3
  • 2