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
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!
PS: Apologies for not capitalizing key words...