SQL SERVER – Performing IF…THEN In T-SQL With IIF Expression

Today, we are going to see one of the most underutilized features of SQL Server IIF. In SQL Server we can performance IF…THEN logic in T-SQL with IIF Expression but I hardly see it being used at most of the organization.

I believe there are two reasons for this underutilization of feature.

Reason 1: People are extremely comfortable with the CASE expression which works in all the different versions of the SQL Server and also very familiar with its usage.

Reason 2: The second most reasons is that IIF only supports two results when the conditions are met and when the conditions are not met. Whereas a CASE expression supports multiple conditions and results. The extendibility of the CASE expression is often preferred over IIF. You can always nest multiple IIF to simulate CASE expression but in that case, I prefer to use CASE expression over IIF.

Now let us see a simple example where how we can use IIF statement to achieve IF…THEN in T-SQL.

Let us assume that we have a situation where we want to list all the OrderLineID where the UnitPrice of the table is over USD 10 as a Good Value and anything which is below USD 10 as a Single Digit. You can achieve the same with the help of IIF as demonstrated here.

SELECT [OrderLineID],
IIF(UnitPrice >= 10, 'Good Value', 'Single Digit') Lable
FROM [WideWorldImporters].[Sales].[OrderLines]

You can convert the same to CASE expression as follows:

SELECT [OrderLineID],
CASE WHEN UnitPrice >= 10
THEN 'Good Value'
ELSE 'Single Digit' END Lable
FROM [WideWorldImporters].[Sales].[OrderLines]

If you are worried about the performance, performance of both of them is absolutely the same. You can see that in the execution plan comparison here.

Well, that’s it! Use the IIF or CASE expression as you prefer.

Reference: Pinal Dave (https://blog.sqlauthority.com)