SQL Server IIF vs CASE

sql-server sql-server-2012 case iif
neophyte picture neophyte · Apr 3, 2014 · Viewed 117.6k times · Source

I recently came to know about the availability of IIF function in SQL Server 2012. I always use nested CASE in my queries. I want to know the exact purpose of the IIF statement and when should we prefer using IIF over CASE Statement in the query. I mostly use nested CASE in my queries.

Thanks for all the inputs.

Answer

Karl Kieninger picture Karl Kieninger · Apr 3, 2014

IIF is the same as CASE WHEN <Condition> THEN <true part> ELSE <false part> END. The query plan will be the same. It is, perhaps, "syntactical sugar" as initially implemented.

CASE is portable across all SQL platforms whereas IIF is SQL SERVER 2012+ specific.

Related questions

Using CHARINDEX and SUBSTRING in a CASE statement

I need to evaluate a field with a CASE statement. The field name is Commodity and is a varchar(255). The values differ and I need to extract a specific portion from it after a specific character. The character is a …

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More
Auto increment primary key in SQL Server Management Studio 2012

How do I auto increment the primary key in a SQL Server database table, I've had a look through the forum but can't see how. I've looked the the properties but can't see an option, I have seen an answer …

Read More