I'm new to SQL Server, and wasn't aware of the possibility that T-SQL may be case insensitive.
I have been normalizing entities by splitting tables and constraints. I'm scared that if I change the collation now, that some of those constraints may be invalid if, for example, a 'case insensitive' foreign key value points to a PK value with different case.
If I were to run this query on the DB (tested on a basic database):
ALTER DATABASE <db_name>
COLLATE SQL_Latin1_General_Cp1_CS_AS ;
GO
What would happen if some constraints were violated?
Is there any other reason that I should be careful making a database-wide change such as changing the collation?
Best Answer
Changing a database's Collation doesn't affect existing columns. It affects new non-XML string columns created that don't specify the
COLLATE
clause (including table variables), string literals, and variable values (not variable name resolution, which is determined by the Instance-level Collation). Meaning, something like the following will be affected:This change will also affect Database-level meta-data, such as names of schemas, objects, columns, indexes, etc. Meaning, the following two scenarios will be affected:
and:
In both of those examples, they would work in a case-insensitive Collation, but return nothing or error, respectively, in a case-sensitive Collation.
Finally, as @JonathanFite was kind enough to remind me of, changing the DB Collation may impact queries involving temporary tables. The default Collation for string columns in temporary tables (not table variables) is the default Collation for
[tempdb]
(which should be the same as[model]
, which should be the Instance default, unless someone restored[model]
from a server that had a different default Collation), not the Collation of the local Database. Meaning, even though the temp tables get created each time and thus you might expect them to act like "newly created tables" with the new Collation, they will in fact act like "existing tables" and will continue to behave as they did prior to the Collation change. If you need string columns in temporary tables to use the new Collation, you will need to explicitly set their Collation usingCOLLATE DATABASE_DEFAULT
in theCREATE TABLE
statements.Hence you really need to do a lot of testing!
If you want to change existing columns then you will need to drop existing constraints, issue an
ALTER TABLE ... ALTER COLUMN
and then recreate the Constraints. You will also need to rebuild indexes that use any column that has its Collation changed as the sort order might be different.Also, it is best to not use Collations starting with
SQL_
. Instead useLatin1_General_100_CS_AS
. The Collations starting withSQL_
have been obsolete (even if not officially deprecated) since SQL Server 2000 was released. Their handling ofVARCHAR
/ 8-bit data is obsolete and is not inline with newer behavior. Unfortunately, for backwards compatibility reasons, the default Collation for US English installations used to be theSQL_Latin1
Collations, as noted in the Using SQL Server Collations MSDN page:This is also noted in the default Collation chart in the Collation Settings in Setup MSDN page (hit Control-F and paste in
sql_latin
). I believe this default changed to a Windows Collation starting in SQL Server 2014, but the documentation, even for the SQL Server 2016 setup, still points to the 2008 R2 setup page for Collations.Below is a script to see some of the behavior differences when changing a Database's Collation: