2

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.

  • 2
    The only issue I can think of are index hints in queries, if you're using those you should check that this index isn't used anywhere – James Z Feb 13 '15 at 8:34
  • 1
    No index hints are used. – Kenny Eliasson Feb 13 '15 at 8:47
  • 1
    @KennyEliasson, verify foreign keys that reference the table are bound to the primary key index rather than the unique non-clustered index. See dbdelta.com/secrets-of-foreign-key-index-binding for a script. – Dan Guzman Feb 14 '15 at 14:15
  • 1
    @DanGuzman This particular database doesnt have any foreign keys ( yes it's true :( ) – Kenny Eliasson Feb 16 '15 at 8:16
  • 1
    What I have found out though is that the index and PK is in different filegroups. Both these filegroups is on the same disk though. Is this anything I should consider? – Kenny Eliasson Feb 16 '15 at 8:16
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.