[dba-SQLServer] Can't drop the PK from a table

If you really want to drop an index associated with a constraint (a PK or
Unique Constraint) you have to drop the *constraint* - you can't just drop
the index, but dropping the constraint will automatically drop the
associated index as well.
SQL Server has made this restriction as a precaution against dropping a
unique index not knowing that the index is there for a constraint-reason:
that's why you have to explicitly drop the constraint, telling SQL Server
that you are aware of what you are doing...

But any way, I don't think you have to drop the index. The REBUILD option is
made just for your case: a heavy fragmented index and an index bound to a
constraint. Using the example from your previous posting I would recommend
this statement:

ALTER INDEX [PK_BESTSecurityLog] ON [dbo].[BESTSecurityLog] REBUILD

Asger


-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Arthur
Fuller
Sendt: 16. juni 2008 19:07
Til: Discussion concerning MS SQL Server
Emne: [dba-SQLServer] Can't drop the PK from a table

I have a table into which a process inserts 1300 rows every 10 minutes.
Currently there are 34 million rows in the table. There are 7 indexes on the
table, two of which are seriously fragmented (98% and 87%). I ran dbcc
reindex on the table and it changed the fragmentation not at all. Why would
that be the case? Another thing that puzzles me is that one of the
fragmented indexes is the PK clustered index and the PK as is int identity.
Also, I find that I cannot drop the PK index, which I thought I'd do as a
way of rebuilding it from scratch. When I try to drop the index and save the
table, the system times out and won't let me do it. Any idea why this might
be so?

Arthur
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com