Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Collation and SQL_VARIANT Expand / Collapse
Author
Message
Posted Tuesday, November 1, 2011 4:46 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:59 AM
Points: 897, Visits: 1,488
Amazing question! Thank you.

Best regards,


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1198537
Posted Tuesday, November 1, 2011 5:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:38 AM
Points: 1,762, Visits: 477
Great question, I have definitely learnt something today! Whether I ever use it will be another question!!!
Thanks
Post #1198552
Posted Tuesday, November 1, 2011 5:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:26 AM
Points: 1,803, Visits: 2,168
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
Post #1198576
Posted Tuesday, November 1, 2011 5:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1198587
Posted Tuesday, November 1, 2011 6:00 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:59 PM
Points: 8,748, Visits: 9,296
Excellent question.

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


Tom
Post #1198592
Posted Tuesday, November 1, 2011 6:04 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Wow, did I have to think on this one. Thanks for the interesting question.

http://brittcluff.blogspot.com/
Post #1198593
Posted Tuesday, November 1, 2011 6:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
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




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1198595
Posted Tuesday, November 1, 2011 6:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1198597
Posted Tuesday, November 1, 2011 6:20 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:36 PM
Points: 1,812, Visits: 899
Only row 2 matches the explicit Latin1_General_CI_AS collation in the query.

I see one row. Which is the other one?
Post #1198605
Posted Tuesday, November 1, 2011 6:29 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 22, 2014 1:41 PM
Points: 845, Visits: 728
Great question, I sure learned something new this morning.
Post #1198611
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse