The Evolution of Querying: SET ROWCOUNT vs TOP vs OFFSET-FETCH

Introduction

SET ROWCOUNT, TOP and OFFSET-FETCH are the filters that allow users to select the required number of rows. Let's create Pets Table to verify the behaviour of the filters.
Table Creation Script:

IF OBJECT_ID('Pets') IS NULL
BEGIN
 CREATE TABLE Pets (OwnerId INT,PetName VARCHAR(20))
 INSERT Pets(OwnerId,PetName)
 VALUES 
 (1,'Rocky'), (1,'Jackie'),
 (2,'Charlie'), (2,'Archie'),
 (3,'Oscar'), (3,'Coco'),
 (4,'Jasper'), (4,'Shadow')    
END

Input:
SELECT OwnerId, PetName FROM Pets

Output:
(8 rows affected)

SET ROWCOUNT

It causes SQL Server to stop processing the query after returning the specified number of rows.
Example:

Input:

SET ROWCOUNT 4;
SELECT OwnerId, PetName FROM Pets

Output:
(4 rows affected)

Points to Ponder

Using SET ROWCOUNT doesn't affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server.
Obsolete support for DELETE, INSERT, and UPDATE statements and not recommended to be used.
SET ROWCOUNT is outside a statement that executes a query and not considered in a query plan.

Top

It returns only the first set of rows from the query result. The set of rows can be either a number or a percent of the rows.
Example:

Input:
SELECT TOP(4) OwnerId, PetName FROM Pets

Output:
(4 rows affected)

Points to Ponder

Input is the numeric expression that returns the number of rows, and the expression is implicitly converted to a FLOAT value if PERCENT is specified; BIGINT otherwise.
Example:

Input:
```
DECLARE @p AS int = 4;  
SELECT TOP(@p) OwnerId, PetName FROM Pets
```
Output:
(4 rows affected)
It can work without ORDER BY Clause, without it the order of the rows is arbitrary.
PERCENT option to select top percent of rows from the result set.
Example:

Input:
SELECT TOP(20) PERCENT OwnerId, PetName FROM Pets

Output:
(4 rows affected)
WITH TIES option returns two or more rows that tie for the last place in the limited results set. It may return more rows than the value specified in the expression. Using WITH TIES to include rows that match the values in the last row. It can only be used with ORDER BY Clause.
Example:

Input:
```
-- Add Duplicate Values
INSERT Pets(OwnerId,PetName)
VALUES 
(1,'Rocky'), (1,'Jackie')
-- Select Top two rows brings tied rows with same OwnerId
SELECT TOP(2) WIth TIES OwnerId, PetName FROM Pets
WHERE OwnerId = 1
```
Output:
(4 rows affected)
We can limit the rows affected by DELETE, INSERT, or UPDATE using TOP filter.

OFFSET FETCH Clause

The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause. It comes in handy to implement paging solutions using OFFSET-FETCH Clause.
Example:

Input:

-- OFFSET denotes the number of rows to skip
-- FETCH denotes the number of rows to return
-- Below query brings tied rows with same OwnerId
SELECT OwnerId, PetName FROM Pets
 ORDER BY OwnerId
OFFSET 0 ROWS FETCH NEXT 4 ROWS ONLY;

Output:
(4 rows affected)

Points to Ponder

ORDER BY is mandatory to use OFFSET and FETCH clause.
OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
TOP and OFFSET -FETCH cannot exist together in the same query expression.
The OFFSET/FETCH RowCount expression can be any arithmetic, constant, or parameter expression that returns an integer value. The RowCount expression does not support scalar sub-queries.