How to Limit Rows Returned by a SQL Query in SQL Server?

LIMIT Clause in SQL Server: Implementation and Alternatives

Many developers are accustomed to using the LIMIT clause commonly used in MySQL and other database management systems to limit the number of rows returned by a SQL query. However, SQL Server does not natively support the LIMIT clause. This article explores alternative ways to achieve the same functionality in SQL Server.

Method 1: ROW_NUMBER() function for SQL Server 2005 and above versions

For SQL Server 2005 and above, you can use the ROW_NUMBER() function to simulate the LIMIT clause. The following example demonstrates this approach:

<code class="language-sql">USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 10 AND 20;</code>

This query returns rows with a SalesOrderID between 10 and 20, effectively limiting the result set.

Method 2: Nested query for SQL Server 2000 and below

For SQL Server 2000 and below, you can use the nested query method:

<code class="language-sql">SELECT TOP 10 * FROM (SELECT TOP 20 * FROM Table ORDER BY Id) ORDER BY Id DESC</code>

This query retrieves the first 10 rows from an inner subquery that returns the first 20 rows sorted by Id. By reversing the sort order in the outer subquery, the final result is the top 10 rows in ascending order by Id.

The above is the detailed content of How to Limit Rows Returned by a SQL Query in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!