SQL SERVER – Denali – New Functions and Shorthand for CASE Statement

This blog post is written in response to the T-SQL Tuesday post of Data Presentation. This is a very interesting subject. I recently started to write about Denali Logical and Comparison functions. Many readers asked me if these new functions replace the CASE statement. They are really shorthand for CASE statement logic.

I really enjoyed writing about new functions, but there was one question kept cropping up – is the CASE statement being replaced with this new functions. The answer is NO. New functions that are introduced are just shorthand for the CASE statement, and they are not replacing anything.

1) TRY_PARSE() is not replacing the CASE statement, infect it is not. However, it can be smartly used along with the CASE statement. Here is the example in which it is used along with the CASE statement.

SELECT
CASE WHEN TRY_PARSE('A100.000' AS INT) IS NULL
THEN 'Error In Result'
ELSE 'No Error'--TRY_PARSE('A100.000' AS INT)
END AS ValueInt;

2) IIF() is nothing but a shorthand to CASE statement. Here is a quick example:

SELECT IIF ( -1 < 1, 'TRUE', 'FALSE' ) AS Result;

Now, IIF is simulated by CASE statement

SELECT CASE
WHEN -1 < 1 THEN 'TRUE'
ELSE 'FALSE' END AS Result;

The matter of the fact is that when you see the execution plan of IIF it also shows that internally it is using the CASE statement only.

3) CHOOSE() is also a shorthand to CASE statement. Here is a quick example:

USE AdventureWorks2008R2
GO
SELECT A.ModifiedDate,
DATEPART(dw, A.ModifiedDate) DayofWeek,
DATENAME(dw, A.ModifiedDate) DayofWeek,
CHOOSE(DATEPART(dw, A.ModifiedDate), 'WEEKEND','Weekday',
'Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON SP.StateProvinceID = A.StateProvinceID
GO

The above statement is re-written using the CASE statement.

USE AdventureWorks2008R2
GO
SELECT A.ModifiedDate,
DATEPART(dw, A.ModifiedDate) DayofWeek,
DATENAME(dw, A.ModifiedDate) DayofWeek,
CASE DATEPART(dw, A.ModifiedDate)
WHEN 1 THEN 'WEEKEND'
WHEN 2 THEN 'Weekday'
WHEN 3 THEN 'Weekday'
WHEN 4 THEN 'Weekday'
WHEN 5 THEN 'Weekday'
WHEN 6 THEN 'Weekday'
WHEN 7 THEN 'WEEKEND'
END  WorkDay
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON SP.StateProvinceID = A.StateProvinceID
GO

Both the above queries are giving us the same result; however, by using CHOOSE function, T-SQL looks much cleaner and easier to read. You can refer my earlier blog post where I have discussed how CHOOSE() uses the CASE statement under the hood.

Summary: Well, the CASE statement is going nowhere; I think the CASE statement is so important in data presentation that it will be always there, and many new commands will show up and they will be using the CASE statement under the hood.

Now, here is a question back to you – Does PIVOT use CASE statement too? Please leave your answer as comments.

The New Functions as Shorthand for CASE Statement Logic

One small point of wording helps a lot here. In T-SQL, CASE is an expression, not a statement. It returns a single value, so it can go anywhere a value is allowed: in the SELECT list, in WHERE, in ORDER BY, and even inside an aggregate such as SUM(CASE WHEN ... THEN 1 ELSE 0 END). It cannot control the flow of a batch the way IF and WHILE do.

That is also why IIF and CHOOSE can be rewritten as CASE so easily. They are simply shorter ways to write the same expression. As for the PIVOT question, I will leave that one to you, and the execution plan of a small PIVOT query is a good place to start looking.

Want to try this yourself? Here is how to install AdventureWorks.

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