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

tdr
Very strange, can you show us your code?



"tdr" <tdr@discussions.microsoft.com> wrote in message
news:3B43A8A2-1EA7-483B-B8E1-D9AE45B0E99F@microsoft.com...
>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

> 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.

--
Hope this s.

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

"tdr" <tdr@discussions.microsoft.com> wrote in message
news:3B43A8A2-1EA7-483B-B8E1-D9AE45B0E99F@microsoft.com...
>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

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.

"tdr" wrote:

> 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

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

thanks again.

"tdr" wrote:

> 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

On Mon, 14 Jul 2008 06:38:03 -0700, tdr
<tdr@discussions.microsoft.com> wrote:

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

Yes, absolutely.

Roy Harvey
Beacon Falls, CT

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

"Roy Harvey (SQL Server MVP)" <roy_harvey@snet.net> wrote in message
news:pinm7419l2al3p6imufpu13dn8t1gjdp5k@4ax.com...
> On Mon, 14 Jul 2008 06:38:03 -0700, tdr
> <tdr@discussions.microsoft.com> wrote:
>
>>does this rule apply to building index.
>>i.e. build cluster first and non-clustered last?
>
> Yes, absolutely.
>
> Roy Harvey
> Beacon Falls, CT

Tom Cooper wrote:
> 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
>
> "Roy Harvey (SQL Server MVP)" <roy_harvey@snet.net> wrote in message
> news:pinm7419l2al3p6imufpu13dn8t1gjdp5k@4ax.com...
>> On Mon, 14 Jul 2008 06:38:03 -0700, tdr
>> <tdr@discussions.microsoft.com> wrote:
>>
>>> does this rule apply to building index.
>>> i.e. build cluster first and non-clustered last?
>> Yes, absolutely.
>>
>> 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