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

by

Troubleshooting Guide: SQL Server Error 10674

Why This Error Happens

This error occurs because SQL Server 2025 does not allow a table to simultaneously support a traditional clustered (B-tree) index and a nonclustered columnstore index when an unsupported %S_MSG option (such as certain ONLINE, SORT_IN_TEMPDB, or DATA_COMPRESSION combinations) is specified in the CREATE INDEX statement. The storage engine treats the nonclustered columnstore index as a distinct physical structure, and certain clustered index creation options are incompatible with the columnstore’s segment-based storage format. This typically happens during index maintenance scripts or migrations where options valid for standard rowstore indexes are reused without modification for columnstore-enabled tables.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Index Option

  • Open SQL Server Management Studio (SSMS) and review the exact CREATE CLUSTERED INDEX statement that triggered Error 10674.
  • Check the error message for the specific %S_MSG placeholder value (e.g., ONLINE = ON, DROP_EXISTING, or SORT_IN_TEMPDB) that is unsupported.
  • Cross-reference the statement against Microsoft’s documentation for “Columnstore Index Limitations” in SQL Server 2025.

Step 2: Remove or Modify the Unsupported Option

  • Edit the T-SQL script to strip out the incompatible option from the WITH (...) clause.
  • Example fix:
CREATE CLUSTERED INDEX IX_Clustered
ON dbo.YourTable (ColumnName)
WITH (DROP_EXISTING = ON); -- Remove ONLINE or SORT_IN_TEMPDB if causing conflict
  • If the option is required for performance reasons, consider rebuilding the index in two separate steps instead of a single combined statement.

Step 3: Drop and Recreate the Nonclustered Columnstore Index Separately

  • Drop the existing nonclustered columnstore index using DROP INDEX [IndexName] ON [SchemaName].[TableName];.
  • Create the clustered index first using the corrected syntax from Step 2.
  • Recreate the nonclustered columnstore index afterward using a standalone CREATE NONCLUSTERED COLUMNSTORE INDEX statement without conflicting options.

Step 4: Validate Compatibility Level and Database Settings

  • Confirm the database compatibility level is set to 170 (SQL Server 2025) by running:
SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
  • Ensure ALTER DATABASE SCOPED CONFIGURATION settings do not force legacy cardinality estimation or indexing behaviors that might reintroduce option conflicts.

Alternative or Verification Steps

  • Run DBCC CHECKTABLE('YourTableName') after index creation to confirm there is no structural corruption and that both indexes coexist correctly.
  • If the error persists, use sp_helpindex 'YourTableName' to list all current indexes and verify no duplicate or overlapping columnstore/clustered definitions remain on the table.