.svg)
Article Summary:
How collation mismatches arise during SQL Server migrations, how they affect queries and how to remediate them safely.
A SQL Server migration can finish successfully and still leave a subtle collation problem behind. Queries may fail with a collation conflict, return results in an unexpected order or become slower because SQL Server has to convert character data at runtime. The safest response is to identify the exact boundary before changing database settings or application code.
Where collation is applied
Microsoft's collation guidance explains that collation can apply at server, database, column and expression level. The instance collation also influences system databases and tempdb. A migrated user database can therefore keep its original default while temporary objects are created with a different one.
- Server and system database collation
- User database default
- Individual character columns
- Variables, literals and explicit COLLATE clauses
- Temporary tables and table variables
Recognise the two common symptoms
The obvious symptom is an error when two incompatible character expressions are compared. The quieter symptom is an implicit conversion. A query may still run, but the conversion can affect cardinality estimates or prevent an efficient index seek.
Use the actual execution plan, Query Store history and runtime statistics to confirm the change. Microsoft's query performance troubleshooting guide recommends comparing environments systematically rather than assuming the database engine version is the cause.
- Collation conflict errors
- CONVERT_IMPLICIT warnings in plans
- Scans replacing expected seeks
- Different estimates or join choices
- Regressions involving temp tables or cross-database joins
Inventory before changing anything
Capture the source and destination instance collations, every database default and any character columns that differ. Search stored procedures and application SQL for explicit COLLATE clauses. Identify temporary tables, linked servers, distributed queries and reporting databases that cross collation boundaries.
Keep a representative workload. A metadata comparison alone will not show which mismatches matter to users.
Choose the narrowest safe remedy
A query-level COLLATE clause can resolve a deliberate boundary, and COLLATE DATABASE_DEFAULT can help temporary objects follow the user database. A column change may be appropriate when the data model is wrong, but it can require index work and application testing. Standardising the whole instance is the broadest option.
Microsoft's server collation procedure describes rebuilding system databases or moving data. Treat either as a planned change with tested backups, scripted server objects, security mapping and rollback.
Add collation to the migration gate
Agree the target standard during discovery, then test schema deployment, tempdb interaction, cross-database queries and representative plans before cutover. Re-run the same checks after migration. This converts collation from an emergency diagnosis into an explicit acceptance criterion.
- Document the target collation
- Test temporary and cross-database workloads
- Compare actual plans and durations
- Validate sorting and case behaviour
- Record exceptions and their owners
Talk to a database specialist
A change to support, licensing, cloud architecture or a production database should be based on evidence from your own estate. Pro DBA can assess the current position, identify the material risks and help you plan the next step. Contact the Pro DBA team to discuss your environment.
