How Can I Limit the Number of Records Retrieved in SQL Server Without Using LIMIT?

Record limit in SQL Server: LIMIT exceeded

SQL Server does not support the LIMIT function or OFFSET operator commonly used in other database systems. This can be a challenge when limiting the number of records retrieved.

However, SQL Server provides an alternative way to achieve similar functionality. One way is to use the ROW_NUMBER() function with a WHERE clause:

<code class="language-sql">SELECT * 
FROM ( 
  SELECT *, ROW_NUMBER() OVER (ORDER BY name) AS row 
  FROM sys.databases 
) a 
WHERE row > 5 AND row <= 10</code>

This subquery assigns each row a row number and then filters the results to include only rows within the specified range.

Another way is to use the TOP clause:

<code class="language-sql">SELECT TOP 10 * FROM stuff</code>

While TOP limits the number of rows returned, it does not provide control over the starting position.

Why doesn’t SQL Server have LIMIT?

The lack of LIMIT function in SQL Server may be due to performance considerations. Using LIMIT may cause performance degradation, especially on large tables. SQL Server prioritizes optimizing query execution time.

Use OFFSET and ROWS

For SQL Server 2012 and later, you can use the OFFSET and ROWS keywords to have more granular control over the result set:

<code class="language-sql">SELECT  *
FROM     sys.databases
ORDER BY name 
OFFSET  5 ROWS 
FETCH NEXT 5 ROWS ONLY</code>

This query skips the first 5 rows and retrieves the next 5 rows, providing limiting capabilities comparable to LIMIT.

The above is the detailed content of How Can I Limit the Number of Records Retrieved in SQL Server Without Using LIMIT?. For more information, please follow other related articles on the PHP Chinese website!