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.