While executing SELECT queries on tables containing huge records, developers often need to limit the number of records being fetched out to avoid performance issues or just analyze a subset of records for testing purposes. In this article, we can go through the various options provided by SQL Server to limit the number of records fetched by a SELECT query or other DML statements.
TOP
The TOP clause is the most commonly used option to limit the no. of records returned in a result set. For DML statements like INSERT, UPDATE, DELETE and MERGE, using the TOP clause limits the no. of rows affected by this operation. Syntax of TOP Clause is as follows:
TOP (expression) [PERCENT] [ WITH TIES ]In this syntax:
- TOP is the keyword to limit the no. of records based upon the expression (a numeric value) or else based upon a Percentage value.
- Expression value can also derive its value from a variable as well.
- WITH TIES option refers to fetching more records based upon the record value matching the last value. We can see the WITH TIES option in more details with an example.
In the AdventureWorks database, we can SELECT TOP 10 records from the Person.Person table using the below query.
SELECT TOP 10 FirstName, LastName
FROM Person.Person
OR
SELECT TOP (10) FirstName, LastName
FROM Person.Person
OR
declare @top_value int = 10;
SELECT TOP (@top_value) FirstName, LastName
FROM Person.Person
The usage of () brackets while specifying the integer values for the TOP clause is optional, so I use the numeric value without () brackets throughout this article, however () brackets are mandatory for DML operations.
Executing the above query will return the below result set.
As seen above, using TOP 10 doesn’t guarantee that the records are in a specific order and SQL Server just fetches 10 records randomly. To fetch the TOP 10 records in a specific order for example based upon FirstName, we need to use ORDER BY as shown below.
SELECT TOP 10 FirstName, LastName
FROM Person.Person
ORDER BY FirstName
TOP WITH TIES Option
The WITH TIES option will fetch more records if the last record fetched as a part of using the TOP statement has tied values. In the previous result set shown above, we can see a lot of records with Aaron as FirstName but fetched only 10 records by using the TOP 10 clause. Let’s use the WITH TIES option to see how many records are fetched out.
SELECT TOP 10 WITH TIES FirstName, LastName
FROM Person.Person
ORDER BY FirstName
Using WITH TIES option will fetch all records with Aaron as FirstName and the values before Aaron resulting in 59 records even though we have specified that we need to fetch only TOP 10 records. That clearly explains that using the WITH TIES option, we can expect additional records based upon the ORDER BY fields specified out. While using the WITH TIES option, the ORDER BY clause is mandatory and if we are using the WITH TIES option in the SELECT statement without the ORDER BY clause, SQL Server will show the below error.
TOP PERCENT Clause
Instead of specifying the numeric value in the TOP clause, we can use the PERCENT option to fetch the no. of records based upon a Percentage value as shown below.
SELECT TOP 0.01 PERCENT FirstName, LastName
FROM Person.Person
ORDER BY FirstName
While using the PERCENT option, we can use any numeric value between 0 and 100 with decimal values as well as shown in the above script with a value of 0.01 PERCENT. However, without using the PERCENT option, the TOP clause accepts only integer values and will return an error stating that only integer values are expected in the TOP clause as shown below.
So far, we have understood how to filter the number of records returned in the SELECT statement and we can now verify how the TOP clause works on DML statements.
Limit Records in the INSERT Statement
To limit the number of records affected by an INSERT statement, we can use the below query.
CREATE TABLE [Person].[Person_test](
[PersonType] [nchar](2) NOT NULL,
[FirstName] [dbo].[Name] NOT NULL,
[MiddleName] [dbo].[Name] NULL,
[LastName] [dbo].[Name] NOT NULL
)
GO
INSERT TOP (10) INTO Person.Person_test
SELECT PersonType, FirstName, MiddleName, LastName
FROM Person.Person
As we can see, the TOP (10) clause here requires the braces () mandatorily while using along with the INSERT clause.
Limit Records in the UPDATE Statement
Similar to the INSERT statement, we can limit the no. of rows affected by the UPDATE statement, and for testing purposes, I’ll INSERT a few more sample records to the Person.Person_test table, add a new column named ModifiedDate and the table has around 1030 records inside it as shown below.
ALTER TABLE Person.Person_test ADD ModifiedDate Datetime;
Let’s try updating the ModifiedDate column using the below script
UPDATE TOP(10) Person.Person_test
SET ModifiedDate = GETDATE()
WHERE ModifiedDate IS NULL
Using TOP (10) clause updated only 10 records based upon random order as ORDER BY clause can’t be used in UPDATE statement. If we need to UPDATE based upon the ORDER BY clause, we need to do it inside a Subquery or using temporary tables with sorted results inside.
Limit Records in the DELETE Statement
The TOP clause can also be used in DELETE statements to limit the number of records deleted:
DELETE TOP (10)
FROM Person.Person_test;
Similar to the UPDATE statement, we can’t use the ORDER BY clause in the DELETE statement and need to use a Subquery or temporary table with sorted results inside to DELETE based upon a specific order.
SET ROWCOUNT Option
SET ROWCOUNT option is available as a session-based or client-based option to limit the number of records being fetched out by stopping the query processed. The syntax of the SET ROWCOUNT option would be
SET ROWCOUNT { number | @number_var }In this syntax:
number or @number_variable is an integer value or variable similar to the TOP clause. If the number is set to 0, it means no restriction to the records fetched. If the number is greater than 0, then it will restrict the result set.
To turn SET ROWCOUNT into OFF, we need to execute SET ROWCOUNT 0 (numeric zero) to indicate that there shouldn’t be any restrictions placed out.
The SET ROWCOUNT option can be turned ON/OFF via different methods as shown below
- At the Session level by executing SET ROWCOUNT num_value before executing our actual query. We will have a hands-on below.
- For all sessions opened up in SSMS by Clicking on Query Menu in SSMS -> Query Options -> General -> SET ROWCOUNT option. This will set the ROWCOUNT option to all Query windows newly opened out.
To test the SET ROWCOUNT option, let’s see how many records we have in the Person.Person table by running the below query.
SELECT COUNT(*)
FROM Person.Person;
Let’s execute the below query with the SET ROWCOUNT option along with the SELECT query as shown below.
SET ROWCOUNT 10;
SELECT FirstName, LastName
FROM Person.Person
ORDER BY FirstName;
This works pretty well compared to the TOP 10 Clause but we don’t have the additional options like PERCENT or WITH TIES option using the SET ROWCOUNT option.
To turn off the SET ROWCOUNT option for this session in which the SET ROWCOUNT option is enabled, we need to use SET ROWCOUNT 0 as shown below.
SET ROWCOUNT 0;
SELECT FirstName, LastName
FROM Person.Person
ORDER BY FirstName;
Let’s try the SET ROWCOUNT option on DML statements (this feature will be deprecated in a future version of SQL Server and hence care should be taken in development)
INSERT Statement
To INSERT only 10 records into a table, we can use the below script.
SET ROWCOUNT 10;
INSERT INTO Person.Person_test (PersonType, FirstName, MiddleName, LastName)
SELECT PersonType, FirstName, MiddleName, LastName
FROM Person.Person
UPDATE Statement
To UPDATE only 10 records in a table, we can use the below script.
SET ROWCOUNT 10;
UPDATE Person.Person_test
SET ModifiedDate = GETDATE()
WHERE ModifiedDate IS NULL;
DELETE Statement
To DELETE only 10 records in a table, we can use the below script
SET ROWCOUNT 10;
DELETE FROM Person.Person_test;
Caution using the SET ROWCOUNT option:
- SET ROWCOUNT option currently works for both SELECT and DML statements but support for DML statements will be removed in the future versions of SQL Server. Hence it is recommended to use the TOP Clause option instead of SET ROWCOUNT for DML statements to avoid unforeseen issues in future.
- Every time the SET ROWCOUNT option is enabled or set, it won’t be turned OFF by default and it will be active till the session is closed or disconnected. Hence accidentally forgetting to turn off SET ROWCOUNT option can negatively impact your overall activities via that particular session.
ROW_NUMBER Pagination Query
Often developers use the TOP query to limit the records returned to the client to do Pagination. ROW_NUMBER () function introduced in SQL Server 2005 along with Common Table Expressions often referred as cte can help achieve the Pagination option easily for developers by using the below query.
;WITH cte AS (
SELECT FirstName, LastName, ROW_NUMBER() OVER (ORDER BY FirstName) rnum
FROM Person.Person
)
SELECT *
FROM cte
-- WHERE rnum BETWEEN 1 AND 10; -- First Execution
WHERE rnum BETWEEN 11 AND 20; -- Second Execution 
Developers can easily replace the highlighted numeric values and achieve the pagination effect. Those numeric values can also be replaced with variables and then developers can pass the values via a variable as shown below.
DECLARE @start_value int = 11,
@pagination_value int = 10
;WITH cte AS (
SELECT FirstName, LastName, ROW_NUMBER() OVER (ORDER BY FirstName) rnum
FROM Person.Person
)
SELECT FirstName, LastName
FROM cte
WHERE rnum BETWEEN @start_value AND @start_value + @pagination_value - 1;OFFSET and FETCH Option
In SQL Server 2012, the ORDER BY clause was enhanced with the OFFSET and FETCH option to provide a better pagination option without the use of additional analytical like ROWNUMBER with common table expressions. The syntax of ORDER BY with OFFSET and FETCH option is as follows:
ORDER BY ColumnName
OFFSET n ROWS
FETCH NEXT x ROWS ONLY;WHERE OFFSET refers to skipping the n number of ROWS from the beginning and FETCH NEXT x ROWS refers to returning the x number of ROWS after skipping the OFFSET records.
To achieve the pagination logic for the query we have tried using the ROW_NUMBER option, we can achieve it via the OFFSET and FETCH option with the below query.
SELECT FirstName, LastName
FROM Person.Person
ORDER BY FirstName
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
To fetch the next set of 10 records, we can execute the above query by changing the OFFSET value to 10 instead of 0 ROWS like this.
SELECT FirstName, LastName
FROM Person.Person
ORDER BY FirstName
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Both OFFSET value and the FETCH NEXT numeric values can be replaced with a variable and used inside Procedure or Application Code to fetch the records optimally like this.
DECLARE @offset_value int = 10,
@next_value int = 10
SELECT FirstName, LastName
FROM Person.Person
ORDER BY FirstName
OFFSET @offset_value ROWS
FETCH NEXT @next_value ROWS ONLY;Conclusion
In this article, we have seen various approaches to limit the records returned by a SELECT statement using the TOP Clause and how to control the records affected by DML statements. We have also gone through the SET ROWCOUNT option to limit the SELECT statement and limit the number of records affected by DML statements along with the precautions to be taken care of while using the SET ROWCOUNT option. We have also gone through the Pagination query options using the ROW_NUMBER () function and the advanced feature of ORDER BY clause with OFFSET and FETCH option introduced in SQL Server 2012 and later versions. In my next article, we shall meet with another interesting topic. When writing queries for this article, I used code suggestions dbForge SQL Complete from a third-party vendor Devart which helped me a lot.
Last modified: October 26, 2022
