Detailed steps for partitioning tables, placing them on specific devices, and loading data with parallel bulk copy are in Chapter 6, “Controlling Physical Data Placement,” in the book Performance and Tuning: Basics. The commands and tasks for creating, managing, and maintaining partitioned tables are:
alter database – to make devices available to the database.
sp_addsegment – to create a segment on a device; sp_extendsegment to extend the segment over additional devices, and sp_dropsegment to drop the log and system segments from data devices.
create table...on segment_name – to create a table on a segment.
alter table...partition and alter table...unpartition – to add or remove partitioning from a table.
create clustered index – to distribute the data evenly across the table’s partitions.
bcp (bulk copy) – with the partition number added after the table name, to copy data into specific table partitions.
sp_helpartition – to display the number of partitions and the distribution of data in partitions, and sp_helpsegment to check the space used on each device in a segment and on the segment as a whole.
Figure 7-8 shows a scenario for creating a new partitioned table.
Figure 7-8: Steps for creating and loading a new partitioned table
