Step by step SQL database partition

Time:2020-11-22

Direct steps:

1) Create a new database

 

2) Add several filegroups

3) Return to the General tab and add the database file

Do you see the red boxes? The file group created in the previous step is used here. Look at the following path, I put each file on a different disk, and it is best to put it on a different physical disk, which will greatly improve the performance of the data. Click “OK” to create the database.

4) The next thing to do is to create a partition row number. The SQL statement is as follows: when you study, you’d better not copy it directly, but copy it again.


create partition function PartFuncForExample(Datetime) 
as Range Right for Value('20000101','20010101','20020101','20030101') 

Here I am going to use a time field in the table as the partition condition. Of course, you can also use other fields, such as int, as long as the segmentation is good.

Note the right keyword here, which means that when the record time (which will be referred to a field in the table below) is greater than or equal to 20000101, the data will be divided into the next interval. For example, the data before January 1, 2000 will be divided into zone 1, and the data including January 1, 2000 and after will be divided into zone 2, and so on. Right can also be replaced by left, meaning the same as above. In addition, I have defined four split points, which are based on the file group we just created. Four segmentation points can generate five interval segments. We store the data of each segment into a file group.

After executing the above statement correctly, you can find the partition function named “partfuncforexample” in the data, as shown in the figure below

5) After the partition function is set up, we can create the partition scheme again. The purpose is to map the partition generated by the partition function to the file data group. The partition function tells the database how to partition the data, while the partition scheme tells the database how to store the partitioned data in which file group.

Let me create a partition scheme.

Create partition scheme partschforexample // create a partition scheme + partition scheme name 
As partition partfuncforexample // the purpose is to partition the function partfuncforexample 
To 
( 
Primary, // filegroup name 
Partition1, // filegroup name 
Partition2, // filegroup name 
Partition3, // filegroup name 
Partition4 // filegroup name 
)

It can be seen in the partition scheme after correct execution, as shown in the figure below

6) We are about to make a big announcement soon. Let’s create a table to be partitioned and stored. Theoretically, the data in this table should be very, very large, with records of more than one million levels and basically not updated. Otherwise, it doesn’t make much sense to build partitioned storage.

Create Table PartitionTable( 
[ID] [int] Identity(1,1) not null, 
[Name] [nvarchar](50) not null, 
[logindate] [datetime] not null // used for partitioning 
)On partschforexample ([logindate]) // the specified partition field mentioned above is specified here.

Execute this SQL, if there is no error, you will be finished

Summary:

Partitioned storage improves the performance of the database. The partitioned data is physically multiple files, but logically it is a table, and any operation on the table is the same as before. When you insert, delete, query, update and so on, the database will automatically find the corresponding partition for you, and then execute the operation. In addition, the performance can be greatly improved by deploying multiple data files and log files on different high-performance physical disks

Of course, there are many advantages of partition storage that I don’t know, welcome to discuss together!