Index building performance

I am building some tables with 10s of millions of
records. Each table has 1 or 2 indexes. Is it faster to
create the index when I create the table, then let it
build as I load the data. OR Should I load all my data
then let SQL build the index?
Thanks.Depends really. Say you've got a clustered index on a
table that you are going to bulk insert from a .txt file
into. You would have much better performance if you built
the clustered index after the data was all in place.
However, when you say building tables, do you mean just
insert? Or are you doing any insert/ update type of deals.
We very often do insert/ updates. (If already exists
update where column1 = this else insert.) For this
example, an index on column1 would be very benificial.
insert into
>--Original Message--
>I am building some tables with 10s of millions of
>records. Each table has 1 or 2 indexes. Is it faster to
>create the index when I create the table, then let it
>build as I load the data. OR Should I load all my data
>then let SQL build the index?
>Thanks.
>.
>|||You'll get better performance by loading data into a table with no
indexes and then building the indexes (including primary key and unique
constraints) afterward. The exception is you can load data into a table
with only a clustered index and get good performance if the data is
sorted in clustered index sequence.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"DB" <daveblair@.adelphia.net> wrote in message
news:1384801c38473$1a3b5f70$a601280a@.phx.gbl...
> I am building some tables with 10s of millions of
> records. Each table has 1 or 2 indexes. Is it faster to
> create the index when I create the table, then let it
> build as I load the data. OR Should I load all my data
> then let SQL build the index?
> Thanks.