SQL Server table Partition

In SQL Server 2005, table partitions are finally introduced, that is, when there is a large amount of data in a table, it can be split
Multiple tables greatly improve the performance. The following is an example

For example, create the following directories under drive C:
C: \ data2 \ primary
C: \ data2 \ FG1
C: \ data2 \ FG2
C: \ data2 \ FG3
C: \ data2 \ fg4

The primary stores the master database file, the other FG1--FG4 stores four separate file groups, you can see the creation of the database
Data Partition DB2, as shown below

Use [Master] Go/****** object: database [Data Partition dB] script Date: 10/08/2006 23:09:53 ******/if exists (Select name from sys. databases where name = n' Data Partition DB2 ') drop database [Data Partition DB2] Go create database [Data Partition DB2] on primary (name = 'data partition dB primary fg ', filename = 'C: \ data2 \ primary \ Data Partition dB primary FG. MDF ', size = 5, maxsize = 500, filegrowth = 1), filegroup [Data Partition dB FG1] (name = 'data partition dB FG1', filename = 'C: \ data2 \ FG1 \ Data Partition dB fg1.ndf ', size = 5 MB, maxsize = 500, filegrowth = 1 ), filegroup [Data Partition dB FG2] (name = 'data partition dB FG2 ', filename = 'C: \ data2 \ FG2 \ Data Partition dB fg2.ndf', size = 5 MB, maxsize = 500, filegrowth = 1), filegroup [Data Partition dB FG3] (name = 'data partition dB fg3', filename = 'C: \ data2 \ FG3 \ Data Partition dB fg3.ndf ', size = 5 MB, maxsize = 500, filegrowth = 1 ), filegroup [Data Partition dB fg4] (name = 'data partition dB fg4', filename = 'C: \ data2 \ fg4 \ Data Partition dB fg4.ndf', size = 5 MB, maxsize = 500, filegrowth = 1) Next, we will create a partition table function, which can be understood as a rule to describe how to divide a table by a rule, as shown below:
Use [Data Partition DB2] Go create partition function [Data Partition range] (INT) as range left for values (100,200,300) where the Partition Function Name Is Data Partition range, the following type (INT) indicates that the type of the field used for partitioning is int,

 
Values (100,200,300) indicates that the table is divided into four partitions, from negative number to 100,100-200,200-300, greater than 300.
 

 
Next, we will create a partition architecture to apply the partition function to the four file groups we have divided.
 

 
Use [Data Partition DB2] Go create Partition Scheme [Data Partition Scheme] As partition [Data Partition range] To ([Data Partition dB FG1], [Data Partition dB FG2], [Data Partition dB FG3], [Data Partition dB fg4]);
 

 
Create a table structure
Use [Data Partition DB2]
 

Go
Create Table mytable
(ID int not null, date datetime, cost money)
On [Data Partition Scheme] (ID );

Note that on [Data Partition Scheme] (ID); indicates that the partition is based on the size of the ID, and the name of the Shard to be purchased after on

Finally, we can fill in the data.

Use [Data Partition DB2] Go declare @ count int set @ COUNT =-25 while @ count <= 100 begin insert into mytable select @ count, getdate (), 100.00 set @ COUNT = @ count + 1 end set @ COUNT = 101 while @ count <= 200 begin insert into mytable select @ count, getdate (), 200.00 set @ COUNT = @ count + 1 end set @ COUNT = 201 while @ count <= 300 begin insert into mytable select @ count, getdate (), 300.00 set @ COUNT = @ count + 1 end set @ COUNT = 301 while @ count <= 400 begin insert into mytable select @ count, getdate (), 400.00 set @ COUNT = @ count + 1 end set @ COUNT = 401 while @ count <= 800 begin insert into mytable select @ count, getdate (), 500.00 set @ COUNT = @ count + 1 end
 

Finally, we can check whether the inserted data is actually partitioned into four table partitions in different file groups.
Select *, $ partition. [Data Partition range] (ID)
From mytable