A brief discussion on SQL Server database paging

Database paging is a commonplace issue. If you use the ORM Framework and then use LINQ, a skip and take can be done. However, sometimes due to limitations, you need to use stored procedures to implement them. There are already many ways to implement paging using stored procedures in SQL Server. Before the interview encountered this problem, asked how to efficiently implement database paging. Just last week in the business also encountered this demand, so here simply record and share.

A demand

Here is a sample database for SQL Server, Northwind, with a product table, now assuming that our requirements are to be sorted in descending order of UnitPrice and 10 records per page. Requires service-side paging. The parameters are the number of records per page and the page number.

Two implementation top pagination

The most straightforward approach at the time was to use two top to achieve, the final result is the ascending, in the C # code to deal with it. Here as a demonstration, the statement is used * For convenience, the actual development to be replaced with a specific column name. The following method is simple.

Select Top (@pageSize)        *from    (select Top (@pageSize * @pageIndex)                    *          from      [northwind].[ DBO]. [Products]          ORDER by  UnitPrice DESC        

But this code is problematic, do not know that you have found no. When the qualifying set of records is less than the number of records per page, there is no problem, but when the greater than the problem, for example, in the instance database in the products there are 77 records, when 10 records per page, the 8th page should return only 7 records, the 9th page should return empty, but using the method above, 10 records are returned each time.

Follow the above ideas, modify the code in order to use three layer Select, the first layer to query all the records before the data, and then select the second layer of the top pagesize all the data in the first layer, because it does not have a bug

SELECT  *from    dbo. Productswhere   ProductID in (The        SELECT TOP (@pageSize)                ProductID from    dbo. Products        WHERE   ProductID isn't in (the SELECT TOP (@pageSize * (@pageIndex-1))                                            ProductID from     dbo. Products                                   ORDER by UnitPrice DESC)        ORDER BY dbo. Products.unitprice DESC) ORDER by dbo. Products.unitprice ASC

Paging using the Row_number function

In fact, there is one of the simplest and most straightforward way of thinking is to use a temporary table, that is, create a table variable in memory, the variable contains a self-increment column, the table key column, and then the table to be sorted by the sorting criteria and rules inserted into the table, and then you can use the self-increment as the row number, In earlier, such as SQL Server 2000, can only do this, but for the large data volume of the recordset, the need to create a temporary table is also relatively large, inefficient, not described here.

The Row_number () function is introduced in SQLServer2005 to generate a record ordinal according to a given good sort field rule, with the following basic usage:

SELECT  row_number () over (ORDER by dbo. Products.ProductID DESC) as rownum,        *from    dbo. Products

In this way, the first column in the result set is rownum and increments from 1 to 1, which is somewhat similar to the self-incrementing field starting with 1 Step 1. It should be mentioned here that the RowNum column in this statement cannot be used in the current where statement, nor can the entire row_number () statement be placed in the where as a condition, and the following two ways of using it are all wrong.

SELECT  row_number () over (ORDER by dbo. Products.ProductID DESC) as rownum,        *from    dbo. Products WHERE rownum between 1 and 10

will prompt for error:

Invalid column name ' RowNum '.

SELECT  row_number () over (ORDER by dbo. Products.ProductID DESC) as rownum,        *from    dbo. Products WHERE (Row_number ()-Over (ORDER by city) as Rown) between 1 and 10

will prompt for error:

Incorrect syntax near the keyword ' as '.

The correct approach is to use the results of the query as an internal query, and then set up an outside query statement:

Select  *from (    select    row_number () over (ORDER by dbo. Products.ProductID DESC) as RowNum,                    *          from      dbo. Products        ) as Tempwhere   temp.rownum between 1 and 10

With these basics in place, we can use the Row_number feature to sort it out.

Select  *from (    Select TOP (@pageSize * @pageIndex)                    row_number () over (ORDER by dbo. Products.unitprice DESC) as RowNum,                    *          from      dbo. Products        ) as Tempwhere   temp.rownum > (@pageSize * (@pageIndex-1)) ORDER by temp. UnitPrice

The strategy is simple, we first select the data that contains the page to be looked up, then use the Row_numer function to number, and then specify the rownum in the outer query to be greater than the page start record. This is a quick and easy way.

There is also a way to use the CTE (common_table_expression, common table expression, not CTE level 46 Oh, I first contact This is the interview when asked how to use SQL to write recursion, hehe), the use is very simple, is to put internal query in the CTE inside, As follows:

With    productentity as          (the SELECT TOP (@pageSize * @pageIndex)                        row_number () over (ORDER by dbo. Products.unitprice DESC) as RowNum,                        *               from     dbo. Products             ) SELECT  *from    productentitywhere   

This performance is similar to the above. In some cases, however, using a CTE is more efficient than directly taking an external query. For example, we can use only the CTE to store line numbers, keywords, and sort fields, and then use the join query with the original table, as follows:

With    productentity as          (the SELECT TOP (@pageSize * @pageIndex)                        row_number () over (ORDER by dbo. Products.unitprice DESC) as rownum,                        ProductID,--primary key,                        unitprice--to sort fields               from     dbo. Products             ) SELECT  *from    productentity        INNER JOIN dbo. Products on dbo. Products.ProductID = Productentity.productidwhere   productentity.rownum > (@pageSize * (@pageIndex-1)) ORDER B Y Productentity.unitprice

Paging using Row_number is a very wide-spread approach, with the take and skip statements that can be used in LINQ at the beginning of this article, but only with SQL statements for interacting with the database, and LINQ internally translates us into appropriate SQL statements. In fact, the statement is also using the Row_number function, in order to demonstrate, we create a new console program, and then add a LINQ to SQL class, the use of the method is very simple, as follows:

list<product> product;int pageSize = 10;int PageIndex = 8;using (Productsdatacontext context = new Productsdataconte XT ()) {    Product = context. products.orderbydescending (x = x.unitprice)//Sort                                . Skip (PageSize * (pageIndex-1))//Skip the previous record                                . Take (pageSize)//Select the number of each page                                . ToList ();}

A few sentences to achieve the pagination.

We know that LINQ is actually converting the C # expression tree to the SQL language, and through the SQL Server Profile tool, we can see the request that the program sends to SQL Server, as follows:

I copy the following statement and I can see

EXEC sp_executesql N ' SELECT [T1]. [ProductID], [T1]. [ProductName], [T1]. [SupplierID], [T1]. [CategoryID], [T1]. [QuantityPerUnit], [T1]. [UnitPrice], [T1]. [UnitsInStock], [T1]. [UnitsOnOrder], [T1]. [ReorderLevel], [T1]. [Discontinued] From (    SELECT row_number () through (ORDER by [t0].[ UnitPrice] DESC) as [Row_number], [t0]. [ProductID], [t0]. [ProductName], [t0]. [SupplierID], [t0]. [CategoryID], [t0]. [QuantityPerUnit], [t0]. [UnitPrice], [t0]. [UnitsInStock], [t0]. [UnitsOnOrder], [t0]. [ReorderLevel], [t0]. [Discontinued]    from [dbo]. [Products] As [t0]    ) as [t1]where [T1]. [Row_number] Between @p0 + 1 and @p0 + @p1ORDER by [T1]. [Row_number] ', N ' @p0 int, @p1 int ', @p0 = n, @p1 = 10

This is exactly what we used to write the Row_number-based paging program. It can be seen that the simple word skip and take,linq in the back to help us do a lot of work.

Paging using the offset FETCH clause

Since LINQ is so simple about paging, there are no similar simple statements in SQL Server that can handle paging, and the answer is, that is, the offset FETCH clause introduced in SQL Server Compact 4.0.

SELECT  * from    dbo. Products ORDER by   UnitPrice DESC OFFSET  (@pageSize * (@pageIndex-1)) rows FETCH NEXT @pageSize rows only;

is not like LINQ, Offseet equivalent to Skip,fetch next equivalent to take.

SQL Server CE 4.0 can be downloaded on the official web and currently supports only SQL Server 2012 and SQL Server 2014, but you can use this new feature with the Microsoft WebMatrix tool.

Comparison

Before we discuss performance, we need to make it clear that when we write SQL statements, we try to reduce the output of unnecessary fields, which are used for demonstration purposes, so we don't do this in practice. There is to be based on business logic, such as query conditions, to establish a suitable aggregate index and non-aggregated index, the index for the efficiency of the search is very large, the index in SQL is actually to establish some kind of balance search tree, such as B-tree to do, this knowledge can look at my previous written algorithms in the article, and then there is to understand Some of the features of the Server, such as the difference between cte,in and exist, and so on, are small places that may have some impact on performance.

After all of this has been done, let's talk about the paging scheme better.

• The use of the Top–not in-top scheme is complex, containing in statements, inefficient, but compatible with a version of SQL Server.
• Using the Row_number method to achieve the ease of paging is moderate, high efficiency. Skip and take in LINQ are also paged in this way, and should be the more extensive paging method currently in use.
• The Ffset FETCH method was introduced in SQL Server CE 4.0 because this article does not have SQL Server 2012 and the test data. From the Comparing-performance-for-different-sql-server-paging-methods article and the SQL Server 2012 paging method analysis in the garden (offset and fetch), The performance should be better.

The above is a summary of SQL Server database, I hope to help you.