SQL Server Collation: How to Change Server, Database and Column Collation Safely

Collation decides how SQL Server compares and sorts text: whether “a” equals “A”, whether “e” equals “é”, how characters are ordered, and which code page stores varchar data. It’s chosen once during setup and usually forgotten, until a join fails with a collation conflict, a query can’t find a column called id, or a migration moves a database to a server with a different default.

This guide explains what the parts of a collation name mean, the four levels where collation applies, how to change the server, database and column collation safely, why tempdb causes most conflicts, the Turkish “I” problem, and the performance trap of mixing varchar and nvarchar under SQL collations.

Quick Answer: How to Change SQL Server Collation Safely

A SQL Server collation change is safe when you decide the level first, because each level has a different blast radius:

• Database: ALTER DATABASE ... COLLATE changes metadata and the default for new objects. It does not change the collation of existing user columns.
• Column: ALTER TABLE ... ALTER COLUMN changes one column at a time, is blocked by dependent indexes, keys and constraints, and can block a busy table.
• Server: changing it means rebuilding master, model, msdb and tempdb. Microsoft’s procedure is to export the data, drop the user databases, rebuild, recreate and import. Most teams migrate to a new instance instead.
• Whatever the level: inventory first, take a full backup and test the restore, test the application, and define the rollback before you start. The lowest-risk rollback is leaving the original database or instance untouched until sign-off.

The decision table below compares the options. Each row is labelled Microsoft docs or Field practice (our recommendation, not a Microsoft requirement).

Reading a Collation Name

A collation name is a list of rules. Take Latin1_General_100_CI_AS_SC_UTF8:

Collations whose names start with SQL_, such as SQL_Latin1_General_CP1_CI_AS, are legacy SQL collations kept for backward compatibility. Collations without that prefix are Windows collations, which apply the same rules to varchar and nvarchar. For new databases, prefer a Windows collation, ideally version 100 or later.

-- All collations available on the instance
SELECT name, description
FROM sys.fn_helpcollations()
WHERE name LIKE N'Turkish%' OR name LIKE N'Latin1_General_100%'
ORDER BY name;

The Four Levels of Collation

Collation is set at four levels, and each one inherits from the level above when nothing is specified:

• Server (instance): chosen during setup. It’s the collation of master, model, msdb and tempdb, and the default for new databases. It also governs the names of variables, GOTO labels and temporary tables.
• Database: set with CREATE DATABASE ... COLLATE. It applies to the database’s metadata (object and column names) and is the default for new columns.
• Column: each char, varchar, nchar and nvarchar column has its own collation, fixed when the column is created.
• Expression: COLLATE in a query overrides the collation for one comparison or ORDER BY.

-- Server, database and column collations at a glance
SELECT SERVERPROPERTY('Collation') AS server_collation;

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

-- Columns whose collation differs from their database's default
SELECT
    s.name  AS schema_name,
    t.name  AS table_name,
    c.name  AS column_name,
    c.collation_name
FROM sys.columns AS c
JOIN sys.tables  AS t ON t.object_id = c.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE c.collation_name IS NOT NULL
  AND c.collation_name <> CONVERT(sysname, DATABASEPROPERTYEX(DB_NAME(), 'Collation'))
ORDER BY schema_name, table_name, column_name;

The last query matters more than it looks. When a database’s collation is changed, existing columns keep their old one, so many older databases have a mix of collations that nobody planned.

Collation Conflicts and Why tempdb Is Usually Involved

When two expressions with different collations are compared and neither has priority, SQL Server can’t decide which rules to use and raises error 468:

The most common cause is tempdb. Temporary tables are created in tempdb, so their string columns take the server collation, not the user database’s. If a database uses Turkish_CI_AS on a server installed with SQL_Latin1_General_CP1_CI_AS, every join between a temp table and a real table fails.

The fix belongs in the temp table definition, not in every query:

CREATE TABLE #Customers
(
    CustomerCode varchar(20) COLLATE DATABASE_DEFAULT NOT NULL,
    CustomerName nvarchar(200) COLLATE DATABASE_DEFAULT NULL
);

-- Or, in a single query, resolve the conflict explicitly
SELECT o.OrderID
FROM dbo.Orders AS o
JOIN #Customers AS c
  ON o.CustomerCode = c.CustomerCode COLLATE DATABASE_DEFAULT;

Tip: SELECT … INTO #temp copies the source columns’ collation, so it doesn’t cause conflicts. Problems come from CREATE TABLE #temp with string columns and no COLLATE clause. Contained databases avoid the issue entirely: their temp tables use the database collation.

Applying COLLATE to a column inside a WHERE or JOIN has a cost: the expression is no longer the indexed column, so an index seek on that column can turn into a scan. Put the COLLATE on the side that isn’t indexed, which is usually the temp table or the parameter.

The Turkish “I” Problem

Turkish has four letters where most languages have two: dotted i/İ and dotless ı/I. Under Turkish collations, the uppercase of i is İ, not I. That is correct for Turkish text, and surprising for code written with English rules:

SELECT
    CASE WHEN N'istanbul' = N'ISTANBUL' COLLATE Latin1_General_100_CI_AS
         THEN 'equal' ELSE 'not equal' END AS latin_ci,   -- equal
    CASE WHEN N'istanbul' = N'ISTANBUL' COLLATE Turkish_100_CI_AS
         THEN 'equal' ELSE 'not equal' END AS turkish_ci, -- not equal
    UPPER(N'id' COLLATE Turkish_100_CI_AS)   AS upper_tr; -- İD

The same rule applies to object names, because metadata follows the database collation. In a database with Turkish_CI_AS, a column named ID isn’t found by a query that writes id, and an application or ORM built on a Latin collation fails with “Invalid column name”. This is one of the most frequent issues when packaged software is installed on a Turkish server, or when a Turkish database is moved behind an application that was tested elsewhere.

The opposite mistake also exists: storing Turkish text under a Latin collation. Sorting puts Ç, Ğ, İ, Ö, Ş and Ü in the wrong places, and case-insensitive search doesn’t match “ı” with “I” as Turkish users expect. For Turkish business data, a Turkish Windows collation such as Turkish_100_CI_AS is usually the right choice, as long as the application is written for it.

The varchar/nvarchar Trap with SQL Collations

Under a SQL collation such as SQL_Latin1_General_CP1_CI_AS, varchar and nvarchar are compared with different rules. When an application sends an nvarchar parameter (the default in many drivers and ORMs) against a varchar column, SQL Server converts the column, not the parameter, and an index seek becomes a scan of the whole index.

Under a Windows collation the same comparison can still use a seek, because both types follow the same rules. That’s one more reason to prefer Windows collations for new databases, and to make sure applications send parameters with the same type as the column.

How to Change the Database Collation

ALTER DATABASE SalesDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE SalesDB COLLATE Turkish_100_CI_AS;
ALTER DATABASE SalesDB SET MULTI_USER;

What this does and doesn’t do:

• It changes the collation of metadata and the default for new columns.
• It doesn’t change existing columns. Their data and indexes stay in the old collation.
• It needs exclusive access to the database.
• It fails if schema-bound objects depend on the current collation, such as schema-bound views and functions, computed columns and CHECK constraints. Those have to be dropped first and recreated afterwards.

How to Change a Column’s Collation

ALTER TABLE dbo.Customers
ALTER COLUMN CustomerName nvarchar(200) COLLATE Turkish_100_CI_AS NOT NULL;

The statement fails if the column is part of an index, a primary or foreign key, a statistics object, a computed column or a CHECK constraint. In practice a column change is a small project: script those objects, drop them, change the column, recreate them, and update statistics. On large tables it’s a size-of-data operation that writes the whole table to the log.

To change every column in a database, it’s often cleaner to create a new database with the target collation, create the schema there and move the data, than to run hundreds of ALTER COLUMN statements in place.

How to Change the Server Collation

The server collation is the collation of the system databases. Changing it means rebuilding them:

.\setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER `
  /SQLSYSADMINACCOUNTS="DOMAIN\DBA-Admins" /SAPWD="<StrongPassword>" `
  /SQLCOLLATION=Turkish_100_CI_AS

Because of that, a server collation change is usually done by installing a new instance with the right collation and migrating to it, often together with an upgrade. Our SQL Server upgrade guide lists the server-level objects to move.

Risk and Rollback: Which Collation Change Fits?

System Databases, tempdb and Contained Databases

The server collation is the default collation for every system database and for new user databases. It also decides the collation of objects in tempdb, such as temporary tables. That is why a database with a different collation from the server so often hits error 468 on temp table joins.

-- Server and tempdb collation, and every database's collation and containment
SELECT SERVERPROPERTY('Collation')            AS server_collation,
       DATABASEPROPERTYEX('tempdb', 'Collation') AS tempdb_collation;

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

Two consequences matter when you plan a change:

• A server rebuild changes the server default and tempdb only. Existing user databases keep their own collation, so after a rebuild you can create exactly the mismatch you were trying to remove.
• Changing a database collation does not change tempdb. Temp tables with string columns and no COLLATE clause will still follow the server collation.

A contained database avoids the dependency on the instance and tempdb collations. Microsoft documents this behavior:

Field practice: treat containment as a design decision made for portability and review its feature restrictions in the Microsoft documentation first. Do not convert a database only to hide collation conflicts.

Migration Approach: Change Collation by Moving, Not Rebuilding

This is Field practice. It keeps the original database or instance intact, which makes the rollback a cutover rather than a restore:

1. Inventory. Record server, tempdb, database and column collations, containment, and the indexed columns that would block ALTER COLUMN (queries below).
2. Choose the target collation per database and confirm what the application vendor supports. Test identifier case and the Turkish “I” behavior on a restored copy.
3. Prepare the target. Create the new database with CREATE DATABASE ... COLLATE, or install the new instance with the target SQLCOLLATION. Script the schema, including indexes, constraints, permissions and server-level objects.
4. Load the data with bcp or INSERT ... SELECT. Microsoft’s own server collation procedure uses bcp for the export and import.
5. Validate. Compare row counts and sample query results, run the application test suite, and check plans for new scans caused by type or collation mismatches.
6. Cut over and keep the old copy read-only or offline until the business signs off, then remove it.

Read-only inventory queries for step 1. These are Field practice, not Microsoft samples; run them in a test environment before relying on the output. They list candidates and are not an exhaustive dependency scan: computed columns, CHECK constraints, schema-bound objects and foreign keys need their own review.

-- Run in each user database: how many string columns use which collation?
SELECT c.collation_name, COUNT(*) AS column_count
FROM sys.columns AS c
JOIN sys.tables  AS t ON t.object_id = c.object_id
WHERE c.collation_name IS NOT NULL
GROUP BY c.collation_name
ORDER BY column_count DESC;

-- Indexed or key string columns that differ from the database default
-- (they block ALTER COLUMN until the index or key is scripted and dropped)
SELECT s.name AS schema_name, t.name AS table_name, c.name AS column_name,
       i.name AS index_name, i.is_primary_key, i.is_unique_constraint, c.collation_name
FROM sys.index_columns AS ic
JOIN sys.indexes AS i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
JOIN sys.tables  AS t ON t.object_id = c.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE c.collation_name IS NOT NULL
  AND c.collation_name <> CONVERT(sysname, DATABASEPROPERTYEX(DB_NAME(), 'Collation'))
ORDER BY schema_name, table_name, index_name;

The in-place statements in this article come from Microsoft’s documentation: ALTER DATABASE ... COLLATE, ALTER TABLE ... ALTER COLUMN, and setup /ACTION=REBUILDDATABASE ... /SQLCOLLATION. Run any of them first on a restored copy.

Choosing a Collation for a New Server or Database

• Follow the application vendor’s requirement first. ERP and packaged software often support only one collation.
• Use the same collation for the server and the user databases when you can. It removes the tempdb conflicts.
• Prefer Windows collations over SQL collations, and version 100 or 140 over older versions.
• Use a Turkish collation for Turkish data, after testing the application for the “I” problem.
• Consider _SC_UTF8 collations only after testing; they save space for mostly Latin text in varchar, but not for every workload.
• Decide case sensitivity deliberately. A CS collation makes object names, data comparisons and, at server level, variable names case-sensitive.

Checklist Before a Collation Change

• Current server, database and column collations are documented.
• Dependent indexes, keys, constraints, computed columns and schema-bound objects are scripted.
• A full backup is taken and a restore is tested.
• The application is tested against the new collation, including identifier case and LIKE searches.
• Temp table definitions use COLLATE DATABASE_DEFAULT.
• The maintenance window is sized for rewriting the affected tables.
• For a server change, every server-level object is scripted before the rebuild.

Sources and Review Date

This article was checked against the following Microsoft Learn pages on 19 October 2026. Statements labelled Field practice are our own recommendations and are not stated in those pages. The catalog queries in this article are read-only. T-SQL tested on SQL Server 2025 (build 17.0.4055.5).

• Set or change the server collation
• Set or change the database collation
• Set or change the column collation
• Contained database collations
• Rebuild system databases