partition table in SQL Server 2005 (a): What is a partitioned table? Why use partitioned tables? How do I create a partitioned table? Go

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

1. There is a lot of data in a table in the database. What are many concepts? 10,000 lines? 20,000 lines? Or 100,000 or 1 million? This, I think is the question of the benevolent see and the beholder. Of course, when the data in the data table is more than the query, you can obviously feel that the data is very slow, then you may consider using the partition table. If I have to say a value, 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, it is best not to use the partition table, it might not be worth the candle. Only if your data is segmented, consider whether you need to use a partitioned table.

3, what is the data is segmented? This statement, though unprofessional, is 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 no need to operate, or your actions are often limited to queries, then congratulations, you can use the partition table. In other words, you tend to work with data that involves only a subset of the data, not all of the data, so you can consider what the partitioning table is.

So, what is a partitioned table?

To put it simply, a partitioned table is one that divides a large table into a number of small tables. Assuming that you have a sales record sheet that records the sales of each store, you can divide the sales record into a few smaller tables, such as 5 small ones. Records from 2009 Use a table, 2010 records use a table, 2011 Records use a table, 2012 records use a table, and records in 2012 use a table. Then, you want to query which year's record, you can go to the corresponding table query, because the number of records in each table is less, the query time will naturally be reduced.

But dividing a large table into a few small tables can make programming more difficult for programmers. To add records as an example, the above 5 tables are separate 5 tables, when adding records at different times, the programmer to use different SQL statements, such as when adding records in 2011, the programmer to add records to the 2011 table, in 2012 to add records, The programmer wants to add the record to the 2012 table. In this way, the programmer's workload increases and the likelihood of errors increases.

The use of partitioned tables can be a good solution to the above problems. A partitioned table can physically divide a large table into smaller tables, but logically, it is a large table.

Then the above example, the partition table can divide a sales record table into five physical small tables, but for the programmer, he is still facing a large table, whether it is to add records in 2010 or 2012 to add records, for the programmer is no need to consider, he just insert records into the Sales record table- The big table in this logic is on the line. SQL Server automatically places it in the physical table that it should stay in.

Similarly, for the query, programmers only need to set up the query criteria, Ok,sql server will automatically go to the corresponding table query, do not care too much.

Is all this tempting?

Yes, then we can start creating a partitioned 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, you can create several filegroups, so that different small tables can be placed in different filegroups, both easy to understand and improve the speed of operation. The way to create a filegroup is simple, open SQL Server Management Studio, locate the database where the partition table resides, right-click, and select Properties from the popup menu. Then select the Filegroup option, and then click the Add button below, as shown in:

Second, after you create a filegroup, you create several more database files. Why create a database file, this is very understandable, because the partition of the small table must be placed on the hard disk, and put on the hard disk where? Of course it's in the papers. Besides, there are no files in the filegroup, what's the use of filegroups? Or in that interface, select the "File" option, and then add a few files. Note the following points when adding a file:

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

2. If possible, put different files in different hard disk partitions, preferably on separate hard drives. Knowing the speed of IQ is often one of the important conditions that affect the speed of SQL Server operations. By placing different files on different hard disks, you can speed up the SQL Server run.

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

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 be done by SQL script. In the example above, we want to divide the sales table by time into 5 small tables. Suppose the time is divided:

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

2nd Small table: Data from 2010-1-1 (including 2010-1-1) to 2010-12-31.

3rd Small table: Data from 2011-1-1 (including 2011-1-1) to 2011-12-31.

4th Small table: Data from 2012-1-1 (including 2012-1-1) to 2012-12-31.

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

Then the code for the partition function is as follows:

[C-sharp]View Plaincopy

1. CREATE PARTITION FUNCTION Partfunsale (datetime)
2. As RANGE right for VALUES (' 20100101 ',' 20110101 ',' 20120101 ', ' 20130101 ' )  

which

1. Create PARTITION functions means creating a partition function.

2, Partfunsale is the partition function name.

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

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

Here you need to explain, in values, ' 20100101 ', ' 20110101 ', ' 20120101 ', ' 20130101 ', these are the conditions of the partition. "20100101" for the January 1, 2010, the record less than this value will be divided into a small table, such as Table 1, and less than or equal to ' 20100101 ' and less than ' 20110101 ' value, will be placed in another table, such as Table 2. And so on, in the end, all values of size or equal to ' 20130101 ' are placed in another table, as in table 5.

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

IV. Create a partitioning scheme. The purpose of a partitioning scheme is to map partitions generated by partition functions to filegroups. The purpose of the partitioning function is to tell SQL Server how to partition the data, and the partitioning scheme is to tell SQL Server which filegroup the partitioned data is placed in. The code for the partitioning scheme is as follows:

[C-sharp]View Plaincopy

1. CREATE PARTITION SCHEME Partschsale
2. As PARTITION Partfunsale
3. to (
4. Sale2009,
5. Sale2010,
6. Sale2011,
7. Sale2012,
8. Sale2013)

which

1. Create PARTITION scheme means creating a partition scheme.

2, Partschsale is the partition scheme name.

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

4, to the back of the content refers to the Partfunsale partition function is divided into the data corresponding to the filegroup stored.

So far, the partitioning function and partitioning scheme have been created. The created partition function and partition scheme can be seen in the storage of the database as shown in:

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

[C-sharp]View Plaincopy

1. CREATE TABLE Sale (
2. [Id] [int] IDENTITY (*) not NULL,
3. [Name] [varchar] (+) Not NULL,
4. [Saletime] [DateTime] Not NULL
5. ) on Partschsale ([Saletime])

which

1. Create table means creating a data table.

2, sale is the data table name.

3, () is the field in the table, there is no difference between the content and the creation of a normal data table, the only thing to note is that you can no longer create a clustered index. The simple truth is that clustered indexes can store records in a physical order, whereas partitioned tables store data in separate tables, which are conflicting, so you can't create a clustered index when you create a partitioned table.

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

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

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

Original is not easy, reproduced please indicate the source. Http://blog.csdn.net/smallfools/archive/2009/12/03/4930810.aspx