- Category: SQL Server
CASE expression is supported in all versions where as IIF is supported for SQL 2012 onwards.
IIF is internally converted to CASE expression so CASE expression is slightly faster than IIF but the difference may be of few nano seconds.
I recommend CASE expression.
Hope it helps.
The CASE is slightly faster than IIF.
IF is a control of flow statement; it indicates which T-SQL statement to evaluate next, based on a condition.
CASE is a function -- it simply returns a value.
DECLARE @a int
IF datepart( m, getdate() ) = 1
set @a = 1
ELSE IF datepart( m, getdate() ) = 2
set @a = 2
set @a = 0
SET @a = CASE datepart( m, getdate() )
WHEN 1 THEN 1
WHEN 2 THEN 2
The main difference between the two is that if you use 12 nested ifs, you'll be doing 12 selects, whereas you'll only do one select with the case statement.
Try displaying the estimated execution plan for the above code in Query Analyzer and you'll see the difference.