IIF function – SQL Server Denali

IIF function is new to SQL Server Denali however if you worked with MS Access then you are familiar with this function. So if you are upgrading a database from Access to SQL Server Denali you don’t need to worry about rewriting the IIF function queries. IIF function will evaluate the expression and it will return one value out of two input values. This function is similar to CASE function however you can evaluate the expression for two values and not more than that.

Syntax for the command is below

IIF ( boolean_expression, true_value, false_value )

Let’s try it with an example. I’m going to select Sex column from the table and going to check the value for M as Male and F as Female, however if the input has more than two values then it will return the second result

IIF function with 2 values only

DECLARE @details TABLE(name VARCHAR(50), Sex CHAR(1) NOT NULL)
INSERT @details VALUES('Sagar','M'),('Deepak','M'),('Sugesh','M')
    ,('Uma','F'),('Mohana','F')
SELECT name,IIF(Sex = 'M', 'Male','Female') AS 'Sex' FROM @details

IIF function with more than 2 values

DECLARE @details TABLE(name VARCHAR(50), Sex CHAR(1))
INSERT @details VALUES('Sagar','M'),('Deepak','M'),('Sugesh','M')
    ,('Uma','F'),('Mohana','F'),('Arjun',NULL)
SELECT name,IIF(Sex = 'M', 'Male','Female') AS 'Sex' FROM @details

From the highlighted values you can see that if the column has more than 3 values then it’s returning the second result

IIF and CASE function comparison

DECLARE @details TABLE(name VARCHAR(50), Sex CHAR(1) NOT NULL)
INSERT @details VALUES('Sagar','M'),('Deepak','M'),('Sugesh','M')
    ,('Uma','F'),('Mohana','F')
--IIF function
SELECT name,IIF(Sex = 'M', 'Male','Female') AS 'Sex' FROM @details
--CASE function
SELECT name,CASE SEX WHEN 'M' THEN 'Male'
    ELSE 'Female' END AS 'Sex' FROM @details

I couldn’t find much difference in using the functions, both result in same plan