Indexes on a table database

I will have to insert 1 billion codes into an SQL Server Database table. I'll be using BULK INSERT for this. My question is: Should I create the indexes befo开发者_StackOverflow社区re or after inserting the data? I need the quickest option

1 billion records into a single table? Ouch. If you are dealing with data volumes on that level and you have to ask this question on a forum then you may be out of your element. You should create the index after the insert but make sure that statistics are updated on the table after the insert. The index will work against you if statistics are not up to date.

When there are indexes on the table during an insert, the server will need to be constantly re-ordering/paging the table to keep the indexes up to date. If you drop the indexes, it can just add the rows without worrying about that, and then build the indexes all at once when you re-create them.

Read about Bulk Insert.

Also refer to:

bulk insert with or without index

Create indices before. BULK INSERT takes care of optimizing the indexing.

edit: this seems to be only true if the input data is ordered by the index. From http://msdn.microsoft.com/en-us/library/ms188365.aspx :

ORDER ( { column [ ASC | DESC ] } [ ,... n ] )

Specifies how the data in the data file is sorted. Bulk import performance is improved if the data being imported is sorted according to the clustered index on the table, if any