How to Partition Tables in SQL Server 2008

Partitioning tables spans the data contained in that table across multiple files. SQL Server provides you with a function to create a partition for your tables. This improves speed when writing table data, so it is convenient for users who have tables with millions of records and large data files. You run the "Create Partition" function in your SQL console to segment your data and specify how many records are included in each file.

Instructions

1. Click the Windows "Start" button and select "All Programs." Click "SQL Server" in the program groups and then click "SQL Server Management Studio" to open your console. Click "New Query" to open your SQL text editor.

2. Create your partition scheme. A partition scheme sets the number of records that span across each file. In this example, 100 records are stored to a table before the database engine starts adding records to the second partition. After 100 records are stored on the second partition, the database stores another 100 records on the first partition. This continues as users add records to the tables. The following code sets up a scheme:

   create partition function table_scheme (int) as range left for values (100);

3. Attach the new scheme to a database. In this example, the table_scheme created in Step 2 is added to the customer database. Type the code below into your editor:

   create partition scheme customer as partition table_scheme to ([PRIMARY], customer);

4. Create the partitioned table. Now that the partition scheme is created, you can add your tables to the partition information. The following code creates an "orders" table to the partitioned information:

   create table order (customerId int)

   on table_scheme(ID);

References

• Microsoft: Create Partition Function