Sql-server – Duplicate non-clustered index on Primary Key, can I drop one of them

index-tuningsql server

Looking over on of our older databases I noticed that several large tables had duplicate indexes on the Primary Key.

Both indexes are Non-clustered, Unique and only includes the PK-column. The clustered index is on a different column

Below is the SQL to create the indexes

ALTER TABLE [dbo].[TableName] ADD  CONSTRAINT [PK_TableName_1] PRIMARY KEY NONCLUSTERED 
([id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY] 

and

CREATE UNIQUE NONCLUSTERED INDEX [PK_TableName] ON [dbo].[TableName] 
( [id] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FGIndex]

Both these statements will create a index that to my eyes looks like duplicates.
The PK-index will be indicated with the "yellow key" in the indexlist in SSMS.

Is it safe to drop the the index? According to statistics the 2nd index is often the one that the Query Plan Engine chooses. The second index also uses less space.

Thanks for any input.

Best Answer

I think it's safe to remove the non-primary key as it's an exact duplicate of the primary key.

If one of them is used more and takes less space, did you check the fragmentation and last statistics date on both? Maybe one of them is more fragmented or has better stats.