By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,398 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,398 IT Pros & Developers. It's quick & easy.

Regarding Index Rebuilding

P: n/a
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

Dec 7 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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

Dec 7 '05 #2

P: n/a
can i get some link for SQL2005 bcp utility????

Dec 7 '05 #3

P: n/a
(pa***************@yahoo.co.in) writes:
can i get some link for SQL2005 bcp utility????


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, es****@sommarskog.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
Dec 7 '05 #4

P: n/a
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

Dec 8 '05 #5

P: n/a
(pa***************@yahoo.co.in) writes:
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?


BCP does not have any feature to rebuild indexes. You create the indexes
with the regular T-SQL commands.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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
Dec 8 '05 #6

P: n/a
Thanks.

Dec 8 '05 #7

P: n/a

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
(pa***************@yahoo.co.in) writes:
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?
BCP does not have any feature to rebuild indexes. You create the indexes
with the regular T-SQL commands.


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.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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

Dec 9 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.