Differences between Latin1_General_CI_AS and Latin_General_BIN collations? We want to write a test which proves that the collation of a column in the database is indeed Latin1_General_CI_AS. The database's default collation is Latin_General_BIN.

So we need to have some observable difference between those two databases, write a test using comparing/ordering and assert the correct outcome (which should be compared/ordered by Latin1_General_CI_AS).

Anybody know what kind of difference we could observe?
_BIN will order in the binary (ASCII order) of characters while CI_AS will use dictionary order with case insensitivity and accent sensitivity so BIN will treat "A" and "a" as different characters and order them accordingly and sort the alphabet in this order.


  ...XYZ[]^_`abc


That is the characters will be sorted by their ASCII code values

As a simple test


declare @Chartable table (character char (1))
insert into @Chartable (character)
Values ('a'),('A'),('z'),('Z'),('_'),('æ'),('Æ')
SELECT  character COLLATE Latin1_General_BIN 
   from @Chartable order by character COLLATE Latin1_General_BIN
SELECT  character  COLLATE Latin1_General_CI_AS 
   from @Chartable order by character COLLATE Latin1_General_CI_AS
SELECT distinct character COLLATE Latin1_General_BIN 
   from @Chartable order by character COLLATE Latin1_General_BIN
SELECT distinct character  COLLATE Latin1_General_CI_AS 
   from @Chartable order by character COLLATE Latin1_General_CI_AS