Collation is used by SQL Server to dictate how language is interpreted.
Collation settings control the following:
Case sensitivity ('A'='a'? or 'A' = 'A'?)
Accent sensitivity ('AIMÉE' = 'AIMEE'?)
Latin1_General_CI_AI = Latin code page, Case Insensitive, Accent Insensitive
Collation can be set at the server, database, and column level.
Collation can also be explicitly forced in joins and sorting.
select first_name from [users]
order by first_name COLLATE SQL_Latin1_General_CP1_CS_AS
Let's go through some collation examples below.
SQL has no idea what code page to associate your inserted data to and questions you.
The unicode data type is a double byte standard which SQL knows how to display.
SQL has an idea of what code page to associate your inserted data to and no longer questions you.
Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS"
and "Chinese_Taiwan_Stroke_BIN" in the equal to operation.
You would assume joining a unicode datatype against a specific collation would work,but it doesn't.
You'll need to specify the collation used in the join because the collation for the two sets of data are different.