OFFSET FETCH Next Knowledge point integration

Source: Internet
Author: User

Part1:

This statement belongs to the Transact-SQL language (the applicable database language such as SQL Server)

offset= offset, skipping

FETCH = Fetch

Alternatively, offset can be used alone, as below, skipping the top 100,000:

SELECT Shopname from shop  ORDER by Shopname   OFFSET 100000 ROW

Pretend to have a table shop, which has a list of shopname, taking 100000 to 100,050 data.

The writing of Row_number

SELECT * from  (
SELECT Shopname, Row_number () over (ORDER by Shopname) as R from shop
) T WHERE r>100000 and r<=100050

The writing of Offset,fetch

SELECT shopname from Shop  ORDER by Shopname   OFFSET 100000 row  FETCH NEXT

(This section was excerpted from http://www.ithao123.cn/content-4319840.html)

Part2:

Caveats: The number of rows returned using offset and FETCH restrictions

We recommend that you use the OFFSET and FETCH clauses instead of the TOP clause to implement a query paging solution and limit the number of rows that are sent to the client application. If you use OFFSET and fetch as a paging solution, you need to run the query once for each "page" of data that is returned to the client application. For example, to return query results in increments of 10 behavior, you must execute a query to return 1-10 rows, run the query again to return 11-20 rows, and so on. Each query is independent and is not associated with other queries in any way. This means that the client application is responsible for tracking the state, unlike cursors that use a single query and hold the state on the server.      To use offset and FETCH to obtain stable results between query requests, the following conditions must be met: 1. The underlying data used by the query cannot be changed. That is, the rows processed by the query are not updated, and all page requests in the query are not executed using snapshots or serializable transaction isolation in a single transaction. For more information about these transaction isolation levels, see SET Transactionisolation level (Transact-SQL): http://msdn.microsoft.com/zh-cn/library/ Ms173763.aspx2. The ORDER by clause contains a column or combination of columns that are guaranteed to be unique. More information: http://msdn.microsoft.com/zh-cn/library/ms188385 (v=sql.110). aspx

OFFSET FETCH Next Knowledge point integration

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.