Understanding and implementation of SQL partition table

If the data in one of your databases meets the following conditions, you should consider creating the partition table.

1, a table in the database a lot of data. What are many concepts, 10,000? 20,000, or 100,000 or 1 million? This, I think, is a question of the people of different opinions. Of course, the data in the data table more than the query when the data is obviously very slow, then you can consider using the partition table. If I have to say a number, I think it is 1 million.

2, however, the data is not the only way to create 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 the partition table, it might outweigh the gains. Only if your data is segmented, should you consider whether you need to use a partitioned table.

3, what is the data is segmented? This statement is very unprofessional, but it is very well understood. For example, your data is separated by years, for this year's data, you often do is to add, modify, delete and query, and for previous years of data, you almost do not need to operate, or your operations are often limited to queries, so congratulations, you can use the partition table. In other words, your operations on the data often involve only a subset of the data rather than all the data, so you can consider what partition tables are.

So, what is a partitioned table?

To put it simply, a partitioned table divides a large table into several small tables. Suppose you have a sales record that records the sales of each store, then you can divide the sales record into small tables, such as 5 small tables. Records prior to 2009 use a table, 2010 records use a table, 2011 Records use a table, 2012 records use a table, and records from 2012 use a table. Then, you want to query which year's record, you can go to the corresponding table query, because of the number of records in each sheet less, the query up time will naturally decrease.

However, the process of dividing a large table into several small tables can make programmers more difficult to program. To add records, for example, the 5 tables are independent of 5 tables, and when you add records at different times, programmers use different SQL statements, for example, when you add records in 2011, programmers add records to the 2011 table, and when you add records in 2012, The programmer will add the record to the 2012 list. In this way, the programmer's workload increases, and the likelihood of error increases.

The use of partitioned tables can be a good solution to the above problems. Partitioned tables can be physically divided into small tables from a large table, but logically, a large table.

Then the example above, the partition table can divide a sales record table into five physical small tables, but for programmers, he is still faced with a large table, whether it is to add a record in 2010 or add a record in 2012, for programmers do not need to consider, he simply inserts the record into the Sales record table- The big table in this logic is fine. SQL Server automatically places it in the physical table that it should be in.

Similarly, for the query, the programmer also only needs to set the query condition, Ok,sql server will automatically go to the corresponding table and table query, do not worry too much.

Isn't it all very tempting?

Indeed, then we can start to create the 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 the primary file directly. But I personally think that in order to facilitate management, or you can create a few filegroups, so you can put different small tables in different filegroups, both easy to understand and improve the speed of running. The way to create a filegroup is simple, open SQL Server Management Studio, locate the database where the partition table is located, right-click, and select Properties from the pop-up menu. Then select the Filegroup option, and then click the Add button below, as shown in the following illustration:

Second, after you create a filegroup, you create several more database files. Why do I want to create a database file, which is understandable, because the partitioned table must be placed on the hard disk, and where on the hard drive? Of course it's in the file. Besides, there are no files in the filegroup, so what's the use of the filegroup? In the image above, select the File option, and then add a few files. Be aware of the following points when adding files:

1, do not forget to put different files in the file group. Of course, a filegroup can also contain several different files.

2, if you can, put different files in different hard disk partitions, preferably on a separate hard drive. Knowing the speed of IQ is often one of the most important conditions that can affect the speed of SQL Server running. You can speed up SQL Server by placing different files on different hard disks.

In this case, all database files are placed under the same hard disk for convenience, and there is only one file in each filegroup. As shown in the following figure.

Third, create a partition function. This step is necessary, and the purpose of creating the partition function is to tell SQL Server how to partition the partitioned table. This step must have what SQL script to complete. In the example above, we want to divide the sales table into 5 small tables by time. The time of the assumption is divided into:

1th table: Data prior to 2010-1-1 (not including 2010-1-1).

2nd Small table: 2010-1-1 (inclusive 2010-1-1) to 2010-12-31 data.

3rd Small Table: 2011-1-1 (inclusive 2011-1-1) to 2011-12-31 data.

4th Small table: 2012-1-1 (inclusive 2012-1-1) to 2012-12-31 data.

5th small table: Data after 2013-1-1 (including 2013-1-1).

The code for the partitioning function looks like this:

[C-sharp] View plaincopy CREATE PARTITION FUNCTION partfunsale (datetime)

As RANGE right for VALUES (' 20100101 ', ' 20110101 ', ' 20120101 ', ' 20130101 ')

which

1, create PARTITION function meaning is the creation of a partition functions.

2, Partfunsale is the partition function name.

3, as range right to set the scope of the partition of the way to do, that is, the left way.

4, for values (' 20100101 ', ' 20110101 ', ' 20120101 ', ' 20130101 ') are partitioned by these values.

Here's what you need to explain, in values, ' 20100101 ', ' 20110101 ', ' 20120101 ', ' 20130101 ', these are the conditions for partitioning. "20100101" represents January 1, 2010, where records that are less than this value are divided into small tables, such as table 1, and values less than or equal to ' 20100101 ' and less than ' 20110101 ' are placed in another table, such as Table 2. By the end, all values of size or equal to ' 20130101 ' will be placed in another table, as shown in table 5.

One might ask why the value "20100101" would be placed in table 2, not in table 1. This is determined by the right in the as RANGE, which means that the data equal to this value is placed on the right-hand table, that is, tables 2. If you use left instead of right in your SQL statement, it will be placed in the table on the left-hand side, which is in table 1.

Four, create a partition scheme. The partition scheme's role is to map the partitions generated by the partition function to a filegroup. The role of partitioning functions is to tell SQL Server how to partition data, and the partitioning scheme is to tell SQL Server which filegroup to put the partitioned data in. The code for the partitioning scheme looks like this:

[C-sharp] View plaincopy CREATE PARTITION SCHEME Partschsale

As PARTITION Partfunsale

to (

Sale2009,

Sale2010,

Sale2011,

Sale2012,

Sale2013)

which

1, create PARTITION scheme means creating a partition scheme.

2, Partschsale for the partition scheme name.

3. As PARTITION Partfunsale describes the data partitioning conditions (that is, the partition function used) used by the partitioning scheme as Partfunsale.

4, to the following content refers to the Partfunsale partition function divided by the data corresponding to the file set.

So far, the partitioning function and the partitioning scheme are created. The created partition functions and partition schemes are visible in the database's storage, as shown in the following illustration:

Finally, you create a partitioned table, which is similar to creating a universal table, as follows:

[C-sharp] View plaincopy CREATE TABLE Sale (

[ID] [int] IDENTITY (1,1) not NULL,

[Name] [varchar] () not NULL,

[Saletime] [DateTime] Not NULL

) on Partschsale ([Saletime])

which

1, CREATE table means creating a data table.

2, sale for the data table name.

3, () in the table for the field, the content and create a normal data table is no different, the only thing to be aware of is no longer create a clustered index. The reason is simple, clustered indexes can store records in a physical order, and partitioned tables are stored in separate tables, and the two concepts are conflicting, so you can no longer create a clustered index when you create a partitioned table.

4, on Partschsale () describes the use of a partition scheme called Partschsale.

5, Partschsale () The field in parentheses for the partitioning condition is saletime.

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