USING SET ROWCOUNT, TOP, AND PERCENT TO LIMIT A RESULT SET

Using the SET ROWCOUNT, TOP, and PERCENT statements to limit a result

set can be useful. You can use this statement to limit the rows

returned from a simple select query. This option is session-specific

and does not affect any other existing connection.

Assume you have a table of 23 rows, but you only want a sampling of the

data. Use the following to accomplish this task.

SET ROWCOUNT 5

SELECT * FROM pubs..authors

This would return the first five rows in the table pubs..authors.

Alternatively, you can write the preceding statement to return the same

result set with the TOP keyword.

SELECT TOP 5 * FROM pubs..authors

If you are in need of a percentage of the data in a table, use the

PERCENT keyword in your statement.

SELECT TOP 5 PERCENT * FROM pubs..authors

This would only return 2 rows. i.e., 2 rows / 23 rows = 1.15 rows

rounded to the next highest number.

One thing to remember is that SET ROWCOUNT takes precedence over TOP.

If you have previously defined your SET ROWCOUNT option to 5 and then

try to select TOP 10 from your table, you will only get 5 rows

returned.

To return your ROWCOUNT setting to normal, issue the following:

SET ROWCOUNT 0

You can also use the SET ROWCOUNT to act upon a certain number of

row(s) in a data modification statement.

SET ROWCOUNT = 1

DELETE FROM pubs..authors

Although this statement looks dangerous, it will be limited to the

first row it encounters and will delete only that row. This method is

also helpful if you have a duplicate row in a table that you need to

purge. Set your ROWCOUNT option to the appropriate value and delete the

record based on the criteria in the WHERE clause. This will delete the

first occurrence of the row while leaving the remaining row intact.