To create a new partitioned table and load the data with bcp:
Create the table on the segment, using the on segment_name clause. For information on creating segments, see “Creating objects on segments”.
Partition the table, with one partition for each physical device in the segment.
See “alter table...partition syntax”.
If the input data file is not in clustered key order,
and the table will occupy more than 40% of the space on
the segment, and you need a clustered index.
Copy the data into the table using parallel bulk copy.
See “Using parallel bcp to copy data into partitions” for examples using bcp.
If you do not need a clustered index, use sp_helpartition to verify that the data is distributed evenly on the partitions.
See “Getting information about partitions”.
If you need a clustered index, the next step depends on whether the data is already in sorted order and whether the data is well balanced on your partitions.
If the input data file is in index key order and the distribution of data across the partitions is satisfactory, you can use the sorted_data option and the segment name when you create the index. This combination of options runs in serial, checking the order of the keys, and simultaneously building the index tree. It does not need to copy the data into key order, so it does not perform load balancing. If you do not need referential integrity constraints, you can use create index.
See “Using drop index and create clustered index”.
To create a clustered index with referential integrity constraints, use alter table...add constraint.
See “Using constraints and alter table”.
If your data was not in index key order when it was copied in, verify that there is enough room to create the clustered index while copying the data.
Use sp_spaceused to see the size of the table and sp_helpsegment to see the size of the segment. Creating a clustered index requires approximately 120% of the space occupied by the table.
If there is not enough space, follow the steps in “If there is not enough space to re-create the clustered index”.
Create any nonclustered indexes.
Dump the database.
