Microsoft SQL Server

Limit Result Set

Introduction#

As database tables grow, it’s often useful to limit the results of queries to a fixed number or percentage. This can be achieved using SQL Server’s TOP keyword or OFFSET FETCH clause.

Parameters#

Parameter Details
TOP Limiting keyword. Use with a number.
PERCENT Percentage keyword. Comes after TOP and limiting number.
## Remarks#
If ORDER BY clause is used, limiting applies to the ordered result set.
## Limiting With TOP
This example limits SELECT result to 100 rows.
SELECT TOP 100 *
FROM table_name;

It is also possible to use a variable to specify the number of rows:

DECLARE @CountDesiredRows int = 100;
SELECT TOP (@CountDesiredRows) *
FROM table_name;

Limiting With PERCENT

This example limits SELECT result to 15 percentage of total row count.

SELECT TOP 15 PERCENT *
FROM table_name

Limiting with FETCH

FETCH is generally more useful for pagination, but can be used as an alternative to TOP:

SELECT *
FROM table_name
ORDER BY 1
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

This modified text is an extract of the original Stack Overflow Documentation created by the contributors and released under CC BY-SA 3.0 This website is not affiliated with Stack Overflow