index then insert or insert then index

[jim]

I have to create a table with a clustered PK on col1, col2, col3 and insert 5.6 million rows. There are only 2 other columns in the table. Does it matter if I create table, insert, create PK or create table with PK then insert?

[Markus]

Hi Jim,

create the table, bcp the data and create then the index. Otherwise you would need to rebuild the index after the data load.


------------
jim at 3/3/01 3:55:54 PM


I have to create a table with a clustered PK on col1, col2, col3 and insert 5.6 million rows. There are only 2 other columns in the table. Does it matter if I create table, insert, create PK or create table with PK then insert?

[Joe]

Jim, Markus,
hello
The index is updated on insert,update or delete of data, so the need to rebuild the indexes is not necessary on SQL 7.0 and 2000. If this index is a full text index then you would need to rebuild. It is also always a good idea to enforce the constraint prior to loading the table...


------------
Markus at 3/5/01 2:50:30 AM

Hi Jim,

create the table, bcp the data and create then the index. Otherwise you would need to rebuild the index after the data load.


------------
jim at 3/3/01 3:55:54 PM


I have to create a table with a clustered PK on col1, col2, col3 and insert 5.6 million rows. There are only 2 other columns in the table. Does it matter if I create table, insert, create PK or create table with PK then insert?

[Joe]

Jim, one more thing...
It will take a little longer to insert the data due to the fact that the constraint is verifying that each record is unique, but attempting to enforce a primary key after the fact is much more time consuming, assuming that it will fail.


------------
Joe at 3/6/01 4:48:11 PM

Jim, Markus,
hello
The index is updated on insert,update or delete of data, so the need to rebuild the indexes is not necessary on SQL 7.0 and 2000. If this index is a full text index then you would need to rebuild. It is also always a good idea to enforce the constraint prior to loading the table...


------------
Markus at 3/5/01 2:50:30 AM

Hi Jim,

create the table, bcp the data and create then the index. Otherwise you would need to rebuild the index after the data load.


------------
jim at 3/3/01 3:55:54 PM


I have to create a table with a clustered PK on col1, col2, col3 and insert 5.6 million rows. There are only 2 other columns in the table. Does it matter if I create table, insert, create PK or create table with PK then insert?