IIF logical function Vs CASE expression – SQL SERVER

Both IIF and CASE expressions are used to make some decision based on an expression and return one of the two values. While CASE expression is supported in all versions, IIF is supported from version 2012 onwards. Here is the performance comparison of these two.

Consider the following set of data with 10 millions rows. The column gender will have either 0 or 1.

01.create table #genders (gender bit)
02. 
03.insert into #genders (gender)
04.select
05.top 10000000
06.checksum(newid())%2 as gender_flag
07.from
08.sys.objects as so1 cross join
09.sys.objects as so2 cross join
10.sys.objects as so3 cross join
11.sys.objects as so4

The requirement is to return ‘Female’ if gender=0, else return ‘Male’

01.declare @gender varchar(6)
02. 
03.SET STATISTICS TIME ON
04. 
05.select
06.@gender=case when gender=0 then 'Female' else 'Male' end  from
07.#genders
08. 
09.select
10.@gender=iif(gender=0 ,'Female','Male'from
11.#genders
12.SET STATISTICS TIME OFF

Here is the result

 SQL Server Execution Times:
   CPU time = 1591 ms,  elapsed time = 1599 ms.

 SQL Server Execution Times:
   CPU time = 1607 ms,  elapsed time = 1601 ms.

As you see CASE expression is slightly faster than IIF logical function

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s