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

by

Troubleshooting Guide: SQL Server Error 3822 – Persisted Computed Columns with Geometry/Geography Methods

Why This Error Happens

This warning occurs when a table contains a persisted computed column that relies on a geometry or geography method marked as non-deterministic or imprecise (such as STDistance, STIntersects, or other spatial calculations). SQL Server 2025 cannot guarantee that the persisted value stored on disk accurately reflects the current result of the method, especially after a binary upgrade, service pack, or cumulative update changes the underlying spatial library (Microsoft.SqlServer.Types). As a result, the engine flags the heap to warn administrators that the cached computed values may be stale and require manual recalculation.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Table and Column

  • Run the following query to locate the specific heap and persisted computed column referenced in Error 3822:
  SELECT t.name AS TableName, c.name AS ColumnName, c.definition
  FROM sys.computed_columns c
  JOIN sys.tables t ON c.object_id = t.object_id
  WHERE c.is_persisted = 1;
  • Cross-reference the schema and table name from the error message (%.*ls.%.*ls) with the query output.

Step 2: Force a Recalculation of the Computed Column

  • Since you cannot simply “update” a persisted computed column directly, drop and re-add the column to force SQL Server to recompute all values using the current spatial library:
  ALTER TABLE dbo.YourTable DROP COLUMN YourComputedColumn;
  ALTER TABLE dbo.YourTable ADD YourComputedColumn AS (geometry_col.STArea()) PERSISTED;
  • Alternatively, if dropping the column is not feasible due to dependencies (indexes, constraints), disable persistence temporarily:
  ALTER TABLE dbo.YourTable ALTER COLUMN YourComputedColumn DROP PERSISTED;
  ALTER TABLE dbo.YourTable ALTER COLUMN YourComputedColumn ADD PERSISTED;

Step 3: Update Spatial Library Assemblies

  • Ensure Windows Server 2025 / Windows 11 has the latest Visual C++ Redistributable and SQL Server spatial assemblies installed, as mismatched DLL versions (SqlServerSpatial170.dll) are a common root cause.
  • Verify the installed version path at:
    C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\
  • Apply the latest Cumulative Update (CU) for SQL Server 2025 via SQL Server Setup or Microsoft Update Catalog.

Step 4: Rebuild Indexes on the Affected Heap

  • Since the table is a heap (no clustered index), create a temporary clustered index and drop it to force a physical data reorganization, ensuring the computed values are rewritten:
  CREATE CLUSTERED INDEX IX_Temp ON dbo.YourTable (YourPrimaryKeyColumn);
  DROP INDEX IX_Temp ON dbo.YourTable;
  • This step clears internal flags SQL Server uses to track “dirty” persisted values after a spatial library version change.

Alternative or Verification Steps

  • Verify the Fix: Re-run DBCC CHECKTABLE('YourTable') WITH DATA_PURITY and check the SQL Server Error Log for the absence of Error 3822 on the next scheduled consistency check or table scan.
  • Alternative Approach: If dropping the column is restricted by production uptime requirements, create a non-persisted computed column alongside the old one, validate the data output matches expectations, and then swap the schema during the next maintenance window.