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

by

Troubleshooting Guide: SQL Server Error 5383 – Cannot Reference Index on Expression Column

Why This Error Happens

Error 5383 occurs because SQL Server does not allow indexes to be created directly on computed columns that reference expressions in certain unsupported configurations, such as non-deterministic functions, columns lacking the PERSISTED attribute, or expressions that rely on CLR functions without the correct determinism attributes. This typically happens when a developer attempts to build an index on a computed column whose underlying expression cannot be guaranteed to return the same result for the same inputs. Without this guarantee, SQL Server’s query optimizer cannot reliably store or maintain the index, triggering a schema validation failure during the CREATE INDEX operation.

Step-by-Step Resolution Guide

Step 1: Verify the Computed Column Definition

  • Run sp_helpcolumns 'YourTableName' or query sys.computed_columns to inspect the exact expression used.
  • Check if the column uses non-deterministic functions (e.g., GETDATE(), NEWID(), or certain CLR methods) which inherently block indexing.
  • Identify if the PERSISTED keyword is missing from the computed column definition.

Step 2: Mark the Column as PERSISTED

  • Alter the table to add the PERSISTED keyword to the computed column definition:
  • ALTER TABLE YourTable ADD COMPUTED_COL AS (Expression) PERSISTED;
  • Note that PERSISTED only works if the expression is deterministic; non-deterministic expressions must be refactored before this step will succeed.

Step 3: Validate CLR Function Determinism (If Applicable)

  • If the expression calls a CLR user-defined function, open SQL Server Management Studio (SSMS) and check the function’s attributes.
  • Ensure the function is decorated with [SqlFunction(IsDeterministic = true, IsPrecise = true)] in the original C# source code.
  • Redeploy the assembly to the instance using ALTER ASSEMBLY if changes were made to the attribute.

Step 4: Recreate the Index

  • Drop any failed index attempts using DROP INDEX IF EXISTS IX_YourIndex ON YourTable;.
  • Re-run the CREATE INDEX IX_YourIndex ON YourTable (ComputedColumnName); command.
  • Confirm the SQL Server 2025 instance service is running under the correct service account in services.msc on Windows Server 2025, as permission mismatches can sometimes mask as schema errors during DDL operations.

Alternative or Verification Steps

  • To verify the fix, query sys.indexes joined with sys.columns to confirm the new index is listed as is_disabled = 0 and active against the computed column.
  • If the expression absolutely cannot be made deterministic (e.g., it relies on external system calls), create a standard non-computed column, populate it via a trigger or scheduled job, and index that physical column instead.