In database management, efficient limiting of query results is crucial for optimizing performance and ensuring the retrieval of relevant data. Many SQL database systems, such as MySQL and PostgreSQL, utilize the LIMIT clause to specify the number of records a query returns. However, SQL Server doesn’t support the LIMIT clause, opting instead for alternatives like TOP, OFFSET-FETCH, and SET ROWCOUNT. This design choice reflects SQL Server’s focus on flexibility and performance, offering various methods to achieve similar functionality while catering to different use cases and scenarios.
Let’s take a closer look at the LIMIT alternatives in SQL Server, highlighting their unique features and limitations.
Contents
- Using the SELECT TOP clause
- Implementing pagination with OFFSET-FETCH
- Using the SET ROWCOUNT command
- Try it yourself with dbForge Studio
- Comparing the alternatives
- Further learning
Using the SELECT TOP clause
In SQL Server, the SELECT TOP clause acts as an alternative to the LIMIT clause. Likewise, it’s used to limit the number of rows returned by a query. It’s especially useful when you’re dealing with large datasets and want to retrieve only a subset of records. The basic syntax is:
SELECT TOP (number | percent) column_names
FROM table_name;Here, number stands for the exact number of rows to return, and percent is the percentage of rows to return from the total result set. Use one of these arguments depending on your needs.
You can refine the results further by adding other clauses—like WHERE or ORDER BY. If you’d like to know more about clauses used with SELECT, check out the post about the SQL SELECT statement.
For example, the following query returns the first five employees ordered by the date they were hired (here and below, we’ll be using the AdventureWorks2022 sample database in our examples):
USE AdventureWorks2022;
SELECT TOP 5 *
FROM HumanResources.Employee
ORDER BY HireDate;Or, this query retrieves national IDs and job titles of the top 10% of employees who have more than 20 hours of vacation:
USE AdventureWorks2022;
SELECT TOP 10 PERCENT NationalIDNumber, JobTitle
FROM HumanResources.Employee
WHERE VacationHours > 20;Using the SELECT TOP clause brings a number of advantages. First, performance optimization—it limits the size of the result set, reducing memory and processing load when only a portion of the data is needed. Next, it can be used to create efficient pagination for large result sets by retrieving only the rows required for the current page. Also, it’s helpful when testing queries on large tables by limiting the number of returned rows.
Note that SELECT TOP doesn’t provide random rows. To achieve randomness, you can combine it with ORDER BY NEWID(), but this can be inefficient for large datasets. On the other hand, without specifying an ORDER BY clause, the results can be unpredictable since SQL Server doesn’t guarantee the order of returned rows.
Implementing pagination with OFFSET-FETCH
Speaking of pagination, another clause—OFFSET-FETCH—can be used in SQL Server to implement pagination, allowing you to retrieve a specific subset of records by skipping a number of rows and then fetching a defined number of rows. This clause has the following syntax:
SELECT column_names
FROM table_name
ORDER BY column_name
OFFSET number_of_rows_to_skip ROWS
FETCH NEXT number_of_rows_to_return ROWS ONLY;The OFFSET clause lets you specify how many rows need to be skipped before returning rows, and FETCH NEXT defines how many rows to return after the skipped ones.
To illustrate, imagine you need to skip the first ten records sorted by BusinessEntityID and return the next ten, effectively fetching page 2 in a paginated result. Your query will look like this:
USE AdventureWorks2022;
SELECT *
FROM HumanResources.Employee
ORDER BY BusinessEntityID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;You can also use OFFSET-FETCH with a dynamic page size and page number. For example, this dynamic query fetches records for page 3, assuming a page size of ten rows per page:
USE AdventureWorks2022;
DECLARE @PageSize INT = 10;
DECLARE @PageNumber INT = 3;
SELECT BusinessEntityID, JobTitle, HireDate
FROM HumanResources.Employee
ORDER BY BusinessEntityID
OFFSET (@PageSize * (@PageNumber - 1)) ROWS
FETCH NEXT @PageSize ROWS ONLY;This combination of clauses is great because it gives you precise control over paging—you get a clean and efficient way to handle pagination, especially for web applications. Moreover, OFFSET-FETCH adheres to the SQL standard, making it portable and easy to understand for developers coming from other RDBMS. On top of that, unlike other methods (such as using ROW_NUMBER() with subqueries), OFFSET-FETCH directly skips and fetches rows without the need for complex workarounds.
It’s worth noting, though, that—for large datasets—the further you go in the pagination (say, page 1000), the slower the query might become because SQL Server has to skip more rows. Another point to consider is that OFFSET-FETCH doesn’t return the total number of rows, so if you need to display pagination metadata (like the total number of pages), an additional query—COUNT(*)—is required to fetch the total row count. And don’t forget that an ORDER BY clause is mandatory when using OFFSET-FETCH; otherwise, the results are unpredictable.
Using the SET ROWCOUNT command
You can use the SET ROWCOUNT command in SQL Server to limit the number of rows returned by a SELECT statement or affected by UPDATE or DELETE. The command’s syntax is as follows:
SET ROWCOUNT { number | 0 }Instead of number you specify the number of rows to return or process, and 0 resets the row count.
If you use SET ROWCOUNT and SELECT together with other commands such as ORDER BY and WHERE, their interaction can be quite powerful. In this combination, the WHERE clause filters the rows first, the ORDER BY clause sorts the filtered rows, and then SET ROWCOUNT limits the number of rows returned from the sorted result set.
Here’s an example where we want to retrieve only the top five records of alphabetically sorted (by their job title) employees with over 50 hours of vacation and then reset the row count limit so that future queries return all matching rows:
USE AdventureWorks2022;
SET ROWCOUNT 5;
SELECT *
FROM HumanResources.Employee
WHERE VacationHours > 50
ORDER BY JobTitle;
SET ROWCOUNT 0;SET ROWCOUNT with data modification commands such as UPDATE or DELETE works similarly. Let’s consider an example of updating data. Running this script will change the job title to Chief Stocker for only the first of the employees with the Stocker job title, then reset the row count limit, and display the result:
USE AdventureWorks2022;
SET ROWCOUNT 1;
UPDATE HumanResources.Employee
SET JobTitle = 'Chief Stocker'
WHERE JobTitle = 'Stocker';
SET ROWCOUNT 0;
-- To see the result of the update
SELECT *
FROM HumanResources.Employee
WHERE JobTitle LIKE ('%Stocker%')
ORDER BY JobTitle;As you can see, SET ROWCOUNT provides a simple method for limiting results without complex syntax. Unlike TOP, which sometimes requires subqueries for more complex logic, SET ROWCOUNT can be used directly with a SELECT statement. However, the SET ROWCOUNT command is considered deprecated for limiting result sets in favor of the TOP clause, which offers clearer semantics.
As a downside, the effect of SET ROWCOUNT is session-specific, meaning it must be set in each session where it’s needed. In addition, using SET ROWCOUNT in more complex queries can lead to unintended results if not carefully applied.
Note that SET ROWCOUNT is less and less commonly used today. SQL standards have evolved, and modern SQL practices favor more explicit control over result sets and data manipulation. So Microsoft recommends using the TOP clause instead, as SET ROWCOUNT won’t affect DELETE and UPDATE statements in future releases following the SQL Server 2022 version.
Try it yourself with dbForge Studio
We’ll try one of the above use cases in dbForge Studio for SQL Server.
Let’s first check how many employees in the HumanResources.Employee table of the AdventureWorks2022 database are holding the Marketing Specialist position. We do this as follows:
SELECT
COUNT(*) AS Count
FROM HumanResources.Employee
WHERE JobTitle = 'Marketing Specialist';As we can see, there are five of them:
We want to know three specialists who have the largest number of unused vacation hours. We use the following query:
SET ROWCOUNT 3;
SELECT
*
FROM HumanResources.Employee
WHERE JobTitle = 'Marketing Specialist'
ORDER BY VacationHours DESC;
SET ROWCOUNT 0;And dbForge Studio returns the result (we’ve changed the order of columns just for the purpose of this demonstration):
Why dbForge Studio for SQL Server? We believe that dbForge Studio—with its enhanced user interface and advanced features—outshines SQL Server Management Studio (SSMS). While SSMS revolves around the basics, dbForge Studio offers sophisticated tools like visual query building, database comparison and synchronization, data aggregation and analysis, automated unit testing, and integration with version control systems. This focus on user experience and powerful capabilities makes dbForge Studio a compelling alternative to SSMS for SQL Server management. To know more, watch this video: SSMS vs dbForge Studio for SQL Server – Features Comparison.
Got interested? You can try out dbForge Studio for SQL Server by downloading it for a free 30-day trial. The installation guide will help you get dbForge Studio running.
Comparing the alternatives
To sum up, let’s briefly compare the SQL Server alternatives to the LIMIT clause we’ve discussed above:
- SELECT TOP: Best for quick retrieval of a specified number of rows from a result set, especially when you need a fixed limit. Ideal for reports or dashboards where only the top N records are required.
- OFFSET-FETCH: Most appropriate for pagination in larger datasets. It allows you to skip a specified number of rows and return a defined set, making it great for displaying results across multiple pages.
- SET ROWCOUNT: Useful for limiting rows in older versions of SQL Server or for updating or deleting a specific number of records. However, its use is declining in favor of
TOPandOFFSET-FETCH.
So, each method has its unique strengths depending on the use case.
Further learning
We’d like to offer you the following resources to learn more about dbForge Studio for SQL Server, what it can do, and how to use it:
