How to fix Error 4945 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 4945 (ALTER TABLE SWITCH Collation Mismatch)

Why This Error Happens

Error 4945 occurs when the source and target tables involved in an ALTER TABLE...SWITCH operation have a matching column that is defined with different collations. SQL Server requires that partition switching operations occur between tables with identical schema definitions, including column-level collation settings, because the underlying data pages must be byte-for-byte compatible without requiring a conversion pass. This commonly happens when tables are created at different times under different database-level collation defaults, or when a column was explicitly assigned a specific collation (e.g., SQL_Latin1_General_CP1_CI_AS vs. Latin1_General_100_CI_AS) in one table but not the other.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Column and Current Collations

  • Run the following query against both the source and target tables to compare collation settings for every character-based column:
SELECT t.name AS TableName, c.name AS ColumnName, c.collation_name
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name IN ('SourceTable', 'TargetTable')
AND c.collation_name IS NOT NULL;
  • Note the exact column name reported in the Error 4945 message (%.*ls) and locate it in both result sets.
  • Confirm whether the mismatch is due to table-level collation inheritance or an explicit COLLATE clause on one table only.

Step 2: Alter the Column to Match Collations

  • Once you have identified which table has the “incorrect” or outdated collation, run an ALTER COLUMN statement to align it with the other table.
  • Use the exact collation name retrieved in Step 1 (do not guess, as SQL_ prefixed and _100_ version collations are NOT interchangeable):
ALTER TABLE dbo.TargetTable
ALTER COLUMN YourColumnName VARCHAR(50) COLLATE Latin1_General_100_CI_AS;
  • If the column is part of a Primary Key, Foreign Key, or has an existing Index, you must drop those constraints/indexes before running the ALTER COLUMN command and recreate them afterward.

Step 3: Verify Database Default Collation Settings

  • If this error persists across multiple tables, check if the parent databases themselves have different default collations, which is common after restoring a database from an older SQL Server instance into SQL Server 2025.
SELECT name, collation_name FROM sys.databases WHERE name IN ('DB_Source', 'DB_Target');
  • While you generally cannot easily change a database’s default collation after creation without recreating the DB, ensure new columns added via scripts explicitly declare the COLLATE clause matching your partitioned table’s schema to prevent recurrence.

Step 4: Re-run the Partition Switch Command

  • Execute the original ALTER TABLE...SWITCH statement again after the column collations have been synchronized.
ALTER TABLE StagingTable SWITCH TO PartitionedTable PARTITION 5;
  • Check the Messages tab in SQL Server Management Studio (SSMS) to confirm the command completed successfully with “0 rows affected,” indicating the schema now matches.

Alternative or Verification Steps

  • Verify the Fix: Re-query the sys.columns system view (as shown in Step 1) for both tables; the collation_name values for every matching column should now be identical strings.
  • Alternative Workaround: If altering the collation directly causes conflicts with existing indexed views or is blocked by active replication, create a new staging table with the correct target collation, use INSERT INTO... SELECT to migrate the data, and then rename the new table to replace the old one before attempting the switch again.