Detailed process of creating MSSQL table partitions

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.

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 new files and put them in the FirstPart, SecondPart, and ThirdPart folders of my local D: SPPartition folder. Create three file files corresponding to three file groups respectively, 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:

The Code is as follows:
Alter database CenterMy Add filegroup FGSP1 GO Alter database CenterMy ADD FILE ( NAME = 'sptestlevel1 ', FILENAME = 'd: SPPartitionFirstPartSPTestLevel1.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.

The Code is as follows:
Alter database CenterMy Add filegroup FGSP2 GO Alter database CenterMy ADD FILE ( NAME = 'sptestlevel2 ', FILENAME = 'd: SPPartitionSecondPartSPTestLevel2.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.

The Code is as follows:
Alter database CenterMy Add filegroup FGSP3 GO Alter database CenterMy ADD FILE ( NAME = 'sptestlevel3 ', FILENAME = 'd: SPPartitionThirdPartSPTestLevel3.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:

The Code is as follows:
SELECT * FROM sys. filegrou

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

View the current data file:

The Code is as follows:
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.

The Code is as follows:
Create partition function TestSPFunction (INT) As range right for values (500,100, 1300) GO

Here, the Range can be RIGHT or LEFT. Here, the Range is RIGHT, and RIGHT indicates yes. <或> =, 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.

The Code is as follows:
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.

*

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,