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) )
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' )
SELECT * FROM t ORDER BY c COLLATE Danish_Norwegian_CI_AS
SELECT * FROM t ORDER BY c COLLATE Latin1_General_CI_AS
Following are the two result sets.
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.
SELECT* FROM t WHERE c LIKE 'A%' COLLATE Danish_Norwegian_CI_AS
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.