0

I am working on an application I need to make cross-database.

One of the things I stumbled on is that SQL Server by default has a case-insensitive collation defined, which makes queries on NVARCHAR/VARCHAR fields match on things that the application doesn't expect it to match on (i.e. case differences).

I tried to change the collation of the database to SQL_Latin1_General_CP1_CS_AS (as I could see that the current one was SQL_Latin1_General_CP1_CI_AS). This caused a different failure because the field names were case sensitive (queries where a field was sometimes lower-case would fail when the original table definitions were all upper-case). Since it's a large application, this is not a change that can be adopted.

I have now adapted the creation scripts to add COLLATE SQL_Latin1_General_CP1_CS_AS to the end of all NVARCHAR and VARCHAR fields of tables. The behavior is now back to what was expected (the queries are case-sensitive). So now, the question...

Will this collation change cause any other problems I cannot see? I can see that there are Unicode (_UTF8) collations. Should I add the _UTF8 prefix for the NVARCHAR field or does it make no difference to simply add the normal SQL_Latin1_General_CP1_CS_AS collation to all VARCHAR and NVARCHAR fields equally?

I'm interested in the behavior of SQL Server 2012 and onwards.

  • >>> I can see that there are Unicode (_UTF8) collations....I'm interested in the behavior of SQL Server 2012 and onwards<<< this is starting with SQL Server 2019 (15.x). – sepupic Nov 13 '19 at 13:18
  • So I actually cannot define a UTF8 collation without losing compatibility for version 2012. Thank you! – Daniel Gray Nov 13 '19 at 16:03
  • >>>Will this collation change cause any other problems I cannot see<<<if tempdb collation is different the code that uses temporary tables with join/comparison on string fields will fail until you use COLLATE clause – sepupic Nov 13 '19 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.