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
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
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
NVARCHAR fields equally?
I'm interested in the behavior of SQL Server 2012 and onwards.