Troubleshooting Guide: SQL Server Error 10672 — Unsupported Option for Clustered Index on Filtered Index

Why This Error Happens

This error occurs because SQL Server does not allow certain index-creation options (such as IGNORE_DUP_KEY, ONLINE, or other incompatible flags represented by %S_MSG) to be applied when building a clustered index on a table that already contains a filtered index. Filtered indexes rely on a specific predicate and metadata structure that conflicts with the rebuild/sort behavior triggered by the unsupported option. This typically happens when a DBA or an automated deployment script (e.g., a schema migration tool) attempts to recreate or convert an index without first checking for existing filtered indexes on the target table.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Filtered Index

• Open SQL Server Management Studio (SSMS) and connect to the instance running SQL Server 2025.
• Run the following query to locate filtered indexes on the target table:

  SELECT name, filter_definition 
  FROM sys.indexes 
  WHERE object_id = OBJECT_ID('YourSchema.YourTable') 
  AND filter_definition IS NOT NULL;

• Note the index name(s) returned, as these must be addressed before proceeding.

Step 2: Drop or Temporarily Disable the Filtered Index

• Script out the filtered index definition first so it can be recreated later:

  SELECT OBJECT_DEFINITION(object_id) FROM sys.indexes WHERE name = 'YourFilteredIndexName';

• Drop the filtered index using:

  DROP INDEX YourFilteredIndexName ON YourSchema.YourTable;

• Avoid using DISABLE on the index, as disabled filtered indexes can still trigger Error 10672 in some SQL Server 2025 builds.

Step 3: Recreate the Clustered Index Without the Unsupported Option

• Remove the unsupported flag (e.g., IGNORE_DUP_KEY = ON or ONLINE = ON, depending on which triggered %S_MSG) from your CREATE CLUSTERED INDEX statement.
• Example corrected syntax:

  CREATE CLUSTERED INDEX IX_YourTable_Clustered
  ON YourSchema.YourTable (YourColumn);

• Execute the statement and confirm it completes without Error 10672.

Step 4: Recreate the Filtered Index

• Once the clustered index is successfully built, recreate the filtered index using the definition captured in Step 2:

  CREATE NONCLUSTERED INDEX YourFilteredIndexName
  ON YourSchema.YourTable (YourColumn)
  WHERE YourFilterColumn = 'SomeValue';

• Confirm there are no dependent constraints or statistics objects that reference the dropped index before finalizing.

Step 5: Update Deployment Scripts

• If this table is managed via DACPAC, SSDT, or a CI/CD pipeline, update the deployment script to drop filtered indexes before clustered index operations and recreate them afterward.
• Add a pre-deployment check using sys.indexes to prevent future recurrences.

Alternative or Verification Steps

• Verify the fix by re-running sp_helpindex 'YourSchema.YourTable' to confirm both the clustered index and filtered index exist without errors.
• If dropping the filtered index is not feasible (e.g., due to application dependencies), consider creating the clustered index on a staging table first, then switching the filtered index logic to a indexed view as an alternative design.