Create MSSQL table partitions

Recently, in the project database optimization process, the database table partition solution was taken into account. mssql2005 added the table partition concept. Now I use the test table to make a table partition. Table partitions can store different data in different database files and separate data by physical addresses. Theoretically, if the server is a multi-disk, multi-CPU, and a disk array, query operations are more efficient.

Currently, table partitions exist in almost all databases, but some details about MSSQL table partitions are different. I also have some knowledge about them. Therefore, this article does not explain the principles in detail, this section describes how to create MSSQL table partitions.

First, create a new database file, put it in the firstpart, secondpart, and thirdpart folders in my local D: \ sppartition folder, and create three file files corresponding to three file groups, take my local database centermy as an example. It now has a table testsp, which is a user table. Now the test data has more than 1000 pieces of data. The creation code is as follows:

Alter database centermy

Add filegroup fgsp1

Go

Alter database centermy

Add File

(

Name = 'sptestlevel1 ',

Filename = 'd: \ sppartition \ firstpart \ sptestlevel1.ndf ',

Size: 5120 kb,

Maxsize = unlimited,

Filegrowth = 5120 KB

) To filegroup fgsp1

Go

Create a file group fssp1. The default start size is 5 MB, and the maximum file data is not limited. The size increases by 5 MB according to the actual number of files. The files created below are created with the same configuration, add a corresponding database file sptestlevel1.ndf to the file group.

Alter database centermy

Add filegroup fgsp2

Go

Alter database centermy

Add File

(

Name = 'sptestlevel2 ',

Filename = 'd: \ sppartition \ secondpart \ sptestlevel2.ndf ',

Size: 5120 kb,

Maxsize = unlimited,

Filegrowth = 5120 KB

) To filegroup fgsp2

Go

Create a file group fssp2 and add the corresponding database file sptestlevel2.ndf.

 

Alter database centermy

Add filegroup fgsp3

Go

Alter database centermy

Add File

(

Name = 'sptestlevel3 ',

Filename = 'd: \ sppartition \ thirdpart \ sptestlevel3.ndf ',

Size: 5120 kb,

Maxsize = unlimited,

Filegrowth = 5120 KB

) To filegroup fgsp3

Go

Create a file group fssp3 and add the corresponding database file sptestlevel3.ndf.

 

Query the group of the current database centermy:

Select *

From SYS. filegroups

We can see that there are already four groups, because there is a primary main file group by default.

View the current data file:

Select *

From SYS. database_files

You can see the directory where the data file is located.

 

Create a partition function below, which will set a standard for data distribution in the partition.

Create Partition Function testspfunction (INT)

As range right for values (500,100, 1300)

Go

The range can be right or left. The range here is right, right indicates <or> =, and left indicates <= or>. That is, <500 is a partition,> = 500 and <1000 is a partition,> = 1000 and <1300 is a fetch,> = 1300 is a partition, the partition is divided into four segments.

Then create a partition scheme, which maps the created partition function to a file group, which corresponds to the physical database file on the disk.

Create Partition Scheme testspscheme

As partition testspfunction

To ([primary], fgsp1, fgsp2, fgsp3)

Go

Here, we map the four segments of the Partition Function to four file groups.
Then, link the table to the partition scheme. Here, the spno primary key is used as the partition column.

Alter table testsp add constraint [pk_spno] primary key clustered (spno)

On testspscheme (spno)

 

Through the above operation, the table partition is created and the data is automatically placed in the corresponding partition. Next we will query the data volume of each partition.

Select *

From SYS. partitions

Where object_name (object_id) = 'testsp'

We can see that the primary partition has 467 records. The values of partion_number 1, 2, and 3 correspond to the number of row data records corresponding to different file groups.

Finally, when querying a table, you can query the table partition without knowing it at all. Let's check the partition corresponding to the data:

Select *, $ partition. testspfunction (spno)

From testsp

We found that spno 500 is in the second partition, which proves that the table partition has been created successfully!

The preceding section briefly describes the table partitioning process. mssql2008 also adds a visual interface to add table partitions,

 

This article mainly shows the partition process. For the actual project, whether or not the partition is required, how the partition is, What disk the partition is deployed to, whether the index is created after the partition, and whether it can achieve the optimization effect, you need to consider more based on the actual situation,