Thursday, November 20, 2014

New SQL Server 2012 Logical Functions

Want to present couple of new T-SQL Logical Functions implemented in SQL Server 2012.

The first function is "CHOOSE". It simply chooses one value from a provided list.
Here are couple of examples:
SELECT CHOOSE(3, 'ab', 'cd', 'ef', 'gh');
SELECT CHOOSE(5, 0, 1, 2, 3, '4');

First query will return third value "ef" and the second query will return number "4".

That means you can use any data type in the list as far as all items are of the same type or types can be converted by SQL Server.

It is pretty easy. What about the limits?
Books Online do not mention any limits. The biggest number of items I was be able to test with was 4426. That depends also on size of items and on the first parameter, which value has to be chosen.
In some cases I've got following error:
Msg 8631, Level 17, State 1, Server Bla-Bla-Bla, Line 1
Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.

Here are automated testing results for SQL Express 2014: In Research batch script I was be able to execute "CHOOSE" function with 4981 items and item size upper than 60 bytes each (probably goes higher, but I've stopped the test).

However, in manual mode it shows even better results: Up to 4985 Items with item size 64Kb !!!
I'd consider it as a pretty high limit with query size as big as  300 Mb.

The second function is "IIF" !!!
It was more than 20 years available in MSAccess version of SQL and I really missed it.

For those who do not know what it is: It is just a shorter version of "CASE" clause, which chooses between only two choices.

IIF has three parameters: First parameter is a logical statement, second parameter is a value, which will be returned when the first parameter is "True". In case the first parameter is "False", third parameter's valuse will be returned.

"IIF" looks more convenient than "CASE", but it is about 1.5% slower on SQL 2014 and about 2% slower on SQL 2012.
Just compare two queries below. They produce the same results by marking Even and Odd numbers:

SELECT TOP 10 message_id,
    CASE message_id % 2 WHEN 0 THEN 'Even' ELSE 'Odd' END
FROM sys.messages;

SELECT TOP 10 message_id, 
    IIF(message_id % 2=0,'Even','Odd')
FROM sys.messages;

Unfortunately, do not think a lot of people will be using it because of it slowness.

No comments:

Post a Comment