Set rowcount and Top N

Sometimes, if n in Top N is a variable, you need to use:

declare @count1 intset @count1 = 8select top <strong>(@count1)</strong> * from MyTable

If set rowcount is used, the same effect can be achieved.

declare @count1 intset @count1 = 8        set RowCount @count1select * from MyTable

Note that using set rowcount has a side effect. Once set, the subsequent statements will be affected, and SQL server will change this in subsequent versions, so be careful when using it.

Important:

In the next version of SQL Server, using set rowcount does not affect the delete, insert, and update statements. In the new development work, avoid using the set rowcount statement with the delete, insert, and update statements, and plan to modify the application currently using the statement. In addition, for the delete, insert, and update statements currently using set rowcount, we recommend that you use top syntax to overwrite them. For more information, see Delete (TRANSACT-SQL), insert (TRANSACT-SQL), or update (TRANSACT-SQL ).

Set rowcount and Top N