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

by

Why This Error Happens

Error 4146 occurs when the CREATE STATISTICS or UPDATE STATISTICS command is executed against a column that does not support statistical indexing—typically columns with data types like XML, geography, geometry, image, text, ntext, or non-deterministic computed columns. SQL Server 2025’s query optimizer requires deterministic, comparable data to build a valid statistics object, and it rejects the request at the parser level if the target column fails this validation. This is not a permissions or connectivity issue; it is a schema-level restriction enforced by the Database Engine itself.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column

  • Open SQL Server Management Studio (SSMS) 21 and run the failing script in a new query window against the affected database.
  • Note the exact column name referenced in the error message—SQL Server will explicitly name it in the 4146 output.
  • Run sp_help 'TableName' or query sys.columns to check the data type of that column.

Step 2: Verify Data Type Compatibility

  • Cross-reference the column’s data type against unsupported types: XML, geography, geometry, image, text, ntext, sql_variant (partial support), and rowversion/timestamp columns.
  • If the column is a computed column, run the following to check determinism:
  SELECT name, is_computed, definition 
  FROM sys.computed_columns 
  WHERE object_id = OBJECT_ID('YourTableName');
  • Confirm whether the computed column expression uses non-deterministic functions (e.g., GETDATE(), NEWID()).

Step 3: Modify the Statistics Target

  • Remove the unsupported column from the CREATE STATISTICS column list and target only deterministic, comparable columns instead.
  • Example corrected syntax:
  CREATE STATISTICS stat_SalesDate 
  ON dbo.Orders (OrderDate, CustomerID);
  • If statistics are needed on an XML or spatial column, use a computed persisted column with a deterministic extraction (e.g., XML.value() cast to VARCHAR) and build statistics on that computed column instead.

Step 4: Rebuild and Validate the Statistics Object

  • Drop any partially created or broken statistics object first:
  DROP STATISTICS dbo.Orders.stat_SalesDate;
  • Re-run the corrected CREATE STATISTICS statement from Step 3.
  • Check sys.stats and sys.stats_columns to confirm the object was created successfully:
  SELECT s.name, c.column_id, col.name AS column_name
  FROM sys.stats s
  JOIN sys.stats_columns c ON s.stats_id = c.stats_id AND s.object_id = c.object_id
  JOIN sys.columns col ON c.object_id = col.object_id AND c.column_id = col.column_id
  WHERE s.object_id = OBJECT_ID('dbo.Orders');

Alternative or Verification Steps

  • Run DBCC SHOW_STATISTICS ('dbo.Orders', 'stat_SalesDate') to confirm the statistics object populates correctly with histogram and density data.
  • If the target data genuinely requires indexing on an XML or spatial column, create a Primary XML Index or Spatial Index instead, since these indexing structures natively support those data types without relying on standard column statistics.