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

by

Troubleshooting Guide: Error 12353 – Computed Columns in Memory-Optimized Index Must Be PERSISTED

Why This Error Happens

This error occurs because memory-optimized tables in SQL Server store computed column values differently than disk-based tables, requiring the computed value to be physically materialized rather than calculated on the fly. When you attempt to create an index on a computed column within a memory-optimized table, SQL Server’s engine rejects the operation unless the PERSISTED keyword is explicitly declared, as the indexing mechanism for memory-optimized OLTP tables cannot reference non-materialized (virtual) values. Essentially, the query optimizer needs a static, stored value to build the hash or range index against, which a standard (non-persisted) computed column does not provide.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Table and Column

  • Locate the specific memory-optimized table causing the error by checking the SQL Server Management Studio (SSMS) error log or the specific CREATE TABLE/ALTER TABLE script that triggered Error 12353.
  • Run the following query to find computed columns and check their persistence status: SELECT name, is_persisted FROM sys.computed_columns WHERE object_id = OBJECT_ID('YourSchema.YourTableName');
  • Identify which specific computed column(s) have is_persisted set to 0.

Step 2: Modify the Table Schema to Include PERSISTED

  • Since altering computed column definitions on existing memory-optimized tables is restrictive, you will likely need to drop and recreate the table or column definition.
  • Update your T-SQL script to include the PERSISTED keyword immediately after the computed column formula.
  • Example syntax correction: ALTER TABLE dbo.YourTable ADD TotalPrice AS (Quantity * UnitPrice) PERSISTED;
  • If the table already contains data, back up the data to a temporary disk-based staging table before dropping and recreating the memory-optimized table with the corrected schema.

Step 3: Recreate the Index

  • Once the computed column is successfully altered to PERSISTED, attempt to recreate the index that initially failed.
  • Use the specific memory-optimized index syntax (Hash or Nonclustered) relevant to your workload: CREATE INDEX IX_YourIndex ON dbo.YourTable (TotalPrice);
  • Monitor the “Messages” tab in SSMS to confirm the index creation completes without triggering Error 12353 again.

Step 4: Restart the Database Engine Service (If Schema Locks Persist)

  • If you encounter schema lock errors while trying to modify the memory-optimized table (common with Hekaton engine locks), restart the SQL Server service.
  • On Windows Server 2025/Windows 11, open Services.msc, locate SQL Server (MSSQLSERVER) or your specific instance name, and select Restart.
  • Alternatively, use PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

  • Verification: Re-run the sys.computed_columns query from Step 1 to confirm the is_persisted column now returns a value of 1 for the modified column, and check sys.indexes to ensure the new index status is is_disabled = 0.
  • Alternative: If you cannot modify the column to be PERSISTED due to non-deterministic functions (e.g., using GETDATE()), remove the computed column from the index definition entirely and handle the calculation logic at the application layer or via a natively compiled stored procedure instead.