Thursday, March 29, 2012

Dropping Indexes

I'm doing some bulk inserting into various tables, and I'm dropping all the
indexes first, and rebuilding them after the data load. One of the tables
has several nonclustered indexes + a clustered index. My question is
simple: does the order I drop the indexes make a difference in the speed
with which they are dropped? My thought is that the clustered index should
be dropped last. Any suggestions?
ThanksFrom the Books Online topic "Dropping Indexes"
Dropping a clustered index can take time because in addition to dropping the
clustered index, all nonclustered indexes on the table must be rebuilt to
replace the clustered index keys with row pointers to the heap. When you
drop all indexes on a table, drop the nonclustered indexes first and the
clustered index last. That way, no indexes have to be rebuilt.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Mike C#" <xyz@.xyz.com> wrote in message
news:OvpqShbhGHA.1276@.TK2MSFTNGP03.phx.gbl...
> I'm doing some bulk inserting into various tables, and I'm dropping all
> the indexes first, and rebuilding them after the data load. One of the
> tables has several nonclustered indexes + a clustered index. My question
> is simple: does the order I drop the indexes make a difference in the
> speed with which they are dropped? My thought is that the clustered index
> should be dropped last. Any suggestions?
> Thanks
>|||That's what I figured, just wanted to make sure. Thanks Gail.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:Odo3RpbhGHA.4864@.TK2MSFTNGP03.phx.gbl...
> From the Books Online topic "Dropping Indexes"
> Dropping a clustered index can take time because in addition to dropping
> the clustered index, all nonclustered indexes on the table must be rebuilt
> to replace the clustered index keys with row pointers to the heap. When
> you drop all indexes on a table, drop the nonclustered indexes first and
> the clustered index last. That way, no indexes have to be rebuilt.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:OvpqShbhGHA.1276@.TK2MSFTNGP03.phx.gbl...
>sql

No comments:

Post a Comment