Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to achieve efficient paging in SQL Server. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

The meaning and grammatical definition of ROW_NUMBER

The ROW_NUMBER implementation numbers the output of the result set. Specifically, the sequence number of the row in the result set partition is returned, and the * row of each partition starts at 1.

ROW_NUMBER () OVER ([PARTITION BY field,] order_by_clause) PARTITION BY: divides the result set generated by the FROM clause into partitions to which the ROW_NUMBER function is applied. Value_expression specifies the column by which the result set is partitioned. If PARTITION BY is not specified, this function treats all rows of the query result set as a single group. Order_by_clause: clause determines the order in which * * ROW_NUMBER is allocated to rows in a particular partition. The order by clause is required. Return value: bigint. The sequence number of the row in the result set partition. The meaning and grammatical definition of offset

Offset is a clause of order by, which is mainly used to limit the number of rows returned, and it is also appropriate for paging. It is only supported from MSSQL2012. The grammatical structure is as follows:

FETCH {NEXT} {integer_constant | fetch_row_count_expression} {ROWS} ONLY

Fetch_row_count_expression can be a variable, parameter, or constant scalar quantum query. When using a subquery, it cannot reference any columns defined in the scope of the external query. That is, it cannot be associated with an external query.

Combined with paging, grammar:

Offset startPage rows fetch next pageSize rows only

The start page: startPage= (@ page-1) * @ rows, and the page size: pageSize=@rows

Demo data preparation

For illustration purposes, we prepare some demonstration data, which is a simple business sales table with only salesman, sales area and sales, as follows:

Declare @ sale table (FName nvarchar (50), FDistrict nvarchar (50), FAmount decimal (28) Insert into @ sale values (Zhang San, Beijing, 20000), (Zhang San, Shanghai, 50000), (Zhang San, Shenzhen, 40000), (Zhang San, Guangzhou, 30000), (Li Si, Beijing, 30000), (Li Si, Shanghai, 50000), (Li Si, Shenzhen, 40000), (Li Si, Guangzhou, 30000), (Wang er) Beijing, 70000), (Wang er, Shanghai, 10000), (Wang er, Shenzhen, 60000), (Wang er, Guangzhou, 20000), (Ma Liu, Beijing, 80000), (Ma Liu, Shanghai, 20000), (Ma Liu, Shenzhen, 70000), (Ma Liu, Guangzhou, 60000)

Prepare presentation data

Paging using ROW_NUMBER

For example, we hope that after sorting by salesman + sales region, every 4 records will be displayed on one page, written as follows:

Declare @ pagesize int = 4 as FRowIndex,* from-number of records per page declare @ pagenum int = 1-what page select v.* from (select row_number () over (order by FName,FDistrict) as FRowIndex,* from @ sale) as v where v.FRowIndex between @ pagesize* (@ pagenum-1) + 1 and @ pagenum*@pagesize

Paging query

The ROW_NUMBER function is a hot name in SQL (that is, the rigid name FRowIndex). It can only appear in the select clause and needs to be placed in a subquery. You can also define the subquery and then reference it directly. The syntax is as follows:

Declare @ pagesize int = 4sale-number of records per page declare @ pagenum int = 1-what page with saledata as (select row_number () over (order by FName,FDistrict) as FRowIndex,* from @ sale) select * from saledata where FRowIndex between @ pagesize* (@ pagenum-1) + 1 and @ pagenum*@pagesize

Paging query

ROW_NUMBER to do paging query, after repeated application testing, the efficiency is still very high. For a complete test script, see the following figure:

Using OFFSET to realize paging

The repeated part of the code is no longer repeated, and it should be noted when querying that offset is a clause of Order By and cannot exist independently. The grammatical structure is as follows:

Select * from @ sale order by FName,FDistrict offset (@ pagenum-1) * @ pagesize rows fetch next @ pagesize rows only

The result returned is the same as using row_number. For a complete test script, see the following figure:

The above is how to achieve efficient paging in the SQL Server shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to 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.