SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL 2012 #2


T-SQL 2012 #2

Author
Message
Thomas Abraham
Thomas Abraham
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2761 Visits: 2255
Thanks for a Monday question, that still made me learn something.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Mike Hays
Mike Hays
Right there with Babe
Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)

Group: General Forum Members
Points: 771 Visits: 764
Good introduction the CHOOSE function.
Dana Medley
Dana Medley
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2512 Visits: 1696
Great question about a new feature.



Everything is awesome!
Toreador
Toreador
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2811 Visits: 8084
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.
(Bob Brown)
(Bob Brown)
SSC Eights!
SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)

Group: General Forum Members
Points: 887 Visits: 1145
Thanks for the question.
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4866 Visits: 72519
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. Smile



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Lon-860191
Lon-860191
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1895 Visits: 278
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)
(Bob Brown)
SSC Eights!
SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)

Group: General Forum Members
Points: 887 Visits: 1145
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.
wolfkillj
wolfkillj
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1470 Visits: 2582
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
Blog: SQLSouth
Twitter: @SQLSouth
Attachments
CHOOSEvsIIFvsCASE.txt (8 views, 1.00 KB)
CHOOSEvsIIFvsCASE.jpg (7 views, 80.00 KB)
CHOOSEvsIIFvsCASE.sqlplan (5 views, 82.00 KB)
paul s-306273
paul s-306273
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2454 Visits: 1116
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search