partition table in SQL Server 2005 (ii): How to add, query, and modify data in a partitioned table

 partition table in SQL Server 2005 (ii): How to add, query, and modify data in a partitioned tableCategory: SQL Server2009-12-03 15:02 13848 People read Comments (3) favorite reports SQL Serverinsert2010sqlclass

After you have created the partitioned table, you can insert data directly into the partitioned table instead of the physical data table in which the data is placed. In the next article, we insert several data into the created partition table:

As can be seen from the above code, we have inserted 13 data in the data table, where the 1th to 3rd data is inserted into the 1th Physical partition table, 4th, 5 data is inserted into the 2nd physical partition table, and the 6th to 8th data is inserted into the 3rd physical partition table The 9th to 11th data is inserted into the 4th Physical partition table, and the 12th and 13 data are inserted into the 5th Physical partition table.

As you can see from the SQL statement, the method of inserting data into a partitioned table and inserting data into a universal table is exactly the same, and for programmers there is no need to bother about which data table The 13 records study is placed on. Of course, when querying data, you can also ignore the fact that the data is stored in the Physical data table. Query using the following SQL statement:

[C-sharp]View Plaincopy

1. SELECT * FROM Sale

The results of the query are as follows:

From the above two steps, it is not felt that the data is stored separately in several different physical tables, because logically, the data belongs to the same data table. If you want to know which record is placed in the physical partition table, then you must use the $partition function, which can call the partition function and return the number of the physical partition where the data resides.

It's a little hard to understand, but it's easy to use. The $PARTITION syntax is:

$PARTITION. partition function name (expression)

Suppose you want to know which physical partition table the data of October 1, 2010 will be placed in, and you can use the following statement to view it.

[C-sharp]View Plaincopy

1. Select $PARTITION. Partfunsale (' 2010-10-1 ')

In the above statement, Partfunsale () is the partition function name, the expression in parentheses must be date data or can be implicitly converted to date data, if you want to ask me why, then think of how to define a partition function (CREATE PARTITION function Partfunsale (DateTime)). When you define the Partfunsale () function, the parameter is a date type, so the expression in parentheses must be a date type or data that can be implicitly converted to date. The result of the above code is as follows:

As you can see in this diagram, the partition function returns a result of 2, which means that the data for October 1, 2010 is placed in the 2nd physical partition table.

Further, you can use the $partition function if you want to know exactly what records are stored in each physical partition table. Because the $partition function can get the number of the physical partition table, it is only possible to use $partition.partfunsale (Saletime) as the Where condition, as shown in the following code:

[C-sharp]View Plaincopy

1. SELECT * from Sale where $PARTITION. Partfunsale (saletime) =1
2. SELECT * from Sale where $PARTITION. Partfunsale (saletime) =2
3. SELECT * from Sale where $PARTITION. Partfunsale (saletime) =3
4. SELECT * from Sale where $PARTITION. Partfunsale (saletime) =4
5. SELECT * from Sale where $PARTITION. Partfunsale (saletime) =5

The result of the above code is as follows:

From here we can see the data record in each partition table-exactly as we set it when we insert it. Similarly, if you want to count the number of records in each physical partition table, you can use the following code:

[C-sharp]View Plaincopy

1. Select $PARTITION. Partfunsale (saletime) as partition number, COUNT (ID) as record number from the Sale group by $PARTITION. Partfunsale ( Saletime)

The result of the above code is as follows:

In addition to the fact that the programmer does not need to consider the physical condition of the partitioned table when inserting data, it is not necessary to even modify the data. SQL Server automatically moves records from one partitioned table to another, as shown in the following code:

[C-sharp]View Plaincopy

1. --Count the total number of records in all partition tables
2. Select $PARTITION. Partfunsale (saletime) as partition number, COUNT (ID) as record number from the Sale group by $PARTITION. Partfunsale ( Saletime)
3. --Modify the record number 1 to change the time to January 1, 2019
4. Update Sale set saletime=' 2019-1-1 ' where id=1
5. --Re-count the total number of records in all partition tables
6. Select $PARTITION. Partfunsale (saletime) as partition number, COUNT (ID) as record number from the Sale group by $PARTITION. Partfunsale ( Saletime)

In the above code, the programmer changes the time of one of the data, and from the partition function it is known that the record should be moved from the first partition table to the fifth partition table, as shown in. And the whole process, programmers do not need to intervene at all.

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

partition table in SQL Server 2005 (ii): How to add, query, and modify data in a partitioned table