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

by

Troubleshooting Guide: SQL Server Error 4966 – Computed Column Mismatch During ALTER TABLE SWITCH

Why This Error Happens

This error occurs because SQL Server enforces strict schema-matching rules for partition switching, and the computed column definitions in the source and target tables are not byte-for-byte identical in their formula expressions. Even minor differences, such as whitespace, column casing, function order, or implicit data type conversions within the computed column expression, will cause SQL Server to reject the switch operation. This commonly happens after a schema drift occurs between staging and production tables, or when tables were created at different times using slightly different T-SQL scripts or ORM-generated DDL.

Step-by-Step Resolution Guide

Step 1: Compare the Computed Column Definitions

  • Run the following query against both the source and target tables to extract the exact definition text:
SELECT name, definition, is_persisted 
FROM sys.computed_columns 
WHERE object_id = OBJECT_ID('YourSchema.YourTable');
  • Execute this for both tables involved in the SWITCH operation and compare the definition column output character-by-character.
  • Pay close attention to hidden differences like extra parentheses, spacing, or ISNULL() wrapping that may not be visible in SSMS object scripting.

Step 2: Drop and Recreate the Mismatched Column

  • Once you identify the discrepancy, drop the computed column on the table with the incorrect definition.
  • Use an explicit ALTER TABLE statement to recreate it, ensuring the expression string matches exactly, including the PERSISTED keyword if used on the other table.
  • Example fix:
ALTER TABLE YourSchema.TargetTable DROP COLUMN ComputedColName;
ALTER TABLE YourSchema.TargetTable ADD ComputedColName AS (Expression) PERSISTED;
  • Ensure the PERSISTED state matches on both tables; a non-persisted column cannot switch with a persisted one even if the formula text matches.

Step 3: Validate Underlying Base Column Data Types

  • Confirm that the data types, collation, and nullability of the base columns referenced inside the computed column formula are identical between the two tables.
  • Check collation explicitly if the computed column involves string concatenation or comparison logic:
SELECT name, collation_name, system_type_id 
FROM sys.columns 
WHERE object_id = OBJECT_ID('YourSchema.YourTable');
  • Align any mismatched collations using ALTER TABLE ... ALTER COLUMN with the COLLATE clause before retrying the switch.

Step 4: Rebuild Indexes and Retry the Switch

  • After altering the computed column, rebuild any dependent indexes since dropping a computed column invalidates associated indexes.
ALTER INDEX ALL ON YourSchema.TargetTable REBUILD;
  • Re-run the original ALTER TABLE ... SWITCH statement to confirm the schema now matches.

Alternative or Verification Steps

  • Verify success by running the SWITCH command again; if it completes without Error 4966, query sys.partitions on both tables to confirm row counts have transferred as expected.
  • As an alternative, if recreating the column is not feasible due to downstream dependencies, use sp_help or generate a scripted comparison report via SSMS “Generate Scripts” on both tables to visually diff the full CREATE TABLE statements side-by-side before manually patching the mismatch.