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.

3
  • >>> 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
    Commented Nov 13, 2019 at 13:18
  • So I actually cannot define a UTF8 collation without losing compatibility for version 2012. Thank you! Commented Nov 13, 2019 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
    Commented Nov 13, 2019 at 16:14

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.