Product:
Microsoft SQL server 2016
Problem:
Why should collation be an issue?
Solution:
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;
More information:
https://kimconnect.com/latin1_general_ci_ai-vs-sql_latin1_general_cp1_ci_as/
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’)
https://docs.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-ver16
https://www.sqlshack.com/sql-server-collation-introduction-with-collate-sql-casting/