Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use TOP, OFFSET-FETCH, SET ROWCOUNT sentences". In daily operation, I believe many people have doubts about how to use TOP, OFFSET-FETCH and SET ROWCOUNT sentences. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use TOP, OFFSET-FETCH, SET ROWCOUNT sentences". Next, please follow the editor to study!

1. TOP usage

Syntax format: TOP (expression) [PERCENT] [WITH TIES]

Expression: a numeric expression that returns the number of rows

PERCENT: refers to the percentage of result set rows returned.

Usage:

-- extract 20 rows of records from UserInfo data table SELECT TOP 20 * FROM UserInfo;-- UserInfo data tables extract 10 rows of records SELECT TOP 10 PERCENT * FROM UserInfo

If you specify the number or percentage of rows returned in the expression, you must include the expression in parentheses. The usage is as follows:

DECLARE @ n AS int; SET @ n = 12 SELECT TOP (@ n) * FROM UserInfo

Note: when the ORDER BY clause is not specified, the data rows returned by the TOP clause are those that are accessed first in physical sequence, not necessarily the first few logical rows. To put it simply, the return result is uncertain. Even if the ORDER BY clause is specified, the specified sort column contains duplicate values and the result returned is uncertain. Uncertain data is of little value to data usage. To resolve the uncertainty of returning results when there are duplicate values in a column, you can use the WITH TIES keyword. This keyword specifies that all rows containing the last value returned by the ORDER BY clause will exceed the number specified by expression. Example:

SELECT TOP 2 WITH TIES FROM UserInfo ORDER BY CreateDate2, OFFSET-FETCH usage

SQL Server version 2012 introduces support for OFFSET-FETCH technology. OFFSET-FETCH filtering is generally regarded as part of the ORDER BY clause and is often used to achieve a sequential paging display. OFFSET specifies the number of rows to skip, and FETCH specifies the number of rows to filter after the number of rows skipped:

SELECT UserID, Birthday, Name,Age FROM UserInfo ORDER UserID OFFSET 20 ROWS FETCH NEXT 15 ROWS ONLY

Note: queries that use OFFSET-FETCH must have an ORDER BY clause. In addition, the FETCH clause must also have an OFFSET clause. If you don't want to skip any rows, but you want to use FETCH filtering, you can use OFFSET 0 ROWS to express it. However, using FETCH alone means skipping the specified number of rows and returning all remaining rows in the query result. There are some interesting language aspects to pay attention to in OFFSET-FETCH syntax. The singular format ROW and the plural format ROWS are interchangeable, so filtering can be described in an intuitive, English-like way. For example, suppose you only want to get a row, and if you specify FETCH 1 ROWS, although this is syntactically valid, it will look weird. Therefore, you can use the FETCH 1 ROW format. This interchange also applies to the OFFSET clause. In addition, if you don't want to skip any lines (OFFSET 0 ROWS), you may think "first" is more appropriate than "next", so the FIRST and NEXT formats are interchangeable. From the point of view of supporting skip function, OFFSET-FETCH clause is more flexible than TOP clause. However, OFFSET-FETCH does not support the PERCENT and WITH TIES options, while TOP does. Since OFFSET-FETCH is standard and TOP is not, it is recommended to use OFFSET-FETCH as the default choice, unless you need features supported by TOP and not supported by OFFSET-FETCH.

3. SET ROWCOUNT statement

The SET ROWCOUNT n statement limits the size of the result set, which specifies that the query stops processing after the specified n rows are returned. SET ROWCOUNT differs from TOP as follows:

The SET ROWCOUNT restriction applies to generating rows in the result set after calculating the ORDER BY. If you specify that the ORDER BY,SELECT statement will end after selecting n rows from a sorted value set.

TOP and OFFSET-FETCH apply to a single SELECT statement. The SET ROWCOUNT setting is in effect until the next SET ROWCOUNT statement is executed. If you execute SET ROWCOUNT 0, this option is turned off.

Note: the performance of TOP and OFFSET-FETCH is better than that of using SET ROWCOUNT, and SET ROWCOUNT should be avoided as far as possible. Usage:

SET ROWCOUNT 4; SELECT TOP 20 * FROM UserInfo; so far, the study on "how to use TOP, OFFSET-FETCH, SET ROWCOUNT statements" is over. I hope I can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.