How to Implement LIMIT Functionality in SQL Server?

Restricting Result Sets in SQL Server Queries

SQL Server offers several methods to limit the number of rows returned by a query, mirroring the LIMIT clause's functionality in MySQL. Let's examine these techniques:

SQL Server 2005 and Later:

The ROW_NUMBER() function provides an elegant solution for versions 2005 and onward:

USE AdventureWorks;
GO
WITH NumberedOrders AS (
    SELECT SalesOrderID, OrderDate,
           ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNum
    FROM Sales.SalesOrderHeader
)
SELECT *
FROM NumberedOrders
WHERE RowNum BETWEEN 10 AND 20;

This assigns a unique row number to each row, enabling selection of a specific row range.

SQL Server 2000 and Earlier:

For older versions (2000 and below), a nested query with the TOP clause is necessary:

SELECT TOP 10 * 
FROM (SELECT TOP 20 * FROM Table ORDER BY Id) AS LimitedSet
ORDER BY Id DESC;

This nested query first retrieves the top 20 rows, ordered by Id, and then the outer query selects the top 10 from that subset, achieving the desired row limitation.

By employing ROW_NUMBER() (for newer versions) or the TOP clause with nested queries (for older versions), you can effectively manage the number of rows returned in your SQL Server queries, mimicking the behavior of the LIMIT clause.

The above is the detailed content of How to Implement LIMIT Functionality in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!