SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by bitbucket / T-SQL 2012 #2 / Latest Posts

RE: T-SQL 2012 #2

jfgoude — Fri, 22 Feb 2013 01:03:34 GMT

learned something about something I will never use :)

RE: T-SQL 2012 #2

manik123 — Tue, 22 Jan 2013 02:29:04 GMT

wow... nice question.. really i learned new choose() functionality. i never ever used it.:w00t:

RE: T-SQL 2012 #2

Dineshbabu — Thu, 17 Jan 2013 00:06:38 GMT

Tom, thanks for your comments about Return type.I don't have 2012 to understand it further.

RE: T-SQL 2012 #2

Revenant — Wed, 16 Jan 2013 13:28:38 GMT

[quote][b]wolfkillj (1/15/2013)[/b][hr][quote][b]L' Eomot Inversé (1/15/2013)[/b][hr][quote][b]KWymore (1/14/2013)[/b][hr]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.[/quote]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).[/quote]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. JasonJason[/quote]Sometimes is better to be late to the discussion: learned lot from Tom's comments.So thanks to both Ron and Tom!

RE: T-SQL 2012 #2

wolfkillj — Tue, 15 Jan 2013 13:12:50 GMT

[quote][b]L' Eomot Inversé (1/15/2013)[/b][hr][quote][b]KWymore (1/14/2013)[/b][hr]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.[/quote]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).[/quote]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. JasonJason

RE: T-SQL 2012 #2

L' Eomot Inversé — Tue, 15 Jan 2013 13:00:41 GMT

[quote][b]KWymore (1/14/2013)[/b][hr]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.[/quote]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).

RE: T-SQL 2012 #2

KWymore — Mon, 14 Jan 2013 17:16:33 GMT

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.

RE: T-SQL 2012 #2

Miles Neale — Mon, 14 Jan 2013 12:53:25 GMT

Fun, easy, and educational. Excellent!

RE: T-SQL 2012 #2

WayneS — Mon, 14 Jan 2013 11:59:39 GMT

[quote][b]Koen Verbeeck (1/14/2013)[/b][hr]Great question about a new 2012 feature. Thanks Ron!(and thanks for including me ;-))[/quote]+1

RE: T-SQL 2012 #2

paul s-306273 — Mon, 14 Jan 2013 09:28:08 GMT

[quote][b]L' Eomot Inversé (1/12/2013)[/b][hr]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 [quote][b]BoL[/b][hr]Returns the data type with the highest precedence from the set of types passed to the function[/quote]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.[/quote]I read this and changed my mind to integer.Ah well, another point gone begging.

RE: T-SQL 2012 #2

paul s-306273 — Mon, 14 Jan 2013 09:26:47 GMT

RE: T-SQL 2012 #2

wolfkillj — Mon, 14 Jan 2013 09:09:58 GMT

[quote][b]SQLRNNR (1/12/2013)[/b][hr]Glad we have this feature added.[/quote]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.[b]Edit: [/b] 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 . . .

RE: T-SQL 2012 #2

(Bob Brown) — Mon, 14 Jan 2013 08:36:22 GMT

[quote][b]Toreador (1/14/2013)[/b][hr]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.[/quote]This was exactly what I was wondering.

RE: T-SQL 2012 #2

Lon-860191 — Mon, 14 Jan 2013 08:33:45 GMT

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.

RE: T-SQL 2012 #2

mtassin — Mon, 14 Jan 2013 08:11:16 GMT

[quote][b]Koen Verbeeck (1/14/2013)[/b][hr]Great question about a new 2012 feature. Thanks Ron![/quote]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. :)

RE: T-SQL 2012 #2

(Bob Brown) — Mon, 14 Jan 2013 07:46:22 GMT

Thanks for the question.

RE: T-SQL 2012 #2

Toreador — Mon, 14 Jan 2013 07:30:57 GMT

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.

RE: T-SQL 2012 #2

Dana Medley — Mon, 14 Jan 2013 07:23:38 GMT

Great question about a new feature.

RE: T-SQL 2012 #2

Mike Hays — Mon, 14 Jan 2013 06:32:45 GMT

Good introduction the CHOOSE function.

RE: T-SQL 2012 #2

Thomas Abraham — Mon, 14 Jan 2013 05:03:12 GMT

Thanks for a Monday question, that still made me learn something.

RE: T-SQL 2012 #2

Koen Verbeeck — Mon, 14 Jan 2013 01:28:56 GMT

Great question about a new 2012 feature. Thanks Ron!(and thanks for including me ;-))

RE: T-SQL 2012 #2

sqlnaive — Mon, 14 Jan 2013 00:06:50 GMT

Thanks a lot for this question. Got to know about CHOOSE function. Very helpful. :-)

RE: T-SQL 2012 #2

Stewart "Arturius" Campbell — Mon, 14 Jan 2013 00:05:52 GMT

Interesting question, thanks Ron

RE: T-SQL 2012 #2

demonfox — Sun, 13 Jan 2013 22:27:17 GMT

[quote][b]Lokesh Vij (1/13/2013)[/b][hr]Wow.. Thanks Ron for another one on SS2012 :-)[/quote]+1Thanks for the question..

RE: T-SQL 2012 #2

Lokesh Vij — Sun, 13 Jan 2013 22:16:08 GMT

Wow.. Thanks Ron for another one on SS2012 :-)

RE: T-SQL 2012 #2

L' Eomot Inversé — Sat, 12 Jan 2013 18:25:35 GMT

[quote][b]bitbucket-25253 (1/12/2013)[/b][hr][quote][b]L' Eomot Inversé (1/12/2013)[/b][hr]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 [quote][b]BoL[/b][hr]Returns the data type with the highest precedence from the set of types passed to the function[/quote]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.[/quote]From the supporting link in the question [quote]CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned.[/quote][/quote]Yes, it says that. But that says nothing about the type returned, it's about which value is selected before any necessary type conversion is done. The same page contains the incorrect statement that I quoted, which says nothing about which value is selected, only about which type it is to be converted to (which it gets wrong).

RE: T-SQL 2012 #2

SQLRNNR — Sat, 12 Jan 2013 17:18:06 GMT

Glad we have this feature added.

RE: T-SQL 2012 #2

bitbucket-25253 — Sat, 12 Jan 2013 13:45:18 GMT

[quote][b]L' Eomot Inversé (1/12/2013)[/b][hr]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 [quote][b]BoL[/b][hr]Returns the data type with the highest precedence from the set of types passed to the function[/quote]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.[/quote]From the supporting link in the question [quote]CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned.[/quote]

RE: T-SQL 2012 #2

L' Eomot Inversé — Sat, 12 Jan 2013 12:41:29 GMT

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 [quote][b]BoL[/b][hr]Returns the data type with the highest precedence from the set of types passed to the function[/quote]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.

T-SQL 2012 #2

bitbucket-25253 — Sat, 12 Jan 2013 12:32:06 GMT

Comments posted to this topic are about the item [B]T-SQL 2012 #2[/B]