473,725 Members | 2,428 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Regarding Index Rebuilding

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
7 3221
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
can i get some link for SQL2005 bcp utility????

Dec 7 '05 #3
(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****@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
Dec 7 '05 #4
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
(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****@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
Dec 8 '05 #6
Thanks.

Dec 8 '05 #7

"Erland Sommarskog" <es****@sommars kog.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****@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

Dec 9 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4766
by: xo55ox | last post by:
Hi, I want to find out what is a good way to identify indexes that need rebuilding. I tried to run DBCC showcontig and identify them based on the scan density %. And according to a Microsoft Tech Net - All About SQL Server Indexes discussion, the expert stated that based on the Density (below 20%), I can pick out those indexes associated to the table and run DBCC INDEXDEFRAG. But instead, I ran DBCC DBREINDEX to rebuild all indexes...
8
5260
by: Mike | last post by:
Hello, I have a few rather urgent questions that I hope someone can help with (I need to figure this out prior to a meeting tomorrow.) First, a bit of background: The company I work for is developing a web-based application, one part of which involves allowing the user the ability to page through transaction "history" information. The _summary_ history table will have the following fields: ServiceName, Date, User-Ref1, User-Ref2,...
3
3838
by: Bari Allen via AccessMonster.com | last post by:
I have a database in a Front-End/Back-End User Environment, which has been running fine, for 4 years. Lately, when the users try to enter data (from a form into a single table) they've been getting the message that they can't add the records, because it would create "duplicate Values in the Index". The table's Primary Key is an AutoNumber Increment field. To solve the problem, I copied the table structure to a new table, and appended...
2
1364
by: B-Dog | last post by:
Any idea why when I try to fill my dataset using xContacts.Fill(xds.Contacts) I get the error: : Index was outside the bounds of the array. I've tried rebuilding my dataadapters and dataset and still get the error, can preview it fine. Thanks
2
2235
by: Peter Lindquist | last post by:
I had a fun issue this morning, and now I'm wondering if I violated a rule I didn't know about. Any insight would be much appreciated. All operations involving this particular table may be assumed to have functioned normally before today. bash-2.05a$ cat PG_VERSION 7.3 Last night, we added 'index_t_payment_param' to this table. payment_id is not a unique entry, hence the composite primary key, and I've since
10
8880
by: wackyphill | last post by:
After rebuilding an index, it still shows as the same amount of fragmentation. ANy ideas what's wrong? I'm determining which indexes to rebuild using the following query: SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName, ips.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,
5
1741
by: vj_dba | last post by:
Hi group, I have a table say with 10000 records and 10 columns initially, I created an index say i1, after few days I altered the table by adding 10 new columns and 90000 rows of records summing up to 100000 records. My doubt is regarding the index is Do I have to create a new index ? or can I use the same index which I created first, or do I have to make any changes to existing index like rebuild etc...? What needs to be done for the...
8
5207
by: rcamarda | last post by:
I thought I would delve into index fragmentation and I found some great sql from many posters (thanks Erland!). My question is how bad is bad? I know this is very subjective. Some scripts I found would reindex if the LogicalFragmenation is over 30%. I have some tables that are 98% (I'm guessing really bad). I know it all depends.. more as a learning point: I found a table that had over 30% logicalfragmentation, I dropped the indexes,...
1
1697
by: trd79 | last post by:
Hi, I am extracting data from an operational system in order to maintain a mirror that can be used for reporting. I have two means of filling this database: 1) A stored procedure which truncates all tables and rebuilds everything from scratch. This takes about an hour and will be run periodically. 2) A stored procedure that updates my database with the previous days activity which will be run daily. This will DELETE and INSERT but...
0
8748
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9248
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9164
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8072
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6000
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4506
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4775
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3212
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2622
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.