SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Collation and SQL_VARIANT


Collation and SQL_VARIANT

Author
Message
Andre Guerreiro
Andre Guerreiro
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2291 Visits: 1515
Amazing question! Thank you.

Best regards,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Michael Riemer
Michael Riemer
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3116 Visits: 656
Great question, I have definitely learnt something today! Whether I ever use it will be another question!!! :-)
Thanks
Thomas Abraham
Thomas Abraham
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3857 Visits: 2256
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!

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36110 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26184 Visits: 12501
Excellent question.

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

Tom

Britt Cluff
Britt Cluff
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2387 Visits: 253
Wow, did I have to think on this one. Thanks for the interesting question.

http://brittcluff.blogspot.com/
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36110 Visits: 11361
L' Eomot Inversé (11/1/2011)
Excellent question.

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

Not at this stage, at least not by me. I was going to do one based on the weird sort order of SQL_VARIANT, but it's too obtuse even for me :-D



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36110 Visits: 11361
Britt Cluff (11/1/2011)
Wow, did I have to think on this one. Thanks for the interesting question.

Me too - and I wrote the thing (a month or so ago).



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Rich Weissler
Rich Weissler
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2185 Visits: 954
Only row 2 matches the explicit Latin1_General_CI_AS collation in the query.

I see one row. Which is the other one?
Mike Palecek
Mike Palecek
SSC Eights!
SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)

Group: General Forum Members
Points: 938 Visits: 823
Great question, I sure learned something new this morning.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search