Efficiency of INSERT with multiple rows

Let's say that I am inserting rows into a table via a statement like this
INSERT INTO A SELECT * FROM B
And let's say that table B has 1,000,000 rows
I was just wondering if SQL Server rebuilds the indexes on table A after
each row is inserted or if it waits until all 1,000,000 rows are inserted
and then rebuilds the indexes at the end? If it rebuilds the indexes upon
each insert then I would probably drop the indexes first and then just add
them at the end.
Does anybody know?
Thanks
Richard Speiss
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
THe index is not 'rebuild' upon each insert. It is updated on each insert.
Since 'table splits' on indexes can be costly, it is sometimes, a good idea
to remove the index.
But that depends, if you have a very large table with bilions of rows and
remove and reapply the index :<<
ps: For batch inserting and bulking rows, always initiate an explicit
transation!

> Does anybody know?
> Thanks
> Richard Speiss
>
|||Oops, I did mean does the index get updated (bad wording on my part. I
didn't expect the entire thing to be rebuilt).
Thanks for the reply
Richard

> THe index is not 'rebuild' upon each insert. It is updated on each insert.
> Since 'table splits' on indexes can be costly, it is sometimes, a good
idea
> to remove the index.
> But that depends, if you have a very large table with bilions of rows and
> remove and reapply the index :<<
> ps: For batch inserting and bulking rows, always initiate an explicit
> transation!
>
|||Richard,
SQL Server does not rebuild the indexes as data is inserted, rather the
indexes are "maintained". i.e. if you insert a new row into the table, then
all indexes on the table will need to have the index pages maintained at the
same time.
Sometimes you will find that dropping all indexes, doing a large insert, and
then creating the index after the load is quicker than doing the load with
the indexes defined. Sometimes you won't. Only testing will reveal which way
will be quick for your environment.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
> Does anybody know?
> Thanks
> Richard Speiss
>