Dropping a unique clustered index on identity colum
3
votes
3
answers
516
views
I'm currently working with a SQL Server database that came from an inherited system. The previous system used a lot of auto incrementing bigint identity fields, let's call the field "KeyID". These KeyID columns are interspersed throughout the database but in our implementation of the app, KeyID is being ignored. There are values for the fields in the tables, but KeyID is not used in any way.
I've noticed that for each table that has a KeyID field, a unique clustered index has been been created for it, IX_Tablename.
I am considering dropping these indices to optimize the number of indices the server needs to maintain, but am not sure what the long term effects will be. The resources I find online seem to assume, I believe, that the identity column is the primary key, and say that deleting the clustered index for that column will result in the table becoming a heap. But KeyID is not a primary key, the PK's are separate Guid columns with their own unique non-clustered indices. The tables with the KeyID index are clustered on a value that is not referenced and so no seeks are performed on their values.
Is their any advantage to keeping them or should I remove these indices?
I am not ready to delete the columns themselves, so they will still need to be there and auto-increment, will their missing indices affect the creation of the next auto-incremented value?
Asked by LeArgus
(31 rep)
May 31, 2024, 06:12 PM
Last activity: Jun 1, 2024, 10:51 PM
Last activity: Jun 1, 2024, 10:51 PM