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

ParameterDetails
TOPLimiting keyword. Use with a number.
PERCENTPercentage keyword. Comes after TOP and limiting number.

Remarks

If ORDER BY clause is used, limiting applies to the ordered result set.

Limiting with FETCH

SQL Server 2012

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

Limiting With PERCENT

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

SELECT TOP 15 PERCENT *
FROM table_name

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;


2016-07-21
2017-02-17
Microsoft SQL Server Pedia
Icon