Collation, Danish_Norwegian Vs Latin1_General

Collation is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast.  We all think that Collation is effected for Sorting and Case Sensitive data.

 CREATE TABLE t ( c char(2) )

GO

INSERT INTO t VALUES ( 'a' )

 

INSERT INTO t VALUES ( 'Aa' )

INSERT INTO t VALUES ( 'Za' )

INSERT INTO t VALUES ( 'A' )

INSERT INTO t VALUES ( 'B' )

--Result 1

SELECT * FROM t ORDER BY c COLLATE Danish_Norwegian_CI_AS

--Result 2

SELECT * FROM t ORDER BY c COLLATE Latin1_General_CI_AS

Following are the two result sets.

Result1 (Danish_Norwegian)	Result2 (Latin1_General)
a	a
A	A
B	Aa
Za	B
Aa	Za

You can see that in the in the both result sets only difference is placement of Aa.

If you run following two statements, things will be quite annoying.

--Result 3

SELECT

* FROM t WHERE c LIKE 'A%' COLLATE Danish_Norwegian_CI_AS

--Result 4

SELECT

* FROM t WHERE c LIKE 'A%' COLLATE Latin1_General_CI_AS

Result 3 will return 2 rows, a and A and Result 4 will return a, A and Aa. Which means that Aa is missing in Danish_Norwegian collation. Above results were obtained from SQL Server 2005 SP3 and these results are same with SQL Server 2008 SP1.