The SQL_Latin1_General_CP1_CI_AS collation is a SQL collation and the rules around sorting data for unicode and non-unicode data are different. The Latin1_General_CI_AS collation is a Windows collation and the rules around sorting unicode and non-unicode data are the same.

– The Windows collation can use an index while comparing unicode and non-unicode, such as nvarchar to varchar, with a slight performance cost. The SQL collation cannot use the index while comparing data in such scenarios.
– Some characters that are treated as independent letters. For example, operator LIKE '%ß%' will return that exact match in SQL collation, while Windows collation will also return LIKE '%ss%' as the expanded character of ß to ss.
– Mixing collations within the database can cause errors such as this: ‘Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AI” in the equal to operation.’
– Collation can be specified in a statement to instruct the SQL engine to use such collation ad-hoc (e.g. SELECT * FROM SomeTable WHERE SomeField COLLATE SQL_Latin1_General_CP1_CI_AS = N'ßeta')

Categories: