T-SQL 2012 #2

Question

Post reply

T-SQL 2012 #2

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

mtassin SSC-Insane Points: 23099 More actions · Answer 1

Koen Verbeeck (1/14/2013)

Great question about a new 2012 feature. Thanks Ron!

I must concur... that's a neat function that I had missed. I got this one right because there was only one answer that made sense, returning an integer after being given a bunch of comma separated strings wouldn't make sense to me, but I didn't know about choose until now. 🙂

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Lon-860191 SSCrazy Points: 2449 More actions · Answer 2

Lon-860191

SSCrazy

Points: 2449

January 14, 2013 at 8:33 am

#1576928

Thanks, for the reminder about the Choose function. The beauty is the ability to provide two things, an alternate name or identification as well as a different sort order than if the value provided was the content of the field. Of course this really only works well if there is a short list of values to translate.

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 3

Toreador (1/14/2013)

What would be a good use of this function?

Why wouldn't you just add a lookup table containing the index and description, and join to that?

So that any changes required to a description could be made by just updating the lookup table, rather than rewriting SQL.

This was exactly what I was wondering.

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 4

SQLRNNR (1/12/2013)

Glad we have this feature added.

But are the CHOOSE function and its also-new-to-SQL-Server-2012 buddy IIF anything other than syntactical sugar for Visual Basic coders writing T-SQL? The CHOOSE function is just a less-flexible shorthand for the ANSI-standard CASE, and it can only be used when evaluating an equality condition for an integer value (the index argument), although I concede that CHOOSE requires less typing than CASE in these limited circumstances. Similarly, the IIF function saves a few keystrokes when there are only two possible values to return, but once you start nesting IIF statements, the code becomes more difficult to understand than the equivalent CASE expression, in my opinion.

SQL Server 2012 just converts CHOOSE and IIF function calls to CASE expressions anyway. I have a code sample that shows that the CHOOSE and IIF functions produce Compute Scalar operators that have Defined Values that are identical to the one produced by the equivalent CASE expression, but I'm having some trouble posting it. I'll try again later.

Edit: I have posted a screenshot of the code, a .txt file of the code, and a .sqlplan file of the execution plan that I get on my SQL Server 2012 instance, if you're interested. Still not sure why I can't post code - I get an error screen that suggests that my employer is blocking the outgoing traffic when it includes certain kinds of code snippets - hmmm . . .

Jason Wolfkill

paul s-306273 SSChampion Points: 10666 More actions · Answer 5

L' Eomot Inversé (1/12/2013)

Nice clear straightforward question.

Good to see a question on this new feature.

Interestingly, there's an error on the BoL page: it says the CHOOSE function

BoL

Returns the data type with the highest precedence from the set of types passed to the function

The first argument is an integer, which has a higher type precedence than any character type, but if it really was going to return an int we would have string to int conversion errors here. BoL should instead say that the return type is highest precedence type of the arguments other than the first.

I read this and changed my mind to integer.

Ah well, another point gone begging.

paul s-306273 SSChampion Points: 10666 More actions · Answer 6

L' Eomot Inversé (1/12/2013)

Nice clear straightforward question.

Good to see a question on this new feature.

Interestingly, there's an error on the BoL page: it says the CHOOSE function

BoL

Returns the data type with the highest precedence from the set of types passed to the function

The first argument is an integer, which has a higher type precedence than any character type, but if it really was going to return an int we would have string to int conversion errors here. BoL should instead say that the return type is highest precedence type of the arguments other than the first.

I read this and changed my mind to integer.

Ah well, another point gone begging.

WayneS SSC Guru Points: 95405 More actions · Answer 7

Koen Verbeeck (1/14/2013)

Great question about a new 2012 feature. Thanks Ron!

(and thanks for including me ;-))

+1

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Miles Neale SSChampion Points: 13147 More actions · Answer 8

Miles Neale

SSChampion

Points: 13147

January 14, 2013 at 12:53 pm

#1577059

Fun, easy, and educational. Excellent!

Not all gray hairs are Dinosaurs!

Ken SSCoach Points: 17069 More actions · Answer 9

Thanks for the 2012 question. I'm not sure why I would ever use CHOOSE. I would usually join to a reference table, or use a case statement, or create a temp table if it was a small set of values to type up for an ad-hoc query or one off report. This does seem more geared towards .NET developers. I think if I came across it in production I would have to turn the array into a reference table.

TomThomson SSC Guru Points: 104773 More actions · Answer 10

KWymore (1/14/2013)

Thanks for the 2012 question. I'm not sure why I would ever use CHOOSE. I would usually join to a reference table, or use a case statement, or create a temp table if it was a small set of values to type up for an ad-hoc query or one off report. This does seem more geared towards .NET developers. I think if I came across it in production I would have to turn the array into a reference table.

If you would sometimesd use a CASE expression for this, why object to CHOOSE? It's just a simplified syntax for CASE in a particular case. Presumably it does index into the list, so it will have better performance than case when it is applicable (because the optimiser surely isn't going to look and see if the set of values for a simple case statement provides an ungapped sequence).

Tom

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 11

L' Eomot Inversé (1/15/2013)

KWymore (1/14/2013)

Thanks for the 2012 question. I'm not sure why I would ever use CHOOSE. I would usually join to a reference table, or use a case statement, or create a temp table if it was a small set of values to type up for an ad-hoc query or one off report. This does seem more geared towards .NET developers. I think if I came across it in production I would have to turn the array into a reference table.

If you would sometimesd use a CASE expression for this, why object to CHOOSE? It's just a simplified syntax for CASE in a particular case. Presumably it does index into the list, so it will have better performance than case when it is applicable (because the optimiser surely isn't going to look and see if the set of values for a simple case statement provides an ungapped sequence).

Hi Tom,

Check my post above (about a page back in this thread) - the optimizer produces an execution plan with a Compute Scalar operator for CHOOSE with a defined value that is identical to that of CASE. It looks like the T-SQL implementation of CHOOSE truly is nothing but an alternative syntax for CASE when writing a conditional expression based on an equality comparison of integer values. SQL Server treats them exactly the same way.

Jason

Jason Wolfkill

Revenant SSC-Forever Points: 42467 More actions · Answer 12

wolfkillj (1/15/2013)

L' Eomot Inversé (1/15/2013)

KWymore (1/14/2013)

Thanks for the 2012 question. I'm not sure why I would ever use CHOOSE. I would usually join to a reference table, or use a case statement, or create a temp table if it was a small set of values to type up for an ad-hoc query or one off report. This does seem more geared towards .NET developers. I think if I came across it in production I would have to turn the array into a reference table.

If you would sometimesd use a CASE expression for this, why object to CHOOSE? It's just a simplified syntax for CASE in a particular case. Presumably it does index into the list, so it will have better performance than case when it is applicable (because the optimiser surely isn't going to look and see if the set of values for a simple case statement provides an ungapped sequence).

Hi Tom,

Check my post above (about a page back in this thread) - the optimizer produces an execution plan with a Compute Scalar operator for CHOOSE with a defined value that is identical to that of CASE. It looks like the T-SQL implementation of CHOOSE truly is nothing but an alternative syntax for CASE when writing a conditional expression based on an equality comparison of integer values. SQL Server treats them exactly the same way.

Jason

Jason