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

This article to share with you is about how to add queries and modify the data in the SQL Server partition table, Xiaobian think it is very practical, so share it with you to learn, I hope you can gain something after reading this article, not much to say, follow Xiaobian to see it.

start of text

After creating the partition table, you can insert data directly into the partition table, regardless of which physical data table the data resides in. We insert a few pieces of data into the created partition table:

From the above code, we can see that we have inserted a total of 13 pieces of data into the data table, of which the first to third pieces of data are inserted into the first physical partition table; the fourth and fifth pieces of data are inserted into the second physical partition table; the sixth to eighth pieces of data are inserted into the third physical partition table; the ninth to eleventh pieces of data are inserted into the fourth physical partition table; and the twelfth and thirteenth pieces of data are inserted into the fifth physical partition table.

From the SQL statement, you can see that the method of inserting data into a partitioned table is exactly the same as the method of inserting data into a universal table. For programmers, you don't need to pay attention to 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, query using the following SQL statement:

select * from Sale

The query results are shown below:

From the above two steps, there is no sense that the data is stored in several different physical tables, because logically, these data belong to the same data table. If you don't want to know which record is in which physical partition table, you must use the $PARTITION function, which calls the partition function and returns 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 in which physical partition table the data for October 1, 2010 will be placed, you can use the following statement to check.

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 can be implicitly converted to date type data, if you want to ask me why, then recall a partition function how to define it (CREATE PARTITION FUNCTION partfunSale (datetime)). When defining the partfunSale() function, you specify that the argument is of date type, so the expression in parentheses must be of date type or data that can be implicitly converted to date type. The result of running the above code is shown in the following figure:

As you can see in this figure, the partition function returns a result of 2, meaning that the data for October 1, 2010 will be placed in the second physical partition table.

Further, if you want to know exactly what 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, just use $PARTITION.partfunSale(SaleTime) as the condition of 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 result of running the above code is shown in the following figure:

From the image above we can see that the data records in each partition table are exactly the same as they were 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 Number of records from Sale group by $PARTITION.partfunSale(SaleTime)

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

Except that programmers don't have to think about the physics of partitioning tables when inserting data, they don't even have to think about modifying data. SQL Server automatically moves records from one partitioned table to another, as shown in the following code:

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

select $PARTITION.partfunSale(SaleTime) as Partition number,count(id) as Number of records from Sale group by $PARTITION.partfunSale(SaleTime)

--Modify record No. 1 to change the date 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 Number of records from Sale group by $PARTITION.partfunSale(SaleTime)

In the above code, the programmer changed the time of one of the data. From the partition function, we can know that this record should be moved from the first partition table to the fifth partition table, as shown in the following figure. The whole process of operation, the programmer is completely without intervention.

The above is how to add queries and modify data in SQL Server partition tables. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.