A collation conflict after a server move usually requires identifying the two character expressions being compared, not changing the whole instance. Record the database and column collations, the current query context and any temporary-table definitions. Then choose the comparison rule that matches the application's intended meaning.1, 2
The reference environment is SQL Server 2022 with ordinary non-contained databases. This article does not cover contained-database or cloud-specific catalogue behaviour. Its examples only inspect metadata; no collation change or customer query was executed.
The fault is about meaning as well as syntax
A collation defines comparison and ordering rules. Case sensitivity, accent sensitivity and other language-related options affect whether two values compare equal and where they sort. For non-Unicode character data, the collation also determines the code page and representable characters.1
That makes an apparently simple error repair a business-data decision. A customer code whose uppercase and lowercase forms must remain distinct cannot safely be compared under arbitrary case-insensitive rules merely because those rules remove the error. A search feature may have different requirements from a unique identifier.
The opposite problem also matters: a changed comparison may stop producing an error while silently changing matches, grouping or ordering. Verification must therefore inspect the result semantics, not only whether the query now completes.
Consider a hypothetical application whose restored database retains its established collation while the destination instance uses a different default. A query creates a temporary table and joins its text column to a permanent table. The failure appears only in that code path, even though both the database and application login work normally. That is a comparison-boundary problem, not evidence that the restore corrupted the data.
Map each level separately
Keep four levels in the investigation:
| Level | Why it matters |
|---|---|
| Instance | Supplies defaults for system databases and relevant instance-level identifiers. |
| Database | Supplies defaults for new character columns and expression context, subject to the documented rules. |
| Column | An existing character column has its own defined collation, which can differ from the database default. |
| Expression | An explicit COLLATE clause can choose the rule used at a particular comparison boundary. |
Microsoft explicitly states that changing a database's default collation does not change the collations or data in existing columns.1 Therefore, an inventory containing only one database-level value is insufficient for a cross-database join or a schema with deliberate column overrides.
Temporary tables are stored in tempdb. For the ordinary database scope here, that introduces the destination instance's tempdb collation into temporary-table definitions unless the code specifies the intended alternative. The COLLATE reference documents database_default for a temporary-table column when it should inherit the current user database's default instead.1, 2
Do not treat database_default as a universal synonym for the permanent column's collation. The current database default and the specific column may differ. Also confirm which database is current at object creation and statement execution; the application can change context.
Collect the exact failing comparison
Preserve the complete error, operation and SQL statement structure, with sensitive values redacted. Identify both operands: a column from each database, a temporary-table column, a literal, a variable or an expression produced by a function.
Record the fully qualified objects, data types and collations. Keep varchar and nvarchar distinctions visible. Unicode storage addresses character representation, but it does not eliminate collation-dependent comparison rules. A cast that changes representation is not automatically a correct choice of equality semantics.1
Use approved metadata access. This read-only example adapts the inspection functions shown in Microsoft's collation guide. Run it in the intended user database; a missing metadata result requires a visibility/context check rather than an invented value.
DB_NAME() without an argument identifies the current database, so confirm that connection context before interpreting its collation.3
SELECT
CONVERT(nvarchar(128), SERVERPROPERTY('Collation'))
AS instance_collation,
CONVERT(nvarchar(128), DATABASEPROPERTYEX(DB_NAME(), 'Collation'))
AS current_database_collation,
CONVERT(nvarchar(128), DATABASEPROPERTYEX('tempdb', 'Collation'))
AS tempdb_collation;
Then inspect the actual character-column definitions through an authorised schema script or table-properties view. Do not stop at the query above. A schema script also shows explicit COLLATE clauses, lengths and types that an abbreviated incident screenshot may omit.
If the failing code constructs temporary tables, preserve that definition and the query's database context. If it compares across databases, inspect both sides on the destination. Do not assume two databases share a collation merely because they are hosted on one instance.
Reproduce the semantics safely
Prepare a small disposable comparison case using synthetic values. Include ordinary matches and non-matches, upper/lowercase pairs and accented characters where relevant to the application. The expected results must come from the business rule, not from whichever collation happens to produce a convenient result.
For example, a case-insensitive search may intentionally return differently cased names, while an account identifier might require a stricter distinction. Document both cases before selecting an expression-level override. If sorting matters, include the ordering contract as well as equality.
No sample data is created by this article. A future laboratory reproduction should preserve the actual types, collations and query context while avoiding customer data. A reproduction using only ASCII values may miss the exact semantic risk present in production.
Compare the result set with the known intended outcome. Include joins that should produce no match, because an inappropriate comparison can create additional matches and duplicate downstream work without an obvious SQL error.
Choose the narrowest correct design
For a temporary-table default mismatch, consider defining the temporary character column under the intended user-database rule. Microsoft's database_default option can express that intent where it is genuinely the correct rule.2 If the permanent column deliberately differs from the database default, specify and validate the corresponding design instead.
For a deliberate cross-database integration, choose which comparison semantics the interface promises. An expression-level COLLATE clause may make the boundary explicit. The collation name is a literal identifier in the syntax, not a value that can simply be supplied through a SQL variable.2
Such an override changes how an expression is evaluated; it does not harmonise stored column definitions across the estate. Review the actual execution plan and performance under representative data, because an otherwise correct expression change still needs operational acceptance. Do not claim the performance effect from syntax alone.
A column-level redesign is a schema migration. It needs dependency review, data-compatibility checks and a plan for indexes, constraints and application expectations. A database-default change is broader but still does not rewrite all existing columns. An instance-default change is broader again and, as Microsoft's guide explains, can require substantial rebuild and migration work.1
This article deliberately provides no ALTER DATABASE, column-conversion or instance-rebuild recipe. Those operations should follow a separately reviewed migration plan with recoverable copies and application-owner approval.
Verify results, uniqueness and performance
The acceptance record should cover more than a successful execution. Compare expected matches, non-matches, grouping, ordering and any uniqueness rules affected by the change. Include representative language and character data rather than only the test values that originally exposed the error.
Check the full application path. A query may succeed while returning a different set of account or product identifiers, which can be more serious than the original visible failure. Have the owner confirm the business interpretation before release.
Measure the changed statement with representative data and concurrency in an authorised environment. Preserve its previous definition and relevant plan evidence. A scoped application change should have a normal release rollback route; a data/schema migration may require a separate recovery strategy because reversing configuration does not undo converted or newly accepted data.
If a comparison-only change must be reverted, review operations already performed with its results. A wrongly matched external update cannot be corrected by returning the SQL expression to its earlier form alone.
Learning notes
- A database default does not describe every existing column.
- Removing the error is insufficient if equality or ordering becomes wrong.
- Temporary-table and cross-database boundaries should state their intended comparison rules explicitly.
Acceptance checklist
- [ ] The conflicting expressions and their actual collations are recorded.
- [ ] Intended equality, ordering and uniqueness rules are confirmed by the application owner.
- [ ] Representative character data and cross-database or temporary-table paths return correct results.
- [ ] Performance and completed external effects are checked before closure or rollback.
References
- Collation and Unicode Support - SQL Server | Microsoft Learn. Accessed 2026-11-01.
- COLLATE (Transact-SQL) - SQL Server | Microsoft Learn. Accessed 2026-11-01.
- DB_NAME (Transact-SQL) - SQL Server | Microsoft Learn. Accessed 2026-11-01.