[SQL Server] Paging implementation

When performing some queries, if the returned result set is large, you may want to display the results by page. That is to say, you can specify the number of records displayed on each page and the page of records to be displayed.

Take the "person. Contact" table in the sample database adventureworks as an example. You want to display the name and name of the person in the table. If you execute the following statement:

Select Title, firstname, lastname
From Person. Contact

The first 19972 rows of records will be returned at a time, which is a headache for such a large result set ...... Fortunately, we can paging the results.

To implement paging, nested subqueries are required. The subquery is the SELECT statement above. However, we need to add a column to the subquery. The column is marked with numbers for each row. The row_number () function is used here:

Select Row_number () Over ( Order   By Contactid) As Rownum, title, firstname, lastname
From Person. Contact

With sequential and unique numbers, you can perform paging in an external query. However, before that, two variables are required to indicate the number of rows and page numbers displayed on each page. Add a where clause and top clause to the external query.

CompleteCodeLike this:

Declare   @ Rowsperpage   Int , @ Pageindex   Int

Set @ Rowsperpage = 10
Set @ Pageindex = 1 

Select Sub. Title, sub. firstname, sub. lastname
From
( Select Row_number () Over ( Order   By Contactid) As Rownum, title, firstname, lastname
From Person. Contact) As Sub
Where Sub. rownum Between ( @ Rowsperpage   * ( @ Pageindex   -   1 ) +   1 ) And ( @ Rowsperpage   *   @ Pageindex )

In this way, the paging function is implemented. You can change the values of @ rowsperpage and @ pageindex to see the effect.