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

by

Troubleshooting Guide: SQL Server Error 2729 – Non-Deterministic Column Reference

Why This Error Happens

This error occurs when SQL Server attempts to create an index, statistics, or a partition key on a column whose computed value cannot be guaranteed to return the same result every time it is evaluated with the same inputs. This typically happens when a computed column references non-deterministic functions (such as GETDATE(), NEWID(), RAND(), or certain CLR functions) or when it depends on another non-deterministic computed column or user-defined function lacking the SCHEMABINDING and determinism attributes. SQL Server enforces this restriction because indexes and statistics require stable, repeatable values to maintain data integrity and accurate query optimization.

Step-by-Step Resolution Guide

Step 1: Identify the Non-Deterministic Column or Function

  • Run the following query to locate the offending computed column definition:
SELECT name, definition, is_deterministic 
FROM sys.computed_columns 
WHERE object_id = OBJECT_ID('YourTableName');
  • Check is_deterministic = 0 to confirm which column is causing the conflict.
  • Review the column’s formula for calls to non-deterministic functions like GETDATE(), NEWID(), RAND(), or CURRENT_TIMESTAMP.

Step 2: Rewrite the Computed Column Using Deterministic Logic

  • Replace non-deterministic built-in functions with deterministic alternatives where possible (e.g., use a fixed input parameter instead of GETDATE()).
  • If a User-Defined Function (UDF) is involved, ensure it is created WITH SCHEMABINDING and does not call any non-deterministic system functions internally.
  • Example of a schema-bound deterministic UDF:
CREATE FUNCTION dbo.CalculateTax(@Amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
WITH SCHEMABINDING
AS
BEGIN
    RETURN @Amount * 0.08;
END;

Step 3: Drop and Recreate the Computed Column

  • Remove the existing non-deterministic column definition:
ALTER TABLE YourTableName DROP COLUMN YourComputedColumn;
  • Add the column back using the corrected, deterministic expression or function:
ALTER TABLE YourTableName 
ADD YourComputedColumn AS dbo.CalculateTax(Price) PERSISTED;
  • Note: Using PERSISTED on a computed column requires the expression to be deterministic (or precise), so this step will fail immediately if determinism issues remain unresolved.

Step 4: Rebuild the Index or Statistics Object

  • Once the column is updated, attempt to recreate the index, statistics object, or partition function that originally failed:
CREATE INDEX IX_YourIndexName 
ON YourTableName(YourComputedColumn);
  • Verify the command completes without throwing Error 2729.

Alternative or Verification Steps

  • Verify the fix by querying sys.computed_columns again to confirm is_deterministic returns 1 for the modified column.
  • If rewriting the function is not feasible, use a PERSISTED computed column without an index, or create a separate non-computed column that is updated via a trigger to “snapshot” the value instead.