September 27, 2011 at 6:30 am
Hi all
When looking at Indexes that are being maintained but not updated, I found one that applies to a seeded column ID of which that column is not the primary key of that table.
I am wondering perhaps what I should do. If the index has never been used then does that mean that column has never been used by the application, in which case should I be looking to drop both the index and the column? The table does have a primary composite key made up of two other columns.
Thanks for any tips
September 27, 2011 at 6:55 am
What do you mean maintained but not updated? How do you know the index hasn't been used?
John
September 27, 2011 at 7:04 am
Sorry I mean maintained but never used, have never had any read against them performed. The information as obtained from index_usage_stats.
I have since been looking at the dependencies of the table to find those views and procs that reference the table, and the ID column is used at various places.
Is there anything to look out for when using the dependencies as I have read that maybe not all of the dependencies could be there, how can I check in a full-proof manner that I am aware of the dependencies?
September 27, 2011 at 7:12 am
If the column is not being used by the application and there is no intended future use of that column by the application then yes, by all means drop the index and column.
Too many times I see tables created with an ID column (and worse, made to be the PK of the table) but which is never used by the application. I think its something that is being taught today: "every table needs an PK ID column.." despite the fact that it is not needed or that there is a natural key for the design. You can't design an efficient database unless you understand the system design and process flow.
Having said that, there may be cases where you need to have a clustered index on an increasing value when nothing else is available. However I have never had a need to do that in any database design.
The probability of survival is inversely proportional to the angle of arrival.
September 27, 2011 at 7:50 am
Hi and thanks for the reply. It seems the ID column is being used to count the number of rows in various procedures. As such I am not sure its worth dropping the column as its in use, although I guess I could alter the count to use a different column.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply