IIF in SQL Server: IF THEN Logic Compared With CASE

IIF in SQL Server is a short way to write IF THEN logic that has two outcomes. Most people reach for CASE instead and never try it. Both do the same job, and the difference is worth knowing.

What IIF Does

The function takes three arguments. They are a condition, the value for true, and the value for false. IIF in SQL Server arrived with the 2012 release. Two things keep many developers away from it. CASE works on every version and everyone knows it. IIF also supports only two outcomes, while CASE can list as many as you like.

The demo uses a small juice shop table. The goal is to label each order line. A unit price of 10 or more is a Good Value, and a lower price is a Single Digit. The last row, a free sample cup, has no price at all.

IF DB_ID(N'IifLogicDemo') IS NULL CREATE DATABASE IifLogicDemo;
GO
USE IifLogicDemo;
GO
DROP TABLE IF EXISTS dbo.JuiceOrderLines;
CREATE TABLE dbo.JuiceOrderLines (
    OrderLineID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ItemName    nvarchar(40) NOT NULL,
    UnitPrice   decimal(8,2) NULL,
    Quantity    int          NOT NULL
);
INSERT INTO dbo.JuiceOrderLines (ItemName, UnitPrice, Quantity)
VALUES (N'Orange juice', 4.50, 2), (N'Cold press green', 11.00, 1), (N'Mango smoothie', 9.99, 3),
       (N'Seasonal cleanse box', 42.00, 1), (N'Ginger shot', 10.00, 6), (N'Sample cup', NULL, 1);

SELECT OrderLineID, ItemName, UnitPrice,
       IIF(UnitPrice >= 10, N'Good Value', N'Single Digit') AS PriceLabel
FROM dbo.JuiceOrderLines
ORDER BY OrderLineID;

Here is the same query written with CASE.

SELECT OrderLineID, ItemName, UnitPrice,
       CASE WHEN UnitPrice >= 10 THEN N'Good Value' ELSE N'Single Digit' END AS PriceLabel
FROM dbo.JuiceOrderLines
ORDER BY OrderLineID;

Both queries return these six rows. Look at row 6 before you move on. It has no price, yet it carries the label Single Digit. The next section explains why.

IIF and CASE Share One Plan

It’s fair to ask whether IIF costs more than CASE. It doesn’t. SQL Server rewrites IIF as a CASE expression while it compiles the query. The plan for the IIF query shows a CASE WHEN expression, the same one the CASE query shows. The query below reads the plan hash of each statement from the plan cache. Equal hashes mean equal plans. Run each of the two queries above in its own batch first. A batch that holds both would make the lookup label both rows IIF.

SELECT CASE WHEN st.text LIKE N'%IIF(UnitPrice%' THEN N'IIF' ELSE N'CASE' END AS WrittenWith,
       qs.query_plan_hash AS PlanHash
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE N'%AS PriceLabel%' AND st.text LIKE N'%dbo.JuiceOrderLines%'
  AND st.text NOT LIKE N'%dm_exec_query_stats%'
ORDER BY WrittenWith;

The hash value will differ on your server. What matters is that the two rows match.

One more clue sits in the error messages. Ten nested IIF calls run. An eleventh level stops with Msg 125, which says case expressions can be nested only to level 10. The message talks about CASE because that is what the IIF became.

The NULL Trap

A comparison with NULL is never true. The test UnitPrice >= 10 on the free sample returns unknown, and unknown isn’t true, so the false branch runs. That is why row 6 got Single Digit. Both IIF and CASE behave this way. A missing price should not read as a low price, so test for NULL first.

With IIF, you nest a second IIF inside the false branch. With CASE, you add a WHEN. The CASE version below also adds a third tier, which is where the two forms part ways.

SELECT OrderLineID,
       IIF(UnitPrice IS NULL, N'No price', IIF(UnitPrice >= 10, N'Good Value', N'Single Digit')) AS NestedIif,
       CASE WHEN UnitPrice IS NULL THEN N'No price'
            WHEN UnitPrice >= 20 THEN N'Premium'
            WHEN UnitPrice >= 10 THEN N'Good Value'
            ELSE N'Single Digit' END AS FourWayCase
FROM dbo.JuiceOrderLines
ORDER BY OrderLineID;

The nested IIF handles NULL, but it can’t add a Premium tier without another level of nesting. The CASE version adds a tier with one more WHEN line. You can nest IIF to imitate CASE, and I’d still choose CASE once a third outcome appears. The reading order is clearer.

Data Types in the Result

IIF returns one type, and it picks the type from the two result values by data type precedence. An integer and a decimal give a decimal. If the branch that runs holds text that can’t convert to the winning type, the statement fails. The first block below shows the winning type. The second block forces the text branch to run, and the message that follows is its result.

SELECT IIF(1 = 1, 1, 2.5) AS Result, SQL_VARIANT_PROPERTY(IIF(1 = 1, 1, 2.5), 'BaseType') AS ResultType;

SELECT IIF(1 = 0, 1, 'text') AS Result;

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'text' to data type int.

The 1 turned into 1.0 in the first result, because the decimal won. In the second, the word text can’t become an integer, so the statement stops. Keep both outcomes of an IIF in the same type family.

Keep IIF Out of the WHERE Clause

The real performance question is a function in a predicate. It matters for IIF and for CASE alike. The script below adds 20,000 rows and an index on UnitPrice. Then it counts the lines with a price of 49 or more, three ways. The first states the condition directly. The other two wrap it in IIF and in CASE.

SET NOCOUNT ON;
INSERT INTO dbo.JuiceOrderLines (ItemName, UnitPrice, Quantity)
SELECT N'Bulk item ' + CAST(n AS nvarchar(10)), CAST((n % 5000) / 100.0 AS decimal(8,2)) + 0.01, 1
FROM (SELECT TOP (20000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS x;
CREATE INDEX IX_JuiceOrderLines_UnitPrice ON dbo.JuiceOrderLines (UnitPrice) INCLUDE (ItemName);
GO
SET STATISTICS IO ON;
SELECT COUNT(*) AS DirectFilter FROM dbo.JuiceOrderLines WHERE UnitPrice >= 49;
SELECT COUNT(*) AS IifFilter FROM dbo.JuiceOrderLines WHERE IIF(UnitPrice >= 49, 1, 0) = 1;
SELECT COUNT(*) AS CaseFilter FROM dbo.JuiceOrderLines WHERE CASE WHEN UnitPrice >= 49 THEN 1 ELSE 0 END = 1;
SET STATISTICS IO OFF;

All three return 404 rows. The direct comparison seeks into the index and reads 5 pages. IIF and CASE both scan the whole index and read 121 pages. SQL Server can’t turn the wrapped test into a range on the column. Put the plain comparison in WHERE. Use IIF and CASE in the SELECT list, where they label rows after the filtering is done.

IIF in SQL Server or CASE: Which One Should You Use?

You could argue that IIF adds nothing, since CASE does everything it does. That’s true for results and for performance. The only gain is brevity. For a single yes or no choice, IIF reads like a spreadsheet formula and fits on one line. I use it there. I switch to CASE for a third outcome, a NULL branch or a long condition. Pick one rule for your team and keep to it.

What to Remember

IIF in SQL Server is CASE with two outcomes. The plan is the same, and so is the NULL behavior: an unknown condition takes the false branch. Test for NULL first when a missing value matters.

Keep both outcomes in one type family, and keep the whole expression out of WHERE. When you finish, run the cleanup script to drop the demo database.

USE master;
GO
IF DB_ID(N'IifLogicDemo') IS NOT NULL
BEGIN
    ALTER DATABASE IifLogicDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE IifLogicDemo;
END;

IIF is not a new kind of logic, it is CASE in a shorter coat.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.