bcp vs insert

[Ayu]

Anyone out there can explain the difference between bcp and insert?
Why is bcp generally faster than insert?
If I have a table with many indexes, is bcp still faster than insert?

And another question:
What is the difference between update index distribution and rebuilding index.
I noticed update index distribution takes less time...why?

I need the answers to support my arguments to my superior.
He is constantly saying that a fact table with many foreign keys will impact my insert tremendously (during loading time)
I know I'm doing a bulk copy insert and not the normal inserting records when loading but I just don't know how to explain it to him.

Thank you so much.

[SRINIVAS DASARI]

Hai AYU,
This is the answer for your question.
The BCP is a command-line utility that is executed from a DOS window with in Windows NT.
It is designed to move large amounts of data into and out of a sql server database,It will not transfer objects ,
ot will only transfer data.
It is the fastest way to load data into a sql server database because BCP doesn't enforce rules ,constants or
triggersduring the transfer like a INSERT statement do.
Other advantage of BCP is it will transfer data to an external tool spreadshhet or word processing file and it will
transfer selected columns or data with customized format,where as in insert it is not possiable.
BCP is also useful to transfer data between different platforms.
Fast BCP and slow BCP are the two modes used by BCP utility to tranfer data.The type of mode is automatically
selected by BCP utility depending on the characterstics of the data .
In fast BCP, sql server only logs the extent allocations used to increase the size of the destination table
instead of logging each transaction.
Indexes and consraints cannot be met for the destination table and the destination table cannot be marked tPOSThttp://WWW.SWYNK.COM:80/DISCUSS_SQL/DoPost.aspHTTP/1.0Content-length:1502Content-type:application/x-www

[SRINIVAS DASARI]

Hai AYU,
This is the answer for your question.
The BCP is a command-line utility that is executed from a DOS window with in Windows NT.
It is designed to move large amounts of data into and out of a sql server database,It will not transfer objects ,
ot will only transfer data.
It is the fastest way to load data into a sql server database because BCP doesn't enforce rules ,constants or
triggersduring the transfer like a INSERT statement do.
Other advantage of BCP is it will transfer data to an external tool spreadshhet or word processing file and it will
transfer selected columns or data with customized format,where as in insert it is not possiable.
BCP is also useful to transfer data between different platforms.
Fast BCP and slow BCP are the two modes used by BCP utility to tranfer data.The type of mode is automatically
selected by BCP utility depending on the characterstics of the data .
In fast BCP, sql server only logs the extent allocations used to increase the size of the destination table
instead of logging each transaction.
Indexes and consraints cannot be met for the destination table and the destination table cannot be marked tPOSThttp://WWW.SWYNK.COM:80/DISCUSS_SQL/DoPost.aspHTTP/1.0Content-le

[Srinivas dasari]

Answer to second question
When you add new data or update existing data on tables with indexes,the index pages are split
to accomidate new index rows on the index pages.A result of this splitting is that over a period of time an index's pages may be only partially filled.Since this splitting happens dynamically over a period of
time ,the performance of action queries can be affected.
If an index needs to be rebuilt ,you could drop and recreat it.Performing a drop and rebuild process of an index requires knoledge of the table and the data in that table.
I hope it will be enough for your question.
You can send mail if you need any clarification to this id.
srinivas_dasari@hotmail.com
Thanking you.

[Don Romano]

BCP exists to provide a fast way to load and unload data. It is not a transaction and there if very little error handling you can do. Basically it is what it is... bulk copy. It does not enforce constraints, rules, triggers, etc.

If a table where data is being bcp in does not have indexes and the 'Select Into/Bulk Copy' option is checked for the database... then bcp can load the table without logging the transaction. If the table has indexes then the load transactions will be logged.

Insert is a statement where constraints will be enforced, triggers fired, etc. The transaction is also logged. You can perform error handling.

BCP will probably (I hate absolutes) be faster because it is usually used with a table without indexes and without the need to perform validity checking during the load. You may want to run scripts after the load and index rebuild to insure integrity... depends how paranoid you are.

Foreign keys will not impact your bcp load but the indexes on the table will. I'd create a script to drop all of the indexes and then recreate them again after the load. If it's a FACT table that doesn't have much new data (if any at all) after the load than create indexes with a 100% fill factor to fully load the indexes.

Explanation to supervisor: bcp does not enforce constraints, rules or triggers. It does have to log and update existing indexes so by dropping them the load will go faster (no logging) and by creating them after the load the indexes will be optimum for retrieval.

[Ayu]

to srinivas dasari and don romano or anyone,
hi, thanks a lot for the explanation.
however, i'm still wonderin if i were to used a bcp within an ETL tool that lets me check data,
enforce rules (transforming data) on the data before loading into the db...
will this bcp still be considered faster than insert? i agree loading data into a table without
indexes would be faster but if i were to have a very big table (fact table), wouldn't rebuilding
the indexes take a long time? i've read somewhere, someone had to spend 6 hours just to
rebuild the indexes. ouch! once again, thank you for any ideas!

Ayu