Collation and SQL_VARIANT

  • Paul White

    SSC Guru

    Points: 150442

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

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • BrainDonor

    SSCoach

    Points: 19228

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

    Steve Hall
    Linkedin
    Blog Site

  • SG Cowley

    Hall of Fame

    Points: 3445

    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

  • Paul White

    SSC Guru

    Points: 150442

    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.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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 s-306273

    SSChampion

    Points: 10615

    Paul - nice question today.

  • call.copse

    SSCoach

    Points: 17188

    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.

  • rfr.ferrari

    SSCertifiable

    Points: 6879

    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!
  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    Amazing question! Thank you.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

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

  • Michael Riemer

    SSCertifiable

    Points: 5123

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

    Thanks

  • Thomas Abraham

    SSChampion

    Points: 10761

    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

  • Paul White

    SSC Guru

    Points: 150442

    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.

  • TomThomson

    SSC Guru

    Points: 104773

    Excellent question.

    I wonder if there's another on sql_variant in the pipleline?

    Tom

Viewing 15 posts - 1 through 15 (of 42 total)

You must be logged in to reply to this topic. Login to reply