DB-유

SQL Server Collation: Choosing and Changing It Safely

· mssql, collation, tsql

Nobody thinks about collation until "Cannot resolve the collation conflict" shows up, usually because a database restored from another server doesn't match tempdb. I've been on the receiving end of that message enough times to want the topic written down. Collation decides how SQL Server sorts and compares strings (does a equal A, does é sort after e) and what code page a varchar column uses. By the time it hurts, changing it is a project rather than a setting.

Here is how to pick one for a new instance, how to see what you have, and the realistic options for changing it at column, database and server level.

What do you have now?

SELECT SERVERPROPERTY('Collation') AS server_collation;

SELECT name, collation_name FROM sys.databases ORDER BY name;

SELECT c.name, c.collation_name, t.name AS type_name, OBJECT_NAME(c.object_id) AS table_name
FROM sys.columns c
JOIN sys.types t ON t.user_type_id = c.user_type_id
WHERE c.collation_name IS NOT NULL
  AND c.collation_name <> DATABASEPROPERTYEX(DB_NAME(), 'Collation')
ORDER BY table_name, c.name;

The last query lists columns whose collation differs from the database default, which is where most surprises hide.

Choosing one for a new instance

The name tells you what the collation does. Latin1_General_100_CI_AS breaks down as: Latin1_General (the language rules), 100 (the version of the sort rules, introduced with SQL Server 2008), CI (case-insensitive), AS (accent-sensitive). Additional suffixes: KS (kana-sensitive), WS (width-sensitive), SC (supplementary characters, for emoji and other characters outside the base plane), UTF8 (2019 only, varchar stores UTF-8).

What I do:

  • For a new instance whose applications are mostly Windows and .NET: Latin1_General_100_CI_AS_SC, or plain Latin1_General_100_CI_AS before 2012 where _SC did not exist.
  • If it must talk to older instances that use the installer default SQL_Latin1_General_CP1_CI_AS, keep that for compatibility. The SQL_ collations are older sort rules kept for backwards compatibility; they are fine, but they behave differently from the Windows collations for varchar comparisons and cannot be mixed painlessly.
  • Match the collation of any instance you regularly restore databases from. Mixed collations between tempdb and user databases is the number one source of runtime errors.
  • Case-sensitive (CS) or binary (BIN2) collations only when the application actually requires it; they turn every WHERE name = 'smith' into a support ticket.

The collation is set on the Server Configuration → Collation tab during setup, or with /SQLCOLLATION for an unattended install.

Changing a column

Column-level changes are the safe, incremental option, and the only one I do without a deep breath. ALTER TABLE rebuilds the column in the new collation:

ALTER TABLE dbo.Customers
    ALTER COLUMN LastName nvarchar(100) COLLATE Latin1_General_100_CI_AS NOT NULL;

It fails if the column is part of an index, a primary key, a foreign key, a check constraint or a computed column. Drop those first, alter, and recreate. For a varchar column, changing to a collation with a different code page can also change the data if characters do not exist in the new code page; test on a copy.

Changing the database default

ALTER DATABASE Sales SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE Sales COLLATE Latin1_General_100_CI_AS;
ALTER DATABASE Sales SET MULTI_USER;

This only changes the default for new columns and the collation used for object names and variables. Every existing column keeps its collation. So after this statement you still need to alter each column as above, which is why the first query matters. Generating the ALTER TABLE statements from sys.columns is straightforward; running them across a large schema with all the constraint drops is the actual work.

ALTER DATABASE ... COLLATE also fails if any schema-bound object (indexed view, WITH SCHEMABINDING function) depends on the collation, or if a CHECK constraint or computed column references a string column.

Changing the server collation

The server collation is the collation of master, model, msdb and tempdb. The only supported way to change it is to rebuild the system databases, which wipes them:

# from the SQL Server installation media, run as administrator
.\setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER `
    /SQLSYSADMINACCOUNTS="CONTOSO\DBA-Team" /SAPWD="Str0ng!Passw0rd" `
    /SQLCOLLATION=Latin1_General_100_CI_AS

Before running it: script all logins (sp_help_revlogin), Agent jobs, operators, alerts, linked servers, Database Mail, server configuration and detach or back up every user database. Afterwards, restore or reattach the user databases and recreate everything else. User databases keep their own collation, so unless you also fix them as described above you have only fixed tempdb.

On Linux the same operation is sudo /opt/mssql/bin/mssql-conf set-collation, which prompts for the new collation and also rebuilds the system databases.

Did it work?

SELECT SERVERPROPERTY('Collation') AS server_collation,
       DATABASEPROPERTYEX('Sales', 'Collation') AS db_collation,
       DATABASEPROPERTYEX('tempdb', 'Collation') AS tempdb_collation;

All three should match. Then run the column query from the top again; it should return nothing. Finally, test the pattern that used to fail:

CREATE TABLE #t (Name nvarchar(50));
INSERT #t VALUES ('Smith');
SELECT c.* FROM dbo.Customers c JOIN #t t ON t.Name = c.LastName;
DROP TABLE #t;

Errors you'll see along the way

  • Msg 468: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CI_AS" in the equal to operation. A join or comparison between columns of different collations, typically a temp table (using tempdb's collation) and a user table. Quick fix is COLLATE DATABASE_DEFAULT on the temp table column; the real fix is aligning collations.
  • Msg 5075: The column 'LastName' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Drop the indexed view or schema-bound function, alter the collation, recreate.
  • Msg 5074: The index 'IX_Customers_LastName' is dependent on column 'LastName'. Expected from ALTER COLUMN; drop and recreate the index.
  • sp_executesql or a linked server query fails with Msg 468 even after aligning the database. Variables and literals in a contained database or linked server context use the server collation. COLLATE on the expression sorts it out.

Choose carefully at install time and keep every instance in an environment on the same collation. If you inherit a mismatch, I'd rather be disciplined about COLLATE DATABASE_DEFAULT on temp objects and fix columns table by table than rebuild a server.