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

We have several large databases here these days, and the technique I follows
is the ALTER INDEX with the REBUILD option.  Although this works most of the
time, I do have a few tables that require the re-building of indexes via
dropping and re-creating them.

On Mon, Jun 16, 2008 at 12:31 PM, Asger Blond <ab-mi at post3.tele.dk> wrote:

> 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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...