SQL SERVER – Change Database and Table Collation

To Change Database collation, I separate its default from existing character-column settings. The customer moved from case-insensitive to case-sensitive behavior.

SELECT name, collation_name FROM sys.databases WHERE name = DB_NAME();
SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName,
       c.collation_name, c.max_length, c.is_nullable
FROM sys.tables t JOIN sys.schemas s ON s.schema_id=t.schema_id
JOIN sys.columns c ON c.object_id=t.object_id
WHERE c.collation_name IS NOT NULL ORDER BY s.name,t.name,c.column_id;
-- Historical case-insensitive to case-sensitive change, reviewed on a copy:
-- ALTER DATABASE [CollationTest] COLLATE SQL_Latin1_General_CP1_CS_AS;
-- ALTER TABLE dbo.TestTable ALTER COLUMN Col1 nchar(10)
--     COLLATE SQL_Latin1_General_CP1_CS_AS NULL;

There is no table-wide collation that replaces every column setting. Inventory schemas, columns and dependencies. ALTER COLUMN must preserve type, length and nullability. Indexes, computed columns and schema-bound dependencies can prevent the change.

Test comparisons, uniqueness and joins, including tempdb collation differences. Previously equal values can compare differently. The changed database default also affects new objects and other database-level behavior.

My old script forced SINGLE_USER WITH ROLLBACK IMMEDIATE. That disconnects users and rolls back work. Plan access modes, dependency changes, backups and validation explicitly. Verify return to normal access afterward.

Reference: Database and existing-column collation changes.

Related reading

• Comprehensive Database Performance Health Check
• LinkedIn

A database default is not a rewrite of existing column collations, it is a default with its own effects.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.