Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you an introduction to the SQL Server partition table. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Preface

If the data in one of the tables in your database meets the following conditions, then you should consider creating a partitioned table.

There is a lot of data in a table in the database. What is the concept of many? Ten thousand? Twenty thousand? Or 100, 000 or 1 million? I think it is a question of the benevolent see benevolence and the wise see wisdom. Of course, there is so much data in the data table that the query obviously feels that the data is very slow, so you can consider using partitioned tables. If I have to say a number, I think it is 1 million.

However, more data is not the only condition for creating a partitioned table, even if you have 10 million records, but these 10 million records are commonly used records, then it is best not to use partitioned tables, the loss may outweigh the gain. Only if your data is segmented, then consider whether you need to use a partition table.

What do you mean the data is segmented? Although this statement is very unprofessional, it is easy to understand. For example, your data is separated by years. For this year's data, you often add, modify, delete, and query, while for previous years, you hardly need to operate, or your operations are often limited to queries. Congratulations, you can use partition tables. In other words, if your operation on the data often involves only part of the data rather than all the data, then you can consider what partitioned table.

Introduction to Partition Table

A partitioned table is to divide a large table into several small tables. Suppose you have a sales record table, which records the sales of each mall, then you can divide the sales record table into several small tables according to time, for example, five small tables. Records prior to 2009 use a table, records in 2010 use a table, records in 2011 use a table, records in 2012 use a table, and records after 2012 use a table. In that case, you can query the records of which year you want to query in the corresponding table. Because there are fewer records in each table, the query time will naturally be reduced.

However, the processing of dividing a large table into several small tables will make it more difficult for programmers to program. Take adding records as an example. The above five tables are independent of five tables. Programmers use different SQL statements when adding records at different times. For example, when adding records in 2011, programmers add records to the table in 2011; when adding records in 2012, programmers add records to the table in 2012. In this way, the programmer's workload will increase and the possibility of error will increase.

The above problems can be well solved by using partition tables. A partitioned table can physically divide a large table into several small tables, but logically, it is still a large table.

Following the above example, a partition table can divide a sales record table into five physically small tables, but for a programmer, he is still faced with a large table, whether it is adding records in 2010 or 2012. For the programmer, there is no need to think about it, he just needs to insert the record into the sales record table-- the big table in the logic. SQL Server will automatically put it in the physical table where it should be.

Similarly, for queries, programmers only need to set the query conditions, OK,SQL Server will automatically go to the corresponding table query, do not worry too much.

Creation of partition table

First, the first step in creating a partitioned table is to create a database filegroup, but this step can be omitted because you can use PRIMARY files directly. But I personally think that in order to facilitate management, you can first create a few filegroups, so that you can put different small tables in different filegroups, which is not only easy to understand but also improve the running speed. The way to create a filegroup is simple. Open SQL Server Management Studio, find the database where the partition table is located, right-click, and select Properties from the pop-up menu. Then select the FileGroup option and click the add button below.

Second, after you create the filegroup, you have to create a few more database files. Why create a database file, which is easy to understand, because the small partitioned table must be placed on the hard disk, and where on the hard disk? In the file, of course. Besides, there are no files in the filegroup, so what's the use of the filegroup? Again in the interface above, select the "File" option, and then add a few files. Note the following when adding files:

1. Don't forget to put different files in the filegroup. Of course, a filegroup can also contain multiple different files.

2. If possible, put different files on different hard disk partitions, preferably on different separate hard drives. It is important to know that the speed of IQ is one of the important conditions that affect the running speed of SQL Server. Putting different files on different hard drives can speed up the running of SQL Server.

In this example, for convenience, all database files are placed on the same hard disk, and there is only one file in each filegroup.

Third, create a partition function. This step is necessary, and the purpose of creating a partition function is to tell SQL Server how to partition the partition table. This step must be done with some SQL script. With the above example, we will divide the sales table into five small tables according to time. Suppose the time is divided as follows:

The first small table: data prior to 2018-1-1 (excluding 2018-1-1).

The second small table: data between 2018-1-1 (including 2018-1-1) and 2018-12-31.

The third small table: data between 2019-1-1 (including 2019-1-1) and 2020-12-31.

The fourth small table: data between 2020-1-1 (including 2020-1-1) and 2021-12-31.

The fifth small table: data after 2021-1-1 (including 2021-1-1).

Then the code for the partition function is as follows:

CREATE PARTITION FUNCTION partfunSale (datetime)

AS RANGE RIGHT FOR VALUES ('20180101' 20190101 '20200101')

Where:

1. CREATE PARTITION FUNCTION means to create a partition function.

2. PartfunSale is the name of the partition function.

3. The way AS RANGE RIGHT sets the partition scope is Right, that is, the right way.

4. FOR VALUES (20180101, 20190101, 20200101, 20210101) is divided according to these values.

To note here, in Values, '20180101', '20190101', '20200101' and '20210101' are all conditions for partitioning. "20180101" represents January 1, 2018, and records less than this value will be divided into a small table, as shown in Table 1; values less than or equal to '20180101' and less than '20190101' will be placed in another table, as shown in Table 2. And so on, in the end, all values of size or equal to '20210101' are placed in another table, such as Table 5.

One might ask, why is the value "20190101" in Table 2 instead of Table 1? This is determined by RIGHT in AS RANGE RIGHT, which means to put data equal to this value in the table on the right, which is Table 2. If you use Left instead of RIGHT in your SQL statement, it will be placed in the table on the left, which is Table 1.

Fourth, create a partition scheme. The function of the partition scheme is to map the partition generated by the partition function to the filegroup. The function of the partition function is to tell SQL Server how to partition the data, while the function of the partition scheme is to tell SQL Server which filegroup to put the partitioned data in. The code for the partition scheme is as follows:

CREATE PARTITION SCHEME partschSale

AS PARTITION partfunSale

TO (

Sale2009

Sale2010

Sale2011

Sale2012

Sale2013)

Where:

1. CREATE PARTITION SCHEME means to create a partition scheme.

2. PartschSale is the name of the partition scheme.

3. AS PARTITION partfunSale indicates that the data partition condition (that is, the partition function used) used by the partition scheme is partfunSale.

4. The content after TO refers to the file group corresponding to the data divided by the partfunSale partition function.

At this point, the partition function and partition scheme are created.

Finally, create a partition table in a manner similar to that of a generic table, as follows:

CREATE TABLE Sale (

[Id] [int] IDENTITY (1) NOT NULL

[Name] [varchar] (16) NOT NULL

[SaleTime] [datetime] NOT NULL

) ON partschSale ([SaleTime])

Where:

1. CREATE TABLE means to create a data table.

2. Sale is the name of the data table.

3. () are the fields in the table, and the content here is no different from creating a normal data table. the only thing to note is that the clustered index can no longer be created. The reason is simple: clustered indexes can store records physically sequentially, while partitioned tables store data in separate tables, and these two concepts conflict, so clustered indexes can no longer be created when partitioned tables are created.

4. ON partschSale () explains the use of a partitioning scheme named partschSale.

5. The field used for the partition condition in partschSale () parentheses is SaleTime.

OK, a partition table that is physically separate and logically integrated, is created. Looking at the properties of the table, you can see that the table already belongs to the partitioned table.

The above is the introduction of the SQL Server partition table shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.