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 10:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:50 AM
Points: 1,990, Visits: 10,965
Great question! I also learned something....

Rob Schripsema
Accelitec, Inc
Post #1198784
Posted Tuesday, November 1, 2011 10:05 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 4,427, Visits: 3,423
Great -- it forced me to re-read collation rules.
Post #1198789
Posted Tuesday, November 1, 2011 12:30 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 6:23 AM
Points: 392, Visits: 405
i ran this query to figure out how this works.. great question

===========================================
Better try and fail than not to try at all...

Database Best Practices

SQL Server Best Practices
Post #1198858
Posted Tuesday, November 1, 2011 2:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:25 PM
Points: 6,256, Visits: 7,436
Rich Weissler (11/1/2011)
cfradenburg (11/1/2011)That's row number 2, not two rows.

Doh! That's clear as day now. Thank you! (Both for the answer, and the patience.) (I learned two things today! First, a little more about SQL_Variant. Second, don't try to comprehend the QotD answer before coffee.)


Particularly Paul's...



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1198956
Posted Wednesday, November 2, 2011 4:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 7:06 AM
Points: 1,216, Visits: 308
Nice question Kiwi!
Post #1199170
Posted Wednesday, November 2, 2011 6:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
SQL Kiwi (11/1/2011)
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.


Hm, do you have an explanation why the following code will return the exact same result?
DECLARE @Example TABLE
(
id TINYINT IDENTITY PRIMARY KEY,
string SQL_VARIANT UNIQUE CLUSTERED
)

INSERT @Example (string) VALUES ('B' COLLATE Latin1_General_CI_AI)
INSERT @Example (string) VALUES ('B' COLLATE Latin1_General_CI_AS)
INSERT @Example (string) VALUES ('b' COLLATE Latin1_General_CS_AI)
INSERT @Example (string) VALUES ('b' COLLATE Latin1_General_CS_AS)

SELECT id, string, sql_variant_property(string, 'basetype') as bt, sql_variant_property(string, 'collation') as collat
FROM @Example
WHERE string = 'b' -- COLLATE Latin1_General_CI_AS

Only if the WHERE clause is ommitted completely, all 4 rows are returned as expected.
Played around with your code on SQL Server 2008 R2 (after having answered the QotD) and found out about this peculiar behavior.

And thanks for a very interesting question!

Regards,
Michael

PS: Apologies for not capitalizing key words...
Post #1199231
Posted Wednesday, November 2, 2011 6:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 6,000, Visits: 8,263
michael.kaufmann (11/2/2011)
Hm, do you have an explanation why the following code will return the exact same result?


My guess would be that your server has a default collation of Latin1_General_CI_AS. My server has Latin1_General_CS_AS as the default, and when I run your code, I get the row with id=4 returned.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1199235
Posted Wednesday, November 2, 2011 6:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
Hugo Kornelis (11/2/2011)
michael.kaufmann (11/2/2011)
Hm, do you have an explanation why the following code will return the exact same result?


My guess would be that your server has a default collation of Latin1_General_CI_AS. My server has Latin1_General_CS_AS as the default, and when I run your code, I get the row with id=4 returned.


Thanks for your quick reply, Hugo--tend to keep forgetting about the default server collation... (I ran the test in tempdb, not in a user database with a known collation).
Post #1199252
Posted Wednesday, November 2, 2011 5:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 11,194, Visits: 11,166
Hugo Kornelis (11/2/2011)
michael.kaufmann (11/2/2011)
Hm, do you have an explanation why the following code will return the exact same result?

My guess would be that your server has a default collation of Latin1_General_CI_AS. My server has Latin1_General_CS_AS as the default, and when I run your code, I get the row with id=4 returned.

Yes that's it. When I first wrote the demo code for this question I created a new database with an explicit collation, and ran the SELECT query without an explicit COLLATE clause. After some thought, I decided that this made the code a bit too long and made the question sneakier than I would like, so I went with the shorter version with the explicit COLLATE. Perhaps I made the wrong decision there; the dependency on default collation is a good learning point.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1199684
Posted Thursday, November 10, 2011 2:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:43 AM
Points: 1,691, Visits: 1,110
SQL Kiwi (11/1/2011)
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.


Thanks Paul, I'll definitely keep that in mind - it sounds like it is at least halfway thought through.
Post #1203347
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse