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.

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.

Reference:  Pinal Dave (https://blog.sqlauthority.com)