Collation and SQL_VARIANT

  • Comments posted to this topic are about the item Collation and SQL_VARIANT

  • 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).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • I'd never heard of SQL_VARIANT, thanks for the question.

  • Also now validated on SQL Server 2000!

    Learnt a couple of new things today. Thanks for the question. 🙂

  • This was removed by the editor as SPAM

  • 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.

  • 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.;-)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Paul - nice question today.

  • 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.

  • one more time, great question!

    thanks, paul!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • Amazing question! Thank you.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • Great question, I have definitely learnt something today! Whether I ever use it will be another question!!! 🙂

    Thanks

  • 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

  • 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.

  • 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