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

by

Troubleshooting Guide: Error 4965 – ALTER TABLE SWITCH Computed Column Mismatch

Why This Error Happens

This error occurs because SQL Server enforces strict schema matching between the source and target tables during a partition switch operation, and computed columns must match exactly in their “computed” status on both sides. The underlying cause is typically a table definition drift, where one table was altered to add a computed column (e.g., via ALTER TABLE ADD) while the corresponding partition table retained a physical, non-computed version of that same column name. This often happens when staging tables are created independently of the production schema rather than being generated via SELECT INTO or scripted directly from the source table’s DDL.

Step-by-Step Resolution Guide

Step 1: Compare Schema Definitions of Both Tables

  • Run sp_help 'YourSourceTable' and sp_help 'YourTargetTable' to compare column properties side-by-side.
  • Specifically check the is_computed column in sys.columns for both tables using the query: SELECT name, is_computed, definition FROM sys.columns c JOIN sys.computed_columns cc ON c.object_id = cc.object_id WHERE c.object_id = OBJECT_ID('YourTable').
  • Identify exactly which column (named in the error message) has mismatched computed status.

Step 2: Drop and Recreate the Staging Table Correctly

  • Do not use CREATE TABLE manually followed by column additions; instead, generate the staging table using SELECT * INTO StagingTable FROM SourceTable WHERE 1=0 to inherit identical computed column definitions.
  • If the staging table already exists, drop the conflicting column with ALTER TABLE StagingTable DROP COLUMN YourColumn.
  • Re-add the column as a computed column using the exact same formula: ALTER TABLE StagingTable ADD YourColumn AS (Expression) PERSISTED (ensure PERSISTED status also matches, as this affects storage behavior during switch).

Step 3: Validate Persisted and Nullability Attributes

  • Confirm that if the source column is PERSISTED, the target column is also marked PERSISTED, as SQL Server 2025 treats persisted and non-persisted computed columns differently during metadata-only switches.
  • Check constraints and collation settings match using sp_columns YourTableName to rule out secondary blocking factors once the computed status is aligned.
  • Rerun the ALTER TABLE ... SWITCH PARTITION command in a test environment (Windows Server 2025 SSMS or Azure Data Studio) to confirm the computed column error (4965) no longer triggers.

Alternative or Verification Steps

  • Verification: Execute ALTER TABLE SourceTable SWITCH PARTITION x TO TargetTable PARTITION y in a transaction block (BEGIN TRAN) first; if it succeeds without Error 4965, commit the transaction.
  • Alternative: If modifying the computed column definition is not feasible due to application dependencies, bypass the computed column mismatch entirely by using a standard INSERT INTO ... SELECT with a DELETE batch process instead of SWITCH, though this sacrifices the instantaneous metadata-only benefit of partition switching.