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.
