Get Collation Conflicts and Cross-Database Dependencies finds real collation mismatches, but assumes you already know what collation actually controls. This post covers the fundamentals directly: what changes with collation, a real case-sensitivity test, and the cross-database trap that script exists to catch.
What Collation Actually Controls
Collation determines two things for character data: sort order (does ‘a’ come before ‘B’?) and comparison rules (does 'Smith' = 'smith' evaluate true?). It’s set at the server level (the default for every new database), the database level (can differ from the server), and can be overridden per-column or even per-expression with COLLATE. Nothing about collation changes what data is stored, only how it sorts and compares.
This instance’s server-level collation:
SELECT SERVERPROPERTY('Collation') AS server_collation;
-- SQL_Latin1_General_CP1_CI_AS
Reading the name itself tells you the behavior: CP1 is the code page, CI means case-insensitive, AS means accent-sensitive. Swap CI for CS and you have a case-sensitive collation with everything else identical.
Case Sensitivity, Tested Directly
The most common way collation actually bites someone: assuming a comparison is case-sensitive (or insensitive) without checking. A real test against three rows differing only in case:
-- Table holds: 'Smith', 'SMITH', 'smith'
SELECT val FROM #CollTest WHERE val = 'smith';
Real result under this database’s default collation (CI_AS):
| val |
|---|
| Smith |
| SMITH |
| smith |
All three match, = is case-insensitive by default here. Overriding the comparison explicitly with COLLATE:
SELECT val FROM #CollTest
WHERE val COLLATE SQL_Latin1_General_CP1_CS_AS = 'smith';
Real result, same source data, only the collation of the comparison changed:
| val |
|---|
| smith |
Only the exact-case match. Same table, same data, same query shape, a different result set purely from the COLLATE clause. This is the direct, testable version of “collation changes what your comparisons actually match,” not a theoretical statement.
The Cross-Database Trap
A database’s collation doesn’t have to match the server’s default, and a query joining across databases (or a temp table compared against a permanent table) with mismatched collations fails with a real, specific error: Cannot resolve the collation conflict. This happens more often than expected because:
- A database restored or migrated from a different server often carries a different collation than the destination server’s default, nobody explicitly chose the mismatch, it just travels with the backup.
tempdbalways uses the server’s default collation, not the calling database’s. A temp table built from a database with a non-default collation, then compared against that same database’s own columns, can conflict with itself.- Linked servers and cross-database queries surface this the moment two differently-collated text columns meet in a
JOINorWHEREclause.
Get Collation Conflicts and Cross-Database Dependencies finds exactly this, every database’s collation against the server default, flagging real mismatches before they surface as a production query failure.
Fixing a Mismatch
- Per-query, the fast fix: add
COLLATE DATABASE_DEFAULT(or an explicit collation name) to the specific column causing the conflict. Safe, immediate, doesn’t touch stored data. - Per-column, permanent:
ALTER TABLE ... ALTER COLUMN ... COLLATE ...changes the column’s stored collation. Requires the column to not be part of an index that depends on the current collation without rebuilding it, test this on a non-production copy first. - Database-level, rare: changing an entire database’s collation is disruptive enough (every character column, every index, potentially every comparison in every stored procedure) that it’s almost never the right first move compared to fixing the specific columns actually in conflict.
Best Practices
- Don’t assume comparison case-sensitivity, check
SERVERPROPERTY('Collation')and the specific database’s collation directly, as this post’s own test shows, the difference is real and testable, not academic. - When restoring or migrating a database onto a new server, check its collation against the destination server’s default before anything else touches it.
- Remember tempdb always uses the server’s collation, a common source of “it worked in my session but not in this stored procedure” confusion when a temp table gets compared against a differently-collated real table.
- Fix conflicts at the column level with an explicit
COLLATEclause before considering a broader collation change.
Related Scripts
- Get Collation Conflicts and Cross-Database Dependencies
- Get Database Inventory, includes collation per database
Leave a Reply