November 30, 2011 at 8:59 am
Hugo Kornelis (11/30/2011)
Gerardo Galvan Castro (11/30/2011)
In the e-mail newsletter the QotD did not state it was for SQL Server 2008 and later, so I tested under SQL Server 2005 (the only version I have available to use), which would not allow me to run it because "you cannot assign a default value to a local variable", so i chose "None of the above" just to find out it was meant to be run under SQLS 2K8.I'd say, with both SQL Server 2008 and SQL Server 2008R2 released and SQL Server 2012 around the corner, it is safe to assume that question are about SQL Server 2008/2008R2 unless specified otherwise.
Agreed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 30, 2011 at 9:00 am
Toreador (11/30/2011)
KWymore (11/30/2011)
I am curious how many out there are utilizing sql_variant and under which circumstances?I've used it for generic audit tables, where we store something like table name, column name, old value, new value - the old/new values are sql_variant as they could be any data type.
Of course you'd never do any comparisons between different data types in these circumstances.
That is a good use - thanks for posting that. I will have to explore that use.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 30, 2011 at 9:22 am
KWymore (11/30/2011)
Interesting question. My brain did the auto translation that v3=v2 was the same as v2=v3 so I didn't even notice that error until I hit the posts here!
+1
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
November 30, 2011 at 9:28 am
Stuart Davies (11/30/2011)
KWymore (11/30/2011)
Interesting question. My brain did the auto translation that v3=v2 was the same as v2=v3 so I didn't even notice that error until I hit the posts here!+1
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 30, 2011 at 9:53 am
SQLRNNR (11/30/2011)
Stuart Davies (11/30/2011)
KWymore (11/30/2011)
Interesting question. My brain did the auto translation that v3=v2 was the same as v2=v3 so I didn't even notice that error until I hit the posts here!+1
+1
+1
November 30, 2011 at 12:09 pm
:w00t:
In the interest of being exact, the answers given transpose the 3rd field so there was not exactly correct answer given...
Here is the result:
v1 > v2v1 > v3v3 = v2v4 > v1
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 1, 2011 at 4:19 am
I see that even if you set
@v1 sql_variant = cast ('15.00' as float(53)),
@v2 sql_variant = cast ('16.00' as decimal(18,4)),
which makes
v1 v2
1516.0000
SQL Server still says v1 > v2.
This behaviour is very counter-intuitive - to say nothing of being arithmetically incorrect!
It would be better if it produced the result one would expect - or if it won't, if it refused to do such comparisons. I can imagine many data errors being caused by people not noticing this behaviour and believing the wrong results they get.
December 1, 2011 at 6:09 am
Good question, but a lot of reading. Thanks for submitting.
http://brittcluff.blogspot.com/
December 1, 2011 at 7:50 am
Toreador (11/30/2011)
KWymore (11/30/2011)
I am curious how many out there are utilizing sql_variant and under which circumstances?I've used it for generic audit tables, where we store something like table name, column name, old value, new value - the old/new values are sql_variant as they could be any data type.
Of course you'd never do any comparisons between different data types in these circumstances.
Toreador, thanks! I, too, was wondering where one would use sql_variant - now it seems obvious.
That said, I appreciate David Data's comment:
David Data (12/1/2011)
I see that even if you set@v1 sql_variant = cast ('15.00' as float(53)),
@v2 sql_variant = cast ('16.00' as decimal(18,4)),
which makes
v1 v2
1516.0000
SQL Server still says v1 > v2.
This behaviour is very counter-intuitive - to say nothing of being arithmetically incorrect!
It would be better if it produced the result one would expect - or if it won't, if it refused to do such comparisons. I can imagine many data errors being caused by people not noticing this behaviour and believing the wrong results they get.
I really don't understand why someone would want to do a comparison that is based on the data type. I can only guess that they chose to set it up this way to avoid errors that might occur in implicit conversions. The lesson I learn from this is to not do comparisons on sql_variant.
December 1, 2011 at 8:15 am
Carla Wilson-484785 (12/1/2011)
I really don't understand why someone would want to do a comparison that is based on the data type.
Well, I guess the issue is that the SQL Server development team had to choose between allowing > and < comparisons for sql_variant (and makinig it work in all cases), or not allowing it at all.
They chose the former, and then had to come up with a way to compare the numeric value 15, the string value N'fünfzehn', the date December 1st, 2011, and the bit value 1. Which one is more than the others? And why? They had to make a choice - but whatever their choice was, it was bound to be perceived as wrong for some cases.
That being said, I do share your amazement at the choice to put exact and inexact numerics in two different groups. It appears this choice was born out of technical rather than functional considerations.
The lesson I learn from this is to not do comparisons on sql_variant.
Amen to that!
December 1, 2011 at 12:48 pm
Hugo Kornelis (12/1/2011)
Carla Wilson-484785 (12/1/2011)
I really don't understand why someone would want to do a comparison that is based on the data type.Well, I guess the issue is that the SQL Server development team had to choose between allowing > and < comparisons for sql_variant (and makinig it work in all cases), or not allowing it at all.
Given that they wanted people to be able to construct indexes on sql_variant columns, they didn't have a choice - they had to provide a total order on SQL_VARIANTs. Not allowing it at all was not an option.
They chose the former, and then had to come up with a way to compare the numeric value 15, the string value N'fünfzehn', the date December 1st, 2011, and the bit value 1. Which one is more than the others? And why? They had to make a choice - but whatever their choice was, it was bound to be perceived as wrong for some cases.
That being said, I do share your amazement at the choice to put exact and inexact numerics in two different groups. It appears this choice was born out of technical rather than functional considerations.
I can't really think of any purely "technical" considerations that would suggest making them two separate type families; perhaps an awareness that conversion from exact to inexact numerics (which is the conversion that would have to be done to allow comparison if they were in the same group) can cause bizarre rounding effects (as can the conversion in the opposite direction, of course) could be considered a technical consideration but I would consider avoiding such effects as a fundamental requirement of indexing (and in particular of having UNIQUE constraints supported by indexes) so I see it as rather an essential requirement of the SQL data model rather than something merely "technical".
The lesson I learn from this is to not do comparisons on sql_variant.
Amen to that!
[/quote]
Yes, Amen to that from me too - as far as I am concerned, the only real uses for SQL_VARIANT ordering are (i) to support indexes and unique constraints and (ii) to provide material for technical trivia questions like this one.
Tom
December 2, 2011 at 1:40 pm
L' Eomot Inversé (12/1/2011)
Hugo Kornelis (12/1/2011)
Carla Wilson-484785 (12/1/2011)
I really don't understand why someone would want to do a comparison that is based on the data type.Well, I guess the issue is that the SQL Server development team had to choose between allowing > and < comparisons for sql_variant (and makinig it work in all cases), or not allowing it at all.
Given that they wanted people to be able to construct indexes on sql_variant columns, they didn't have a choice - they had to provide a total order on SQL_VARIANTs. Not allowing it at all was not an option.
Thanks, that makes sense.
L' Eomot Inversé (12/1/2011)
Hugo Kornelis (12/1/2011)
The lesson I learn from this is to not do comparisons on sql_variant.
Amen to that!
Yes, Amen to that from me too - as far as I am concerned, the only real uses for SQL_VARIANT ordering are (i) to support indexes and unique constraints and (ii) to provide material for technical trivia questions like this one.
Fair enough! (thanks for the explanations!) 😛
December 5, 2011 at 1:15 am
Great question, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 2, 2012 at 8:36 am
nice one
Viewing 14 posts - 46 through 58 (of 58 total)
You must be logged in to reply to this topic. Login to reply