Step by step to teach you to build a table partition for a SQL database _mssql

Directly on the steps:

1) Create a new database

2) Add several filegroups

3) Go back to the General tab and add the database file

See the place with the red box? The filegroup that was established in the previous step is used here. Looking at the following path, I put each file on a separate disk, preferably on a separate physical disk, which greatly improves the performance of the data. Click on the "OK" database even if the creation is complete.

4 The next thing to do is to create a number of partition lines, the SQL statement is as follows: When we learn, it is best not to copy directly, to 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 a partitioning condition, of course, you can also use other, such as int and so on, as long as the good segmentation can be.

Note here that the right keyword means that when the record time (which will be pointed to a field in the table below) is greater than or equal to 20000101, the data is divided into the next range, for example, the data before January 1, 2000 is divided into one area, Data that contains January 1, 2000 and after will be divided into two areas, and so on. Right can also use left substitution, meaning ditto similar. In addition, I've defined four split points above, and these four split points are based on the filegroup we just created. The four split points can produce 5 interval segments, and we store each interval data in a filegroup.

After executing the above statement correctly, you can find the partition function named "Partfuncforexample" in the data, as shown in the following figure

5 after the partition function is established, we will establish the partition scheme. The goal is to map partition-generated partitions to file data sets. The partitioning function tells the database how to partition the data, and the partitioning scheme tells the database how to save the partitioned data to which filegroup.

I'll create the partitioning scheme below.

Create Partition Scheme partschforexample//Creating a partition scheme + partition scheme name as 
Partition partfuncforexample//purpose to Partition functions Partfuncforexample 
to 
( 
PRIMARY,//file group name 
Partition1,//Filegroup name 
Partition2,//Filegroup name 
Partition3,//file group name 
Partition4//Filegroup Name 

After the correct execution can be seen in the partition scheme, the following figure

6 soon soon the big announcement became, below we set up to partition to store the table, the table's data should theoretically be very very many, millions other records above and basically is not updated. Otherwise, it doesn't make much sense to create partitioned storage.

Create Table partitiontable ( 
[ID] [int] Identity (1,1) not NULL, 
[Name] [nvarchar] (a) not NULL, 
[ Logindate] [DateTime] NOT NULL//used for partitioning 

Execute this section of SQL, no error words will be done, hehe.

Summarize:

Partitioned storage improves the performance of the database, the data that is stored in the partition is physically multiple files, but logically it is still a table, and any action on the table is the same as before the partition. Inserts, deletes, inquires, updates and so on the time, the database will automatically find the corresponding partition for you, then carries on the operation. In addition, multiple data files, log files are deployed on different high-performance physical disk, can also greatly improve performance.

Of course, the benefits of partitioned storage There are many I do not know, welcome to discuss together!