Microsoft SQL server 2016
Why should collation be an issue?
Ensure that the SQL server and the database all have the same collation setting from the beginning – and try to not change it.
But some products demand a specific collation setting (like cognos) – that is equal to the SQL server.
To get around differences, between databases, in each select statement use collate;
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’)