<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by bitbucket
»
T-SQL 2012 #2
30 posts, Page 2 of 3
««
1
2
3
»»
T-SQL 2012 #2
Rate Topic
Display Mode
Topic Options
Author
Message
Thomas Abraham
Thomas Abraham
Posted Monday, January 14, 2013 5:03 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 1,146,
Visits: 1,448
Thanks for a Monday question, that still made me learn something.
Please don't go. The drones need you. They look up to you.
Post #1406660
Mike Hays
Mike Hays
Posted Monday, January 14, 2013 6:32 AM
Old Hand
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:02 AM
Points: 300,
Visits: 307
Good introduction the CHOOSE function.
Post #1406689
Dana Medley
Dana Medley
Posted Monday, January 14, 2013 7:23 AM
Ten Centuries
Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:52 PM
Points: 1,350,
Visits: 870
Great question about a new feature.
Oh no, we're toast!
I've got this. *Keyboard clatter*
Woah, how'd you do that?
I'm a DBA...Booyah
Yeah, uh huh, you know what it is. Everything I do, I do it big
Post #1406716
Toreador
Toreador
Posted Monday, January 14, 2013 7:30 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 1,356,
Visits: 4,761
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.
Post #1406723
(Bob Brown)
(Bob Brown)
Posted Monday, January 14, 2013 7:46 AM
Say Hey Kid
Group: General Forum Members
Last Login: Friday, April 19, 2013 10:27 AM
Points: 690,
Visits: 1,100
Thanks for the question.
Post #1406737
mtassin
mtassin
Posted Monday, January 14, 2013 8:11 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 3,226,
Visits: 64,072
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
For tips on how to post your problems
Post #1406753
Lon-860191
Lon-860191
Posted Monday, January 14, 2013 8:33 AM
Ten Centuries
Group: General Forum Members
Last Login: Monday, May 06, 2013 8:39 AM
Points: 1,045,
Visits: 218
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.
Post #1406765
(Bob Brown)
(Bob Brown)
Posted Monday, January 14, 2013 8:36 AM
Say Hey Kid
Group: General Forum Members
Last Login: Friday, April 19, 2013 10:27 AM
Points: 690,
Visits: 1,100
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.
Post #1406769
wolfkillj
wolfkillj
Posted Monday, January 14, 2013 9:09 AM
Right there with Babe
Group: General Forum Members
Last Login: Today @ 8:13 AM
Points: 721,
Visits: 1,369
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 . . .
Post Attachments
CHOOSEvsIIFvsCASE.txt
(
4 views,
1.00 KB
)
CHOOSEvsIIFvsCASE.jpg
(
3 views,
80.22 KB
)
CHOOSEvsIIFvsCASE.sqlplan
(
1 view,
82.33 KB
)
Post #1406792
paul s-306273
paul s-306273
Posted Monday, January 14, 2013 9:26 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 4:24 AM
Points: 1,158,
Visits: 642
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.
Post #1406808
« Prev Topic
|
Next Topic »
30 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
