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

by

Troubleshooting Guide: SQL Server Error 1764

Why This Error Happens

This error occurs when a computed column is referenced in a context that requires it to be deterministic and physically stored on disk—such as being used as a primary key, in a unique index, or as a partitioning key—but the column was created without the PERSISTED keyword. SQL Server 2025’s query optimizer and index engine cannot guarantee referential or structural integrity on a value that is recalculated on-the-fly rather than materialized in the table’s physical storage. This typically surfaces during index creation, foreign key constraint definition, or when upgrading/migrating schemas from older compatibility levels.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column Definition

  • Open SQL Server Management Studio (SSMS) and run sp_help 'YourTableName' to inspect column metadata.
  • Query sys.computed_columns to confirm the is_persisted flag:
SELECT name, definition, is_persisted 
FROM sys.computed_columns 
WHERE object_id = OBJECT_ID('YourTableName');
  • Note the exact column name and expression reported in the Error 1764 message.

Step 2: Alter the Column to Add the PERSISTED Attribute

  • Drop any dependent indexes or constraints temporarily if they block the ALTER TABLE operation.
  • Modify the column definition to include PERSISTED:
ALTER TABLE YourTableName 
DROP COLUMN YourComputedColumn;

ALTER TABLE YourTableName 
ADD YourComputedColumn AS (YourExpression) PERSISTED;
  • Ensure the underlying expression is deterministic (avoid non-deterministic functions like GETDATE() or NEWID() within the formula).

Step 3: Recreate Dependent Objects

  • Re-create any indexes, primary keys, or foreign key constraints that were dropped in Step 2.
  • Re-run the original CREATE INDEX or ALTER TABLE ADD CONSTRAINT statement that originally triggered Error 1764.

Step 4: Validate Compatibility Level (Windows Server 2025 Specific)

  • Check the database compatibility level, as SQL Server 2025 defaults to a higher level that enforces stricter determinism checks:
SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
  • If necessary, align the compatibility level using ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

  • Verify the fix by re-running the failed query or index creation script; the error should no longer appear in the SSMS “Messages” pane.
  • If PERSISTED cannot be applied due to a non-deterministic function in the formula, rewrite the computed column expression using deterministic system functions (e.g., replace GETDATE() with a value populated via a trigger or default constraint instead).