Which script is affected?

sp_IndexCleanup

Script Version

20260401 2.4

SQL Server Version

Microsoft SQL Server 2025 (RTM-CU2-GDR) (KB5077466) - 17.0.4020.2 (X64) Feb 13 2026 14:47:04 Copyright (C) 2025 Microsoft Corporation Enterprise Developer Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 26200: ) (Hypervisor)

Describe the Bug

Will not merge unique constraint with a non clustered index when keys are duplicated.

If I replace the unique constraint with a unique index it will merge.

Steps to Reproduce

USE [Your_Database]
GO
CREATE TABLE [dbo].[tbl_owntesttable](
[OrgUnit_ID] [int] NOT NULL,
[Person_ID] [int] NOT NULL,
[Access_ID] [int] NOT NULL,
[ReadOnly] [bit] NOT NULL,
[Role_ID] [int] NOT NULL,
[Domain_ID] [int] NULL,
[ID] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_tbl_owntesttable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_owntesttable] ADD CONSTRAINT [DF_tbl_owntesttable_ReadOnly] DEFAULT ((0)) FOR [ReadOnly]
GO
/*
Will not produce merge scripts
*/
USE [Your_Database]
GO

ALTER TABLE [Your_Database].[dbo].[tbl_owntesttable]
ADD CONSTRAINT [UI_tbl_owntesttable]
UNIQUE ( Orgunit_id, Person_ID, Access_ID )

/*
Will produce merge scripts
*/
ALTER TABLE [Your_Database].[dbo].[tbl_owntesttable]
DROP CONSTRAINT [UI_tbl_owntesttable]

CREATE UNIQUE NONCLUSTERED INDEX [UI_tbl_owntesttable_LK] ON [dbo].[tbl_owntesttable]
(
[OrgUnit_ID] ASC,
[Person_ID] ASC,
Access_ID ASC
)
GO

/*
Duplicate redundant Index
*/
USE [Your_Database]
GO

CREATE NONCLUSTERED INDEX [IX_tbl_owntesttable_OrgunitID_PersID] ON [dbo].[tbl_owntesttable]
(
[OrgUnit_ID] ASC,
[Person_ID] ASC
)
GO

INSERT INTO tbl_owntesttable(Orgunit_id, Person_ID, Access_ID, ReadOnly, Role_ID, Domain_ID)
VALUES
(10004, 103, 3, 1, -1902154, 3),
(10004, 104, 3, 1, -1898455, 4),
(10004, 105, 3, 1, -1898455, 5),
(10004, 106, 3, 1, -1898455, 6),
(10004, 107, 4, 1, -1897334, 7),
(10004, 108, 3, 1, -1897331, 8),
(10004, 109, 3, 1, -1897329, 9),
(10004, 110, 3, 0, -1272626, 0),
(10004, 111, 3, 0, -1272626, 1),
(10004, 112, 3, 0, -1272626, 44)

EXEC sp_indexcleanup @database_name = 'Your_Database'

Expected Behavior

Should suggest replacing unique constraint with a unique index and drop the duplicated index.

Actual Behavior

No merge replacemant of unique constraint, only compressions.

Error Messages

Additional Context

CL 170