SQL pagination query method summary, SQL pagination Query Method

SQL pagination query method summary, SQL pagination Query Method

Requirement: query the table dbo. Message, with 10 entries per page and 2nd pages

1: TOP ()

SELECT TOP(20) * FROM dbo.Message WHERE Code NOT IN(SELECT TOP(10) Code FROM dbo.Message)

2: BETWEEN * AND *, Row_Number () OVER (order by *) AS rOwNum

SELECT *,ROW_NUMBER() OVER(ORDER BY Code) AS rowNum INTO #a FROM dbo.Message SELECT * FROM #a WHERE rowNum BETWEEN 11 AND 20DROP TABLE #a;

3: WITH * AS (), Row_Number () OVER (order by *) AS rowNum

WITH sss AS(SELECT *,ROW_NUMBER() OVER(ORDER BY CreateTime) AS rowNum FROM DBO.Message)SELECT * FROM sss WHERE rowNum BETWEEN 11 AND 20

4: SQL Server 2012 OFFSET-FETCH Filtering

SELECT * FROM DBO.Message ORDER BY CreateTime OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

Here, the methods in section 4th are described in detail:

The TOP option is a very practical filtering type, but it has two drawbacks-it is not a standard SQL and does not support skipping. The TOP filter defined by standard SQL is called OFFSET-FETCH and supports the Skip function, which is very useful for queries on specific pages. SQL Server2012 introduces support for OFFSET-FETCH filtering.

OFFSET-FETCH filtering in SQL Server 2012 is considered as part of the ORDER BY clause and is usually used for display in ORDER. The OFFSET clause specifies the number of rows to be skipped. The FETCH clause specifies the number of rows to be filtered after the number of rows to be skipped. Consider the following query example in the example.

SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate, orderidOFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

This query sorts the rows in the Orders table by orderdate and orderid (order date is from the farthest to the nearest, And the tiebreaker orderid is added. In this order, the OFFSET clause skips the first 50 rows, and the FETCH clause filters only the following 25 rows.

Note that the query using OFFSET-FETCH must have an order by clause. In addition, the FETCH clause does not support the absence of the OFFSET clause. If you do not want to skip any ROWS but want to use FETCH for filtering, you should use OFFSET 0 ROWS. However, the OFFSET without FETCH is allowed. In this case, the specified number of rows is skipped and all the remaining rows in the query result are returned.

The OFFSET-FETCH syntax has some interesting language aspects that need attention. The singular format ROW and the plural format ROWS can be exchanged, which enables you to describe filtering in an intuitive way similar to English. For example, suppose you only want to get a row. If you specify FETCH 1 ROWS, although this is effective in syntax, it may seem strange. Therefore, you can use the FETCH 1 ROW format. This interchange also applies to the OFFSET clause. In addition, if you do not want to skip any row (OFFSET 0 ROWS), you may think that "first" is more appropriate than "next", so the FIRST and NEXT formats can be exchanged.

As you can see, the OFFSET-FETCH clause is more flexible than the TOP clause. However, OFFSET-FETCH does not support the PERCENT and with ties options, but TOP does. Since OFFSET-FETCH is standard and TOP is not, we recommend that you use OFFSET-FETCH as your default choice unless you need TOP-supported features that are not supported by OFFSET-FETCH.

The above is a summary of the SQL paging query method introduced by the editor. I hope it will help you. If you have any questions, please leave a message. The editor will reply to you in time!