SET ROWCOUNT n versus SELECT TOP n

Both the SET ROWCOUNT and the SELECT TOP statements allow the amount of rows returned to be limited to a specific number. The SET ROWCOUNT command persists for the entire connection session so it has to be reset in every case (for example, SET ROWCOUNT 0) while the TOP option is valid for only the particular query in which it is referenced. One particular advantage of SET ROWCOUNT is that the argument can be a variable (for example, SET ROWCOUNT @NumberOfRows) whereas the TOP option only accepts literal values (for example, SELECT TOP 10).

While in most cases, using either option works equally efficiently, there are some instances (such as rows returned from an unsorted heap) where the TOP operator is more efficient than using SET ROWCOUNT. Because of this, using the TOP operator is preferable to using SET ROWCOUNT to limit the number of rows returned by a query.

On SQL 2005 this is straightforward in static SQL, as with a slight syntax change, TOP accepts expressions for the argument:

SELECT TOP(@n) col1, col2 FROM tbl