July 23, 2008 at 12:45 pm
I am at a loss for the design of the database I have inherited. One of the first things I've noticed is that there are barely any clustered indexes on any of the tables, and many of the nonclustered appear pretty useless compared to the WHERE clauses I am looking at for the queries that access them. So I started putting together a list of clustered indexes and non-clustered that would help make the app faster, since at times it is slow, and I was frustrated to find that when I wanted to eliminate many of the existing indexes, I was getting an error like this:
"An explicit DROP INDEX is not allowed on index . It is being used for PRIMARY KEY constraint enforcement."
Most of the time I just want to drop the nonclustered index (as in the case above), and replace it with a Clustered Index with a few additional columns.
So not being able to talk this over with the previous developer, I am wondering what he had intended by applying a Primary Key constraint to a table without applying a clustered, or additional nonclustered. It seems pretty dumb to me.
So I guess what I want to know, is if I leave the previous nonclustered index with the constraint, but add a Clustered Index, or additional non-clustered indexes, is this going to be an issue? Most of the time the primary key is no where being used in the queries (its just an ID column), and I can't defrag nonclustered indexes.
July 23, 2008 at 12:52 pm
Here's an example of what I am looking at:
XPKDEACTIVATE_SOFTWARE_USER_DETAIL
nonclustered, unique, primary key located on PRIMARY
DEACTIVATE_DETAIL_ID
and what I want to add is
C_DEACTIVATE_SOFTWARE_USER_DETAIL
clustered located on PRIMARY
DEACTIVATE_REFERENCE_ID, SOFTWARE_ID, SOFTWARE_UI
Should I remove the constraint, and the previous index, and create the second one, or just create the second one in addition to the first?
July 23, 2008 at 1:46 pm
The primary key does not have to be the clustered index, it is not unusual to have a clustered index that is not the PK. Don't create duplicate indexes. If you have a primary key on the field you want to create the clustered index on, remove the PK and re-create it clustered.
Duplicate indexes will give you no benefit (there is a way to get benefit out of them, but it will not help you in thie endeavor). They add overhead by taking up space and requiring update when you insert, update or delete in the table. They also give the optimizer one more possibly bad choice.
July 24, 2008 at 8:18 am
To drop those indexes, you first need to drop the PK constraint. In Management Studio, you will find those under Keys, under the table. Of course, you'll probably want to create those again once the index is dropped/rebuilt.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply