October 31, 2011 at 8:51 pm
Comments posted to this topic are about the item Collation and SQL_VARIANT
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 1, 2011 at 1:47 am
Great question, Paul!!
Your production code will likely be more careful with these issues.
Can I interpret this as "Your production code will likely completely avoid the use of SQLVariant"?
(Okay, there may be circumstances where SQLVariant has its use, but I would strongly suggest only using it for data that is stored and retrieved but never operated upon in any other way).
November 1, 2011 at 2:37 am
Also now validated on SQL Server 2000!
Learnt a couple of new things today. Thanks for the question. 🙂
November 1, 2011 at 2:40 am
This was removed by the editor as SPAM
November 1, 2011 at 2:55 am
Hugo Kornelis (11/1/2011)
Great question, Paul!!Your production code will likely be more careful with these issues.
Can I interpret this as "Your production code will likely completely avoid the use of SQLVariant"?
(Okay, there may be circumstances where SQLVariant has its use, but I would strongly suggest only using it for data that is stored and retrieved but never operated upon in any other way).
Hah! Well I came across this collation behaviour with SQL_VARIANT when I was thinking one day about how EAV systems would work when confronted with string data with different comparison semantics that needed to be stored in the same (value) column. For sure, SQL_VARIANT is a niche market - but I have found it useful with UNPIVOT (or the equivalent APPLY syntax) from time to time. Anyway, I'm glad you enjoyed the question.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 1, 2011 at 3:20 am
SQL Kiwi (11/1/2011)
Hugo Kornelis (11/1/2011)
Great question, Paul!!Your production code will likely be more careful with these issues.
Can I interpret this as "Your production code will likely completely avoid the use of SQLVariant"?
(Okay, there may be circumstances where SQLVariant has its use, but I would strongly suggest only using it for data that is stored and retrieved but never operated upon in any other way).
Hah! Well I came across this collation behaviour with SQL_VARIANT when I was thinking one day about how EAV systems would work when confronted with string data with different comparison semantics that needed to be stored in the same (value) column. For sure, SQL_VARIANT is a niche market - but I have found it useful with UNPIVOT (or the equivalent APPLY syntax) from time to time. Anyway, I'm glad you enjoyed the question.
Yeah, EAV and UNPIVOT are about the only options I can think of where I would even consider SQLVariant. And, as I said, I would only store the value and toss it back to the user, not do anything fancy with it (like searching). Using EAV for anything else then simply storing and retrieving is worse than opening twenty cans of worms.;-)
November 1, 2011 at 3:24 am
Paul - nice question today.
November 1, 2011 at 3:37 am
Hugo Kornelis (11/1/2011)
SQL Kiwi (11/1/2011)
Hugo Kornelis (11/1/2011)
Great question, Paul!!Your production code will likely be more careful with these issues.
Can I interpret this as "Your production code will likely completely avoid the use of SQLVariant"?
(Okay, there may be circumstances where SQLVariant has its use, but I would strongly suggest only using it for data that is stored and retrieved but never operated upon in any other way).
Hah! Well I came across this collation behaviour with SQL_VARIANT when I was thinking one day about how EAV systems would work when confronted with string data with different comparison semantics that needed to be stored in the same (value) column. For sure, SQL_VARIANT is a niche market - but I have found it useful with UNPIVOT (or the equivalent APPLY syntax) from time to time. Anyway, I'm glad you enjoyed the question.
Yeah, EAV and UNPIVOT are about the only options I can think of where I would even consider SQLVariant. And, as I said, I would only store the value and toss it back to the user, not do anything fancy with it (like searching). Using EAV for anything else then simply storing and retrieving is worse than opening twenty cans of worms.;-)
I occassionally use an EAV type thing for very limited cases (storing central control variables that may vary by territory say) - is SQL_VARIANT a good move for storing these, where it may be text numbers or dates? I've tended to just use nvarchar(MAX) before and convert as needed.
For the UNPIVOT this would be for normalising some data perhaps for import purposes? I'm just struggling to come up with a use for this SQL_VARIANT chappy.
November 1, 2011 at 4:12 am
one more time, great question!
thanks, paul!
November 1, 2011 at 4:46 am
Amazing question! Thank you.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
November 1, 2011 at 5:00 am
Great question, I have definitely learnt something today! Whether I ever use it will be another question!!! 🙂
Thanks
November 1, 2011 at 5:40 am
Not only was the question a good one, but the explanation was clear and well presented. Plus, the question didn't use a lot of unnecessary code to make the point. Bravo!
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
November 1, 2011 at 5:56 am
call.copse (11/1/2011)
I occassionally use an EAV type thing for very limited cases (storing central control variables that may vary by territory say) - is SQL_VARIANT a good move for storing these, where it may be text numbers or dates? I've tended to just use nvarchar(MAX) before and convert as needed.For the UNPIVOT this would be for normalising some data perhaps for import purposes? I'm just struggling to come up with a use for this SQL_VARIANT chappy.
SQL_VARIANT might be a good choice; after all it was added as a popular user-requested feature for EAV scenarios. It can make some EAV things a lot neater and easier, and there are other language features like SQL_VARIANT_PROPERTY that support this sort of use. Many people find using NVARCHAR(MAX) feels clunky in comparison (and downright awkward if you need to store different collations as in the example given in the question). The various pros and cons are way too much to get into detail with, but it's definitely something to familiarize yourself with, like other new-ish things in the same area like SPARSE columns.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 1, 2011 at 6:00 am
Excellent question.
I wonder if there's another on sql_variant in the pipleline?
Tom
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply