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

by

Troubleshooting Guide: SQL Server Error 8112

Why This Error Happens

Error 8112 occurs when a CREATE TABLE or ALTER TABLE statement attempts to define a second PRIMARY KEY or UNIQUE constraint with a CLUSTERED option on a table that already has an existing clustered index. SQL Server enforces a strict architectural rule that a table (a heap or clustered structure) can only have one clustered index at any given time, since the clustered index physically dictates the sort order of the data rows on disk. This typically happens when a developer forgets that a Primary Key constraint defaults to CLUSTERED and tries to add another one without first specifying NONCLUSTERED.

Step-by-Step Resolution Guide

Step 1: Identify the Existing Clustered Index

  • Before altering the table, check which column currently holds the clustered index.
  • Run the following query in SQL Server Management Studio (SSMS) or Azure Data Studio:
SELECT i.name AS IndexName, i.type_desc, c.name AS ColumnName
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('YourTableName') AND i.type_desc = 'CLUSTERED';
  • Note the index name (e.g., PK_YourTable_ID) returned by this query.

Step 2: Modify the New Constraint to Non-Clustered

  • Locate the script that is attempting to create the second constraint and causing the conflict.
  • Explicitly add the NONCLUSTERED keyword to the new constraint definition to override the default behavior.
  • Example of corrected syntax:
ALTER TABLE YourTableName
ADD CONSTRAINT UQ_YourTable_Email UNIQUE NONCLUSTERED (EmailAddress);

Step 3: Drop the Conflicting Index (If Necessary)

  • If the new clustered index is actually the one you want to keep (and the old one is obsolete), you must remove the old clustered index first.
  • Use the following command, replacing the index name identified in Step 1:
ALTER TABLE YourTableName DROP CONSTRAINT PK_YourTable_ID;
  • Proceed to re-run your original script to add the new clustered constraint now that the slot is free.

Step 4: Re-execute the Transact-SQL Script

  • Save the modified script.
  • Execute the query again in a new query window within SSMS connected to your Windows Server 2025 instance.
  • Confirm that the execution completes with the message “Commands completed successfully.”

Alternative or Verification Steps

  • Verification: Re-run the query from Step 1 to confirm that only one row returns with type_desc equal to CLUSTERED, and that the second constraint now appears as NONCLUSTERED.
  • Alternative: If you are unsure which index is more critical for performance, use the Database Engine Tuning Advisor to analyze the table schema before deciding which key to drop or convert to non-clustered.