I have given a task to find out the unused indexes in my production environment. I'm using a DMV to find the unused indexes should i include the Primary key Clustered Indexes on it? If a Primary key Index isn't being used, shouldn't that be of some concern? Unless, perhaps, it is a small table that isn't accessed very often?
Sql-server – Unused Indexes – Consider Primary Key Constraint Supporting Indexes
indexsql serversql-server-2008-r2
Related Question
- Sql-server – Should I mark a composite index as unique if it contains the primary key
- Sql-server – Change clustered index without dropping the primary key
- Sql-server – Dropping unused indexes – Assessing the unexpected dangers
- Sql-server – Clustered Sequential GUID Primary Key vs Non-Clustered GUID and Clustered Sequential ID Primary Keys
- Sql-server – Non-clustered indexes for columns defined within a composite Primary Key
- Why is DTA recommending non-clustered indexes that match the clustered Primary Key index
- Sql-server – ny difference with specifying the primary key as an include column in a nonclustered index
- Sql-server – Identifying Unused Indexes on SQL Azure
Best Answer
Primary Key and Unique constraints are enforced by a supporting index for performance and to enforce uniqueness. Just because the index is not used for searching does not mean it isn't being used to guarantee uniqueness of the key values.
Whether or not a table has a primary key constraint or any unique constraints is not a performance decision - it is a data integrity decision. The primary key gets a primary key constraint and the candidate keys get unique constraints.
Whether or not the primary key index is clustered or nonclustered, on the other hand, is a performance question. Doing a Binoogle search on that will find many hits, including https://stackoverflow.com/questions/2206541/index-view-how-to-choose-the-clustered-index.