IIF vs CASE: I have the compiler's receipt

This is the shortest question in this series, and it deserves the shortest honest answer anyone can give about SQL Server performance: IIF and CASE are the same thing. Not “roughly the same.” The same. But “trust me” is exactly the kind of claim this blog exists to replace with evidence, so: receipts first, then the benchmark anyway.

The receipt

Take both spellings of the same logic:

IIF(n1 % 2 = 0, s1, s2)

CASE WHEN n1 % 2 = 0 THEN s1 ELSE s2 END

Run them, then pull the compiled scalar expression out of the cached query plan (sys.dm_exec_query_plan, the ScalarString attribute — the harness script does this automatically). What the engine compiled for the IIF:

CASE WHEN [t].[n1]%(2)=(0) THEN [t].[s1] ELSE [t].[s2] END

And for the hand-written CASE:

CASE WHEN [t].[n1]%(2)=(0) THEN [t].[s1] ELSE [t].[s2] END

Character for character identical. IIF is parse-time syntactic sugar — the documentation says so, and the plan proves it. By the time anything executes, there is no IIF; there is only CASE. Which means any measured “difference” between them can only ever be benchmark noise — a useful calibration check for the harness, in fact, because if I had measured a difference, the harness would be lying.

The benchmark agrees (as it must)

Per-row evaluation over a 200,000-row scan, ns/row net of the scan baseline, minimum of eight interleaved passes (methodology):

Logic	IIF	CASE
2-way branch	68.5 ns/row	66.2 ns/row
4-way (nested IIF / searched CASE)	107.6 ns/row	109.9 ns/row

Differences of ~2 ns/row, inside the trial-to-trial spread, alternating sign — that’s what a tie looks like. As a bonus, the simple form CASE n1 % 4 WHEN 0 THEN … END came in at 108.7 ns/row — indistinguishable from the searched form, because it too is just sugar: the plan shows it expanded with the test expression repeated in every arm. One family, three spellings.

So it’s style. Here’s my style advice

• For a single two-way branch, IIF is fine and arguably reads better: IIF(qty > 0, 'in stock', 'sold out') is hard to misread. It’s also what Access, Excel, and VB refugees expect — that’s literally why it was added in SQL Server 2012.
• The moment logic nests, IIF turns hostile. Compare the four-way branch from the benchmark:

IIF(n1 % 4 = 0, s1, IIF(n1 % 4 = 1, s2, IIF(n1 % 4 = 2, s3, s4)))

CASE WHEN n1 % 4 = 0 THEN s1
     WHEN n1 % 4 = 1 THEN s2
     WHEN n1 % 4 = 2 THEN s3
     ELSE s4
END

Same compiled expression, same nanoseconds — but one of them you can read at 2 a.m. during an incident, and one you have to parse with your fingers matching parentheses. Two or more conditions: write CASE.

• CASE is the portable one (ANSI standard; IIF is T-SQL/Access dialect), so shared codebases and migration-prone code lean CASE anyway.
• One inherited behavior to remember in both spellings: branches are evaluated lazily, but don’t rely on that as a guard with aggregates or subqueries — rewrites can evaluate more than you expect. That caution applies equally to IIF and CASE because — say it with me — they’re the same thing.

The meta-point

This argument shows up in code reviews as a performance debate, and it has a performance answer: zero, provably, by construction. When a “which is faster” question is really a “which do we find clearer” question, the kindest thing you can do for your team is produce the receipt once and move the discussion to where it belongs — readability. Consider this post that receipt.

---

Part of a series of measured answers to small T-SQL questions: + vs CONCAT, ISNULL vs COALESCE, SET vs SELECT vs DECLARE, temp-table cleanup. Methodology + harness: how to benchmark a microsecond.