Home>

Partitioned storage improves database performance,The data stored by the partition is physically multiple files,But logically it is still a table,Any operation on the table is the same as before the partition.When inserting, deleting, querying, updating, etc.,The database will automatically find the corresponding partition for you,Then perform the operation.

Directly on the steps:

1) Create a new database

2) Add several file groups

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

Seeing the place framed by the red frame, the file group created in the previous step is used here.Looking at the path behind,I put each file on a separate disk,And it's better to put them on different physical disks separately.This will greatly improve the performance of the data. Click "OK" to complete the database creation.

4) The next thing to do is to create a number of partition rows,The SQL statement is as follows:When you are studying, it is best not to copy directly, it is better to copy it manually.

create partition function partfuncforexample (datetime)
as range right for value ("20000101", "20010101", "20020101", "20030101")

Here I am going to use a certain time field in the table as the condition for partitioning.Of course you can also use other,Such as int, as long as it is well segmented.

Note the right keyword here, which means that when the time of the record (referred to below a field in the table) 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 a district,Data containing January 1, 2000 and after will be divided into two areas,And so on.Right can also be replaced by left, which has the same meaning as above.In addition, I defined four split points above,These four split points are determined based on the file group we just created.Four segmentation points can generate 5 interval segments,We store the data of each interval into a file group.

After executing the above statement correctly, you can find the partition function named "partfuncforexample" in the data.As shown below

5) After the partition function is established,Let's build the partition scheme again.The purpose is to map the partitions generated by the partition function to the file data group.The partition function tells the database how to partition the data.The partitioning scheme is to tell the database how to store the partitioned data in which filegroup.

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 function partfuncforexample
to
(
primary, //filegroup name
partition1, //filegroup name
partition2, //filegroup name
partition3, //File group name
partition4 //File group name
)

It can be seen in the partitioning scheme after the correct implementation,As shown below

6) The big announcement is coming soon,Let's create a table to be partitioned.The data of this table should theoretically be very, very large,Millions of records are above and basically not updated.Otherwise, it doesn't make much sense to establish partition 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.

Executing this sql, you are done without error,Ha ha.

to sum up:

Partitioned storage improves database performance,The data stored by the partition is physically multiple files,But logically it is still a table,Any operation on the table is the same as before the partition.When inserting, deleting, querying, updating, etc.,The database will automatically find the corresponding partition for you,Then perform the operation.In addition, multiple data files and log files are deployed on different high-performance physical disks.It can also greatly improve performance.

  • Previous Struts2 select tag usage example analysis
  • Next Analysis of functions and pointers in C ++