In SQL Server, you can use SET ROWCOUNT to limit the rows returned by a query.

The way it works is that it causes SQL Server to stop processing the query after the specified number of rows are returned.

It’s similar to the TOP() clause, but with the difference that SET ROWCOUNT is set outside of the query, and will affect all subsequent queries.

Example

Here’s an example to demonstrate.

First, let’s do a query without setting ROWCOUNT.

SELECT * FROM Dogs;

Result:

+---------+-----------+
| DogId   | DogName   |
|---------+-----------|
| 1       | Fetch     |
| 2       | Fluffy    |
| 3       | Wag       |
| 1002    | Fetch     |
+---------+-----------+
(4 rows affected)

So four rows are returned without setting ROWCOUNT.

Now let’s use SET ROWCOUNT to limit the number of rows, then run the query again.

SET ROWCOUNT 2;
SELECT * FROM Dogs;

Result:

+---------+-----------+
| DogId   | DogName   |
|---------+-----------|
| 1       | Fetch     |
| 2       | Fluffy    |
+---------+-----------+
(2 rows affected)

Subsequent Queries

As mentioned, SET ROWCOUNT affects all subsequent queries.

If I run the following query immediately after the previous one, two rows are also returned.

SELECT * FROM Cats;

Result:

+---------+-----------+
| CatId   | CatName   |
|---------+-----------|
| 1       | Meow      |
| 2       | Fluffy    |
+---------+-----------+
(2 rows affected)

This is despite the fact that three rows would have been returned, had I not used SET ROWCOUNT to limit the rows being returned.

I can verify this with the COUNT() clause.

SELECT COUNT(*) FROM Cats;

Result:

+--------------------+
| (No column name)   |
|--------------------|
| 3                  |
+--------------------+

Reset ROWCOUNT

You can reset the ROWCOUNT value by giving it a value of 0 (zero).

This means that queries will no longer have their results limited. The query will continue processing all the way to the end.

SET ROWCOUNT 0;
SELECT * FROM Cats;

Result:

+---------+-----------+
| CatId   | CatName   |
|---------+-----------|
| 1       | Meow      |
| 2       | Fluffy    |
| 3       | Scratch   |
+---------+-----------+
(3 rows affected)

ROWCOUNT vs TOP()

If you use the TOP() clause in a query when you’ve already set your ROWCOUNT, your ROWCOUNT setting will only override TOP() if ROWCOUNT is the smaller value.

Here’s an example where ROWCOUNT is the larger value.

SET ROWCOUNT 3;
SELECT TOP(2) * FROM Dogs;

Result:

+---------+-----------+
| DogId   | DogName   |
|---------+-----------|
| 1       | Fetch     |
| 2       | Fluffy    |
+---------+-----------+
(2 rows affected)

In this case, the TOP() clause won out.

And here’s an example where ROWCOUNT is the smaller value.

SET ROWCOUNT 1;
SELECT TOP(2) * FROM Dogs;

Result:

+---------+-----------+
| DogId   | DogName   |
|---------+-----------|
| 1       | Fetch     |
+---------+-----------+
(1 row affected)

Compatibility

Note that Microsoft advises that SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Microsoft recommends that you avoid such usage in new development work, and plan to modify applications that currently use it.

Microsoft recommends using the TOP() clause if you need to limit rows affected in DELETE, INSERT, and UPDATE statements.

Caution

The ROWCOUNT option works on most T-SQL statements, including triggers. However, it does not affect dynamic cursors, but it does limit the rowset of keyset and insensitive cursors. This option should therefore be used with caution.