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

by

Troubleshooting Guide: SQL Server Error 1902 — Cannot Create More Than One Clustered Index

Why This Error Happens

This error occurs because a table in SQL Server can physically contain only one clustered index at a time, as the clustered index determines the actual storage order of the data rows on disk. The error is triggered when a CREATE INDEX or CREATE TABLE statement attempts to define a second clustered index on a table (or constraint, such as a PRIMARY KEY, that implicitly creates one) without first removing the existing one. This is frequently caused by migration scripts, ORM frameworks (like Entity Framework), or deployment pipelines that do not check for existing schema objects before attempting to apply new index definitions.

Step-by-Step Resolution Guide

Step 1: Identify the Existing Clustered Index

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to your Windows Server 2025 instance.
  • Run the following query to locate the conflicting index name referenced in the error message:
SELECT i.name AS IndexName, t.name AS TableName 
FROM sys.indexes i 
INNER JOIN sys.tables t ON i.object_id = t.object_id 
WHERE i.type = 1 AND t.name = 'YourTableName';
  • Note the IndexName returned, as you will need this exact name for the next step.

Step 2: Drop the Pre-existing Clustered Index

  • Execute a DROP INDEX statement using the name identified in Step 1.
  • Use the following T-SQL syntax, replacing the placeholders with your specific table and index names:
DROP INDEX [ExistingIndexName] ON [dbo].[YourTableName];
  • If the clustered index is tied to a Primary Key constraint, you must drop the constraint instead using ALTER TABLE [YourTableName] DROP CONSTRAINT [PK_ConstraintName];.

Step 3: Re-run the New Index Creation Script

  • Once the old index is removed, execute your original CREATE CLUSTERED INDEX or deployment script again.
  • Ensure the script logic for future deployments includes a check: IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IndexName') DROP INDEX... to prevent recurrence.

Step 4: Restart the SQL Server Service (If Cache Issues Persist)

  • If the error persists due to a stale query plan cache on Windows Server 2025, open the Services app (services.msc).
  • Locate SQL Server (MSSQLSERVER), right-click, and select Restart to clear the execution plan cache.

Alternative or Verification Steps

  • Verify the Fix: Run SELECT name, type_desc FROM sys.indexes WHERE object_id = OBJECT_ID('YourTableName'); to confirm only one row shows type_desc as CLUSTERED.
  • Alternative: If you need the data organized by the new key but cannot drop the old index due to dependencies, consider creating the new index as a Non-Clustered index instead to avoid altering the physical table structure.