drop index will recreate an index.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tdr - 14 Jul 2008 13:26 GMT

I need to temporary drop indexes then truncate and reload data into a table.
when I run a drop index command on a table, it will drop the indexes and the
recreate an index.

I'm not sure want index it creates, there are no indexes listed on the table
when I look at the prosperities.
but I will see 'creating index' when I run sp_who while the drop command is
running.

I'm trying to speed up the process of reloading the table by not having
indexes.

I have 8 indexes (one is clustered)
what index would the sql be adding back and why?
Should I disable the index
Or should I truncate the table before dropping the indexes?

TIA

Reply to this Message

Uri Dimant - 14 Jul 2008 13:33 GMT

tdr
Very strange, can you show us your code?

>I need to temporary drop indexes then truncate and reload data into a
>table.
[quoted text clipped - 18 lines]
>
> TIA

Reply to this Message

Dan Guzman - 14 Jul 2008 13:42 GMT

> I have 8 indexes (one is clustered)
> what index would the sql be adding back and why?

If you drop the clustered index before non-clustered indexes, SQL Server
will need to rebuild all of the remaining non-clustered indexes. This is
because non-clustered indexes use the clustered index key as the row locator
when the table has a clustered index. Non-clustered indexes use the
internal row id as the row locator when no clustered index ix present.

> Should I disable the index
> Or should I truncate the table before dropping the indexes?

As long as you drop/disable the non-clustered indexes first, I don't think
it matters much whether your truncate or drop first.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

>I need to temporary drop indexes then truncate and reload data into a
>table.
[quoted text clipped - 18 lines]
>
> TIA

Reply to this Message

tdr - 14 Jul 2008 14:31 GMT

so the drop command below

    drop index LClaims.IX_LuminxClaims
    drop index LClaims.tot_stats_tickler

should be ( drop non clustered first then clustered)

    drop index LClaims.tot_stats_tickler
    drop index LClaims.IX_LuminxClaims

Thanks, I try it.

> I need to temporary drop indexes then truncate and reload data into a table.
> when I run a drop index command on a table, it will drop the indexes and the
[quoted text clipped - 14 lines]
>
> TIA

Reply to this Message

tdr - 14 Jul 2008 14:38 GMT

does this rule apply to building index.
i.e. build cluster first and non-clustered last?

thanks again.

> I need to temporary drop indexes then truncate and reload data into a table.
> when I run a drop index command on a table, it will drop the indexes and the
[quoted text clipped - 14 lines]
>
> TIA

Reply to this Message

Roy Harvey (SQL Server MVP) - 14 Jul 2008 15:10 GMT

>does this rule apply to building index.
>i.e. build cluster first and non-clustered last?

Yes, absolutely.

Roy Harvey
Beacon Falls, CT

Reply to this Message

Tom Cooper - 14 Jul 2008 16:52 GMT

If you are not changing the schema of the tables, just deleting the old data
and reloading it with new data, you could experment whether or not it is
faster/better to keep the clustered index or drop it. So two possible
procedures are

Drop nonclustered indexes
Truncate Table
Drop clustered index
Reload table
Create clustered index
Create nonclustered indexes

or

Drop nonclustered indexes
Truncate Table
Reload table
Create nonclustered indexes

If you do the second method, make sure the rows are loaded in clustered
index order. If they are loaded out of order, you will get page splits and
that will slow things down.

Tom

>>does this rule apply to building index.
>>i.e. build cluster first and non-clustered last?
[quoted text clipped - 3 lines]
> Roy Harvey
> Beacon Falls, CT

Reply to this Message

Jeffrey Williams - 15 Jul 2008 03:31 GMT

> If you are not changing the schema of the tables, just deleting the old data
> and reloading it with new data, you could experment whether or not it is
[quoted text clipped - 27 lines]
>> Roy Harvey
>> Beacon Falls, CT

The OP did not state what version - but, if this is on 2005 I would
recommend disabling the non-clustered indexes instead of dropping them,
leave the clustered index, and after the data is loaded rebuild all
indexes (ALTER INDEX ALL ON table REBUILD;)

This will rebuild all indexes including the clustered index which will
take care of any page splits, if the table is not loaded in clustered
index order.

Jeff

Reply to this Message