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

by

Troubleshooting Guide: Error 10668 — “%S_MSG option is not supported for creating an index on a heap”

Why This Error Happens

This error occurs when a CREATE INDEX statement includes an option (such as ONLINE, SORT_IN_TEMPDB combined with certain flags, or a clustered-only property) that SQL Server 2025 does not support for tables without a clustered index (heaps). Heaps lack the underlying B-tree structure required to apply certain index-build options, so the Database Engine rejects the operation. This is a syntax/compatibility issue tied to table schema design rather than a permissions or connectivity problem.

Step-by-Step Resolution Guide

Step 1: Identify the Target Table Structure

  • Run the following query to confirm whether the table is a heap:
  SELECT OBJECT_NAME(object_id) AS TableName, index_id, type_desc
  FROM sys.indexes
  WHERE object_id = OBJECT_ID('YourSchema.YourTable');
  • A result showing index_id = 0 and type_desc = HEAP confirms the table has no clustered index.
  • Note the unsupported option mentioned in the original error message (e.g., ONLINE = ON, RESUMABLE = ON, etc.).

Step 2: Modify the CREATE INDEX Statement

  • Remove the unsupported option from your script. For example, change:
  CREATE NONCLUSTERED INDEX IX_Test ON dbo.YourTable (ColumnName)
  WITH (ONLINE = ON);

to:

  CREATE NONCLUSTERED INDEX IX_Test ON dbo.YourTable (ColumnName);
  • If the option is required for your workload (e.g., minimal downtime), consider adding a clustered index first instead.

Step 3: Convert the Heap to a Clustered Table (If Required)

  • If the business requirement needs advanced indexing options like ONLINE rebuilds, create a clustered index on a suitable key column:
  CREATE CLUSTERED INDEX IX_Clustered ON dbo.YourTable (PrimaryKeyColumn);
  • This converts the heap into a clustered table, after which most indexing options (including ONLINE) become supported.

Step 4: Re-run the Index Creation Script

  • Execute the corrected CREATE INDEX statement in SQL Server Management Studio (SSMS) or via sqlcmd.
  • Confirm no further Error 10668 messages appear in the Messages tab or command output.

Alternative or Verification Steps

  • Verify success by re-querying sys.indexes to confirm the new index now appears with the expected type_desc (e.g., NONCLUSTERED or CLUSTERED).
  • If converting to a clustered table isn’t feasible, simply omit the unsupported WITH option — the index will still be created successfully on the heap, just without that specific build behavior.