Regarding Index Rebuilding

[pankaj_wolfhunter@yahoo.co.in]

Greetings,
While loading data into Sql Server using BCP utility,
we have to explicitly need to drop the indexes. then after loading the
data we recreate them. I just want to know if this is possible through
some other ways. i mean in DB2 we have an option of index rebuild
option which takes care of this job.
Do we have anything as such in BCP or is there some other way?

Any help will be appreciated

TIA

[m.bohse@quest-consultants.com]

Re: Regarding Index Rebuilding

SQL Server has also a rebuildindex option, but in your case probably
somenone has decided to drop the index in order to speed up the
inserts. Ans once dropped you can't rebuild the index you have to
re-create.
In SQL2005 you have the option to disable non-clustered indexes during
bcp actions, but in 2000 you can't.

Markus

[pankaj_wolfhunter@yahoo.co.in]

Re: Regarding Index Rebuilding

can i get some link for SQL2005 bcp utility????

[Erland Sommarskog]

Re: Regarding Index Rebuilding

(pankaj_wolfhun ter@yahoo.co.in ) writes:[color=blue]
> can i get some link for SQL2005 bcp utility????[/color]

There are not much differences between BCP in SQL 2000 and SQL 2005. The
ALTER INDEX command that Markus mentioned is part of SQL 2005. Just using
BCP from SQL 2005 is not changing anything.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[pankaj_wolfhunter@yahoo.co.in]

Re: Regarding Index Rebuilding

Thanx for the reply. One thing, i went through the docs of bcp utility
i didnt find any option for rebuilding the index.
Can you tell me the exact way of using it in bcp utility?

TIA

[Erland Sommarskog]

Re: Regarding Index Rebuilding

(pankaj_wolfhun ter@yahoo.co.in ) writes:[color=blue]
> Thanx for the reply. One thing, i went through the docs of bcp utility
> i didnt find any option for rebuilding the index.
> Can you tell me the exact way of using it in bcp utility?[/color]

BCP does not have any feature to rebuild indexes. You create the indexes
with the regular T-SQL commands.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[pankaj_wolfhunter@yahoo.co.in]

Re: Regarding Index Rebuilding

Thanks.

[Greg D. Moore \(Strider\)]

Re: Regarding Index Rebuilding


"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns97265EF EEA685Yazorman@ 127.0.0.1...[color=blue]
> (pankaj_wolfhun ter@yahoo.co.in ) writes:[color=green]
> > Thanx for the reply. One thing, i went through the docs of bcp utility
> > i didnt find any option for rebuilding the index.
> > Can you tell me the exact way of using it in bcp utility?[/color]
>
> BCP does not have any feature to rebuild indexes. You create the indexes
> with the regular T-SQL commands.
>[/color]

I have to do something like this quarterly.

The part I haven't bothered to automate yet is the script used to create the
indexes.

(That and dropping them... someday I'll script these parts.)

However, the rest is.

Basically I have a CMD file that I run at the OS level that truncate tables
in question.

Then it BCP's the data in and the calls OSQL to run scripts on the tables in
question to rebuild the indexes.

Basically it takes me about 1/2 hour of prep work (script out indexes incase
they've changed since last quarter), drop them, and then run the batch file
and walk away.

[color=blue]
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]