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 affectDELETE
,INSERT
, andUPDATE
statements in a future release of SQL Server. - Obsolete support for
DELETE
,INSERT
, andUPDATE
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 ifPERCENT
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. UsingWITH TIES
to include rows that match the values in the last row. It can only be used withORDER 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
, orUPDATE
usingTOP
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 useOFFSET
andFETCH
clause.OFFSET
clause is mandatory withFETCH
. You can never use,ORDER BY
…FETCH
.TOP
andOFFSET
-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.