SQL Server Table Partion

1. Summary description

The table partitioning feature of SQL Server is to split a large table (with very many data in the table) into multiple files based on a condition (the table's primary key only) to improve efficiency when querying data. The main steps to create a table partition are 1, determine which field to use as the partition condition, 2, split into how many files to save the table, 3, partition function (split condition), 4, partition scheme (which filegroups need to be mapped after splitting the split function).

Here's a step-by-step explanation of how to create a table partition:

2. Preparatory work

Create a test table

CREATE TABLE Sale (      [Id] [int] IDENTITY () NOT NULL,          --autogrow      [Name] [varchar] (+) not NULL,      [Saletime] [ DATETIME] Not NULL,      CONSTRAINT [Pk_sale] PRIMARY key CLUSTERED  --Create PRIMARY key      (          [Id] ASC      )  )  

Inserting test data

Insert Sale ([name],[saletime]) VALUES (' Zhang San ', ' 2009-1-1 ')    insert Sale ([name],[saletime]) VALUES (' John Doe ', ' 2009-2-1 ') C2/>insert Sale ([name],[saletime]) VALUES (' Harry ', ' 2009-3-1 ')    insert Sale ([name],[saletime]) VALUES (' Money six ', ' 2012-4-1 ')    insert Sale ([name],[saletime]) values (' Zhao VII ', ' 2012-6-1 ')    insert Sale ([name],[saletime]) VALUES (' Zhang San ', ' 2012-6-1 ')    insert Sale ([name],[saletime]) VALUES (' John Doe ', ' 2012-7-1 ')    insert Sale ([Name],[saletime]) VALUES (' Harry ', ' 2012-8-1 ')    insert Sale ([name],[saletime]) VALUES (' Money six ', ' 2012-10-1 ')    insert Sale ([name],[ Saletime]) VALUES (' Zhao VII ', ' 2012-10-1 ')    insert Sale ([name],[saletime]) VALUES (' Zhang San ', ' 2012-11-1 ')    insert Sale ( [Name],[saletime]) VALUES (' John Doe ', ' 2013-12-1 ')    insert Sale ([name],[saletime]) VALUES (' Harry ', ' 2014-12-1 ')  

3. Implementation steps

Primary key Settings

A table partition needs to determine a field to split according to a condition in this field, where we use the Saletime column as an example to split the sale table on a monthly basis. Since the column that needs to be split must be a primary key, we will first delete the primary key created for the ID field when we originally built the table, and change to the Saletime field (note the nonclustered primary key)

1) Modify the table

2) Remove primary key

3) New primary key

4) Setting the association

Because the primary key requires uniqueness, the associated primary key (ID and Saletime) for 2 fields is required here

5) Modify primary key to non-clustered

The effect after success

Remember to save the table when you are done

Creating filegroups and Data files

Because the table partition is saved according to the filegroup, and the actual data is stored in the files contained in this filegroup, so in order to be efficient, a filegroup can be a data file to save the data, below we create a filegroup in the month unit

The file group and data files have been created and the corresponding relationship has been established, and after clicking the OK key, we can see that the 12 data files we set up have been generated in the corresponding directory.

create partition function (cannot visualize implementation)

--fourth step creating partition Function Create PARTITION function Partfunsale (DATETIME)   as RANGE right for VALUES (     ' 2012-02-01 ', ' 2012-03-0 1 ', ' 2012-04-01 ', ' 2012-05-01 ', ' 2012-06-01 ',     ' 2012-07-01 ', ' 2012-08-01 ', ' 2012-09-01 ', ' 2012-10-01 ', ' 2012-11-01 ', ' 2012-12-01 ')

The meaning of this paragraph is to create a partition function with a datetime field type, noting that 12 filegroups correspond to 11 values, because the rules for partitioning are

Filegroup 1 ——— data before 2012-02-01 (date >2012-02-01)
Filegroup 2 ——— data before 2012-03-01 after 2012-02-01 (2012-02-01≤ date <2012-03-01)
Filegroup 3 ——— data before 2012-04-01 after 2012-03-01 (2012-02-01≤ date <2012-03-01)
Filegroup 4 ——— data before 2012-05-01 after 2012-04-01 (2012-02-01≤ date <2012-03-01)
Filegroup 5 ——— data before 2012-06-01 after 2012-05-01 (2012-02-01≤ date <2012-03-01)
Filegroup 6 ——— data before 2012-07-01 after 2012-06-01 (2012-02-01≤ date <2012-03-01)
Filegroup 7 ——— data before 2012-08-01 after 2012-07-01 (2012-02-01≤ date <2012-03-01)
Filegroup 8 ——— data before 2012-09-01 after 2012-08-01 (2012-02-01≤ date <2012-03-01)
Filegroup 9 ——— data before 2012-10-01 after 2012-09-01 (2012-02-01≤ date <2012-03-01)
Filegroup ——— data before 2012-11-01 after 2012-10-01 (2012-02-01≤ date <2012-03-01)
Filegroup one ——— 2012-11-01 data before 2012-12-01 (2012-02-01≤ date <2012-03-01)
Filegroup ——— data after 2012-12-01 (2012-02-01≤ date <2012-03-01)

Create partition scheme (cannot visualize implementation)

Associating a created partition function with a filegroup

--fourth Step create partition scheme (note more than partition function) Create PARTITION scheme Partschsale as   PARTITION Partfunsale to   (          Saletime201201 ,     Saletime201202,       Saletime201203,       Saletime201204,       Saletime201205,       Saletime201206,       Saletime201207,       Saletime201208,     Saletime201209,       Saletime201210,       Saletime201211,       Saletime201212    )

Associating to a table (cannot visualize implementation)

Associating a well-set partition scheme with a specific table

--Fifth Step set partition scheme to the specified table create CLUSTERED INDEX ct_sale on Sale ([Saletime])  

where sale is the table name, Saletime is the field to be split, Partschsale is the partition scheme

After you have created the right-click property in the database, you can see an effect similar to the following to show that the creation was successful

Statistics the number of data bars contained in each data set

--Count the total number of records in all partition tables   select $PARTITION. Partfunsale (saletime) as partition number, COUNT (ID) as record count from Sale GROUP by $ Partition.partfunsale (Saletime)  

SQL Server Table Partion