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
€ character which is ordered once before and in the other collation after the "normal" alphabet.
The catalogue table sys.columns shows the collation for each character-based column.
When the column is created the collation is copied from the database default, unless explicitly stated. It is not inherited in a run-time sense. So, if the DB collation changes, every columns' collation remains as is until explicitly altered.
