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

This article mainly explains "how to modify the data in the partition table by SQL Server". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how SQL Server modifies the data in the partition table.

After you create a partitioned table, you can insert data directly into the partitioned table, regardless of which physical data table the data is placed in. Following the previous article, we inserted a few pieces of data into the created partition table:

As you can see from the above code, we have inserted a total of 13 pieces of data in the data table, of which pieces 1 to 3 are inserted into the first physical partition table, 4 and 5 data are inserted into the second physical partition table, 6 to 8 data are inserted into the third physical partition table, and 9 to 11 data are inserted into the 4 physical partition table Items 12 and 13 are inserted into the fifth physical partition table.

As can be seen from the SQL statement, the method of inserting data into a partitioned table is exactly the same as that of inserting data into a general table. For programmers, it is not necessary to care which data table these 13 records are placed in. Of course, when querying data, you can also ignore which physical data table the data is stored in. For example, use the following SQL statement to query:

Select * from Sale

The result of the query is shown below:

From the above two steps, there is no sense that the data is stored in several different physical tables, because logically, the data belongs to the same data table. If you don't want to know which record is placed in 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 is located.

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

$PARTITION. Partition function name (expression)

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

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

In the above statement, partfunSale () is the name of the partition function, and the expression in parentheses must be date-type data or data that can be implicitly converted to date-type data. If you want to ask me why, recall how to define a partition function (CREATE PARTITION FUNCTION partfunSale (datetime)). When you define the partfunSale () function, you specify that the parameter is date, so the expression in parentheses must be date or data that can be implicitly converted to date. The running result of the above code is shown in the following figure:

As you can see in the figure, the result returned by the partition function is 2, that is, the data for October 1, 2010 will be placed in the second physical partition table.

To take it a step further, if you want to know exactly which records are stored in each physical partition table, you can also use the $PARTITION function. Because the $PARTITION function can get the number of the physical partition table, you can simply use $PARTITION.partfunSale (SaleTime) as a condition for where, as shown in the following code:

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 running result of the above code is shown in the following figure:

From the figure above, we can see that the data records in each partition table are exactly the same as we set when we inserted them. Similarly, if you want to count the number of records in each physical partition table, you can use the following code:

Select $PARTITION.partfunSale (SaleTime) as partition number, count (id) as records from Sale group by $PARTITION.partfunSale (SaleTime)

The running result of the above code is shown in the following figure:

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

-Statistics of the total number of records in all partition tables

Select $PARTITION.partfunSale (SaleTime) as partition number, count (id) as records 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

-- recount the total number of records in all partition tables

Select $PARTITION.partfunSale (SaleTime) as partition number, count (id) as records from Sale group by $PARTITION.partfunSale (SaleTime)

In the above code, the programmer changes the time of one of the pieces of data, and you can see from the partition function that this record should be moved from the first partition table to the fifth partition table, as shown in the following figure. The programmer does not need to intervene in the whole operation process at all.

At this point, I believe you have a deeper understanding of "how SQL Server modifies the data in the partition table". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.