SQL SERVER Paging method

Paging in SQL Server is required in recent projects, and paging query statements need to be written. I've written some statements about paged queries before, but the performance is not flattering. So during business hours, a foreigner wrote an article about SQL Server paging in the Microsoft community.  After seeing it, I feel that the statement I wrote earlier, too low-end, is too unscientific. The article describes two paging methods, one of which applies only to SQL SERVER2012 or later versions.

Row_number () function paging

First introduce the row_number () function, the main function of this function, from its name can be seen. Row, column, number, which is the function of assigning a number to each row. But it is generally not used alone. Its syntax is this:

PARTITION by value_expression: This parameter is divided into several areas by value_expression, which we query to the result set. For example, we have a list of scores. We have to rank the scores of girls and boys in their respective genders. At this point, we can divide the class into two districts, girls ' and boys ' districts through the Partittion by GENDER (gender field). Boys in the Boys district row position, girls in the female district ranking.

Order_by_clause: This is the order BY statement, which sorts the dataset by a field.

It should be noted that PARTITION by Value_expression is not a necessary parameter, but Order_by_clause is a necessary parameter. You must have an ORDER BY statement to use Row_number (). Here are a few row_number () actual usage scenarios:

1.

This statement sorts the data in the table Territoryname not empty, Salesyid not empty, and then assigns a sequential number to each row by the Row_number () function, assigning the number to a newly added field named row, in descending order of the field SalesYTD. The results are as follows:

2. This is the paging method that we are going to talk about today. (Returning a subset of rows)

In this scenario, we used the Comoon table expression (the intermediate table expressions), which is similar in function to the temporal table. is to put the results of the query into a place for querying again. Interested in common Table expression can go to MSDN to learn. Original address: Http://msdn.microsoft.com/en-us/library/ms175972.aspx.

We use the Row_number () function to assign a number to each row of data for the result of the query, placing the number in the RowNumber column (new columns generated through as). Then put the dataset into the intermediate table orderedorders. Query the intermediate table, this time we can use where RowNumber between A and B. To read the data from A to B, we can reach our paging needs.

3.Using row_number () with PARTITION

In this scenario, we use the partition parameter. We partition the results of the query by region (Territoryname), and then in the subregion's dataset, SalesYTD Descending, and then each row of data in descending order is assigned a number. Finally, the orderby sequence is Territoryname word. Results such as:

About Row_number () The original address: http://msdn.microsoft.com/en-us/library/ms186734.aspx.

The main idea of this paging method is to generate identities for each line through Row_number (). By between and statement to get the current page of data, has reached the role of paging.

We created a table with table name Tb.exmple and added 1,000,000 data. We use this paging method to write the following query statements:

-----View 10 records per page, data on page 2nd   DECLARE @PageNumber  as INT,@RowNumber  as INTSET @PageNumber=2SET @RowNumber=5SELETCT*  from (         SELECTRow_number () Over(ORDER  byId_exmple) as  Number,           *  fromtb_example) asTBLWHERE  Number between((@PageNumber-1)*@RowNumber+1) and(@PageNumber *@RowNumber)

The results are as follows:

2.OFFSET and FETCH Paging

Offset and fetch, which is a newly added feature of SQL SERVER 2012. 2012 The following versions are not supported.

Decaler@PageNumber  as INT,@RowNumber  as INTSET @PageNumber=2SET @RowNumber=5SELECT *  fromTb_exampleWHEREOFFSET ((@PageNumber-1)*@RowNumber) ROWSFETCH NEXT @RowNumberROWS only;

OFFSET a rows, place the previous A record, fetch NEXT b rows, and read the B data backwards.

The original address is as follows: Http://social.technet.microsoft.com/wiki/contents/articles/23811.paging-a-query-with-sql-server.aspx.

SQL SERVER Paging method