November 9, 2011 at 8:21 pm
Comments posted to this topic are about the item SQL_VARIANT 1
November 9, 2011 at 11:36 pm
Good question, thanks. The MSDN article doesn't specify the precedence of the date datatype, so there was some guessing involved.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 9, 2011 at 11:58 pm
Another great question about SQL_VARIANT, thanks!
Koen Verbeeck (11/9/2011)
The MSDN article doesn't specify the precedence of the date datatype
Another MSDN article specifies that: http://msdn.microsoft.com/en-us/library/ms173829(v=SQL.105).aspx
November 10, 2011 at 12:19 am
This was removed by the editor as SPAM
November 10, 2011 at 2:51 am
Nice question, Ron!
I only got it right because I figured that the relative order of the date and time related values would be "logical". And since the conversion to smalldatetime would chop off the seconds and the conversion to date the entire time portion, the only logical ordering of those three would be date / smalldatetim / datetime. So I knew that I was looking for an answer where the number 2 was before 3, and 3 before 5 - with the other two numbers at any possible location.
My luck was that there was only one solution left after applying this filter! 😉
PS: Is the "weird" order of the colB values accidental, or a deliberate ploy to make the question even harder?
November 10, 2011 at 3:14 am
Nice one, thanks
Iulian
November 10, 2011 at 3:16 am
nice question!!!
thanks Ron!!!
November 10, 2011 at 4:54 am
Good question, but I'm not sure that the explanation is 100% correct.
Because values from different data type families must be explicitly cast before being referenced in comparison predicates, the effects of the The values in the following table are examples of the rules regarding data type precedence.
The values in the sql_variant column are not casted/converted into a common data type prior to the ORDER BY.
If there is data from different data type families, then the values are sorted based on the data type family precedence.
A quick example is if you add three values from the "Exact numeric" family and one from "Unicode" family.
create table #test(id int, val sql_variant)
insert into #test values(1,'2')
insert into #test values(2,1)
insert into #test values(3,3)
insert into #test values(4,0)
select *,sql_variant_property(val,'BaseType') from #test order by val
/* Result */
idval(No column name)
12varchar
40int
21int
33int
As you can see when running this query, there is no casting of values between int and the varchar values in this column. The varchar (Unicode family) has lower precedence than int (Exact numeric family) and will always be lowest value.
November 10, 2011 at 5:41 am
Hugo Kornelis (11/10/2011)
Nice question, Ron!I only got it right because I figured that the relative order of the date and time related values would be "logical". And since the conversion to smalldatetime would chop off the seconds and the conversion to date the entire time portion, the only logical ordering of those three would be date / smalldatetim / datetime.
I used the same process, but was idiot enough to stop when I figured out the hierarchy of the data types (including the inferred position of date). Would have been beneficial to actual DO the process of type conversion! Well, at least NOW my mind is working this morning! 😉
Thanks for the question.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
November 10, 2011 at 6:35 am
Nils Gustav Stråbø (11/10/2011)
Good question, but I'm not sure that the explanation is 100% correct.
I'm sure that the part of the explanation was deleted by accident:
Because values from different data type families must be explicitly cast before being referenced in comparison predicates, the effects of the something is missing here The values in the following table are examples of the rules regarding data type precedence.
I think the meaning of the explanation is that a programmer must use something like "CAST(sql_variant_value AS datatype)" or "CONVERT(datatype, sql_variant_value, style)" rather than "sql_variant_value" by itself.
November 10, 2011 at 6:48 am
Good question. I'll be honest, I researched first, and Google pointed me to the entry vk posted in his earlier reply. I've never seen the sql_variant data type before.
Of course, mentally I sorted the data in "decending" order using the chart. Not too sure how many others might have done that, but if one of the answers was "5,3,2,1,4" I wonder how many besides me would have picked it, temporarily forgetting default sort order?
There's always something extra to keep in mind 🙂
November 10, 2011 at 7:02 am
I woulda had it right, but I thought I'd double check myself. Since I was using SQL 2008 R2, I figured I'd use the new way to do inserts because I was too lazy to type.
CREATE TABLE #tablea ( cola SQL_VARIANT ,
colb INT )
GO
DECLARE @Date AS DATETIME
SET @date = '2011-08-27 16:20:28.047'
INSERT #tablea
VALUES
( CAST(46279.1 AS DECIMAL(10, 2)), 1 ),
( CAST(@date AS DATETIME), 5 ),
( CAST(@Date AS DATE), 2 ),
( CAST(@DATE AS SMALLDATETIME), 3 ),
( 'abc', 4 )
SELECT
colb
FROM
#tablea
ORDER BY
cola
On 2008r2 you get the following result from the above.
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.
However, if you convert it back to the original code, it works as described.
Just another interesting twist to this.
November 10, 2011 at 7:05 am
vk-kirov (11/10/2011)
Nils Gustav Stråbø (11/10/2011)
I think the meaning of the explanation is that a programmer must use something like "CAST(sql_variant_value AS datatype)" or "CONVERT(datatype, sql_variant_value, style)" rather than "sql_variant_value" by itself.
In my opinion, this would be the correct explanation since there are values from different data type families (Approximate numeric,Date and Time and Unicode) and values with different data types but within the same family (Date and Time). An INT value will always be higher than a varchar when dealing with sql_variant. 1 is higher than '2' simply because 1 is an INT and '2' is a varchar.
From BOL:
- When sql_variant values of different base data types are compared, and the base data types are in different data type families, the value whose data type family is higher in the hierarchy chart is considered the higher of the two values.
- When sql_variant values of different base data types are compared, and the base data types are in the same data type family, the value whose base data type is lower in the hierarchy chart is implicitly converted to the other data type and the comparison is then made.
- When sql_variant values of the char, varchar, nchar, or varchar data types are compared, they are evaluated based on the following criteria: LCID, LCID version, comparison flags, and sort ID. Each of these criteria are compared as integer values, and in the order listed.
November 10, 2011 at 7:13 am
Hugo Kornelis (11/10/2011)
Nice question, Ron!PS: Is the "weird" order of the colB values accidental, or a deliberate ploy to make the question even harder?
Now that is another QOD to which I can only answer "It is mine to know yours to find out"
November 10, 2011 at 7:54 am
When I run this I get an error (which was my answer BTW):
Msg 243, Level 16, State 1, Line 5
Type DATE is not a defined system type.
Why is this working for everyone else?
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply