SQL Add, modify, query the data in the partition table

After you create the partition table, you can insert the data directly into the partition table without having to worry about which physical data table the data is placed in. After the article, we insert several data into the created partition table:

"Rows=" name= "code" class= "C-sharp" >insert Sale ([name],[saletime]) VALUES (' John ', ' 2009-1-1 ')

Insert Sale ([name],[saletime]) VALUES (' Dick ', ' 2009-2-1 ')

Insert Sale ([name],[saletime]) VALUES (' Harry ', ' 2009-3-1 ')

Insert Sale ([name],[saletime]) VALUES (' Money six ', ' 2010-4-1 ')

Insert Sale ([name],[saletime]) values (' Zhao Qi ', ' 2010-5-1 ')

Insert Sale ([name],[saletime]) VALUES (' John ', ' 2011-6-1 ')

Insert Sale ([name],[saletime]) VALUES (' Dick ', ' 2011-7-1 ')

Insert Sale ([name],[saletime]) VALUES (' Harry ', ' 2011-8-1 ')

Insert Sale ([name],[saletime]) VALUES (' Money six ', ' 2012-9-1 ')

Insert Sale ([name],[saletime]) values (' Zhao Qi ', ' 2012-10-1 ')

Insert Sale ([name],[saletime]) VALUES (' John ', ' 2012-11-1 ')

Insert Sale ([name],[saletime]) VALUES (' Dick ', ' 2013-12-1 ')

Insert Sale ([name],[saletime]) VALUES (' Harry ', ' 2014-12-1 ')

As you can see from the code above, we have inserted a total of 13 data in the datasheet, 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, the 6th to 8th bar data is inserted into the 3rd physical partition table; The 9th to 11th data is inserted into the 4th Physical partition table, and 12th, 13 data is inserted into the 5th Physical partition table.

As you can see from the SQL statements, the method of inserting data into a partitioned table is exactly the same as inserting data in a universal table, and for programmers, it is not necessary to ignore the data tables in which the 13 records are studied. Of course, when querying data, you can also ignore the data in which physical data tables are stored. If you use the following SQL statement to query:

[C-sharp] View plaincopy select * from Sale

The results of the query are shown in the following illustration:

From the previous two steps, it was not felt that the data was stored separately in several different physical tables, because logically, the data belonged to the same datasheet. If you want to know which record is on which physical partition table, 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 syntax for $PARTITION 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 select $PARTITION. Partfunsale (' 2010-10-1 ')

In the above statement, Partfunsale () is the partition function name, the expression in parentheses must be date-type data or data that can be implicitly converted to date, and if you ask me why, then think about how to define a partition function (CREATE PARTITION function Partfunsale (DateTime)). When defining the Partfunsale () function, the parameter is specified as a date type, so the expression in parentheses must be of date type or data that can be implicitly converted to date type. The results of the above code are shown in the following illustration:

As you can see in the 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.

For further consideration, you can also 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, use the $partition.partfunsale (Saletime) as a where condition, as shown in the following code:

[C-sharp] View plaincopy select * from Sale where $PARTITION. Partfunsale (saletime) =1

SELECT * from Sale where $PARTITION. Partfunsale (saletime) =2

SELECT * from Sale where $PARTITION. Partfunsale (saletime) =3

SELECT * from Sale where $PARTITION. Partfunsale (saletime) =4

SELECT * from Sale where $PARTITION. Partfunsale (saletime) =5

The results of the above code are shown in the following illustration:

From the image above we can see the data logging in each partition table-exactly the same as we did when we inserted 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 select $PARTITION. Partfunsale (saletime) as partition number, COUNT (ID) as record number from Sale GROUP by $PARTITION. PA Rtfunsale (Saletime)

The results of the above code are shown in the following illustration:

In addition to the fact that programmers do not need to consider the physical situation of partitioned tables when inserting data, they do not even need to consider modifying the data. SQL Server automatically moves records from one partitioned table to another, as shown in the following code:

[C-sharp] View Plaincopy--count the total number of records in all partitioned tables

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

--Modify the record number 1 to change the time to January 1, 2019

Update Sale set saletime= ' 2019-1-1 ' where id=1

--Re-count the total number of records in all partitioned tables

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

In the code above, the programmer changes the time of one of the data, and from the partition function, it should be moved from the first partition table to the fifth partition table, as shown in the following illustration. And the whole process, the programmer is absolutely no need to intervene.