SET ROWCOUNT VS TOP in SQL Server

Both SET ROWCOUNT statement and TOP clause are used to limit the number of rows returned. However there are some significant differences between them. They are listed out here

SET ROWCOUNT statement	TOP clause
It is specific to a batch. It will affect all DML operations until it is reset to 0	It has statement level scope and it will not affect other statements until specified each for them
Variable can be used in all version. Ex SET ROWCOUNT @var	Variable can be used only from version 2005 onwards like TOP (@var)
Not possible to set percentage	Possible to set percentage option. Ex SELECT TOP 20 percent * FROM TABLE
Not possible to specify decimal value	Possible to specify decimal values along with PERCENT option.
It is executed outside of actual DML and its value is not part of query plan	The expression used in TOP clause will be considered as part of query plan.
Multiple SET ROWCOUNT statements are allowed in a single batch. However the lastly available before the statements will be used. SET ROWCOUNT 10 SET ROWCOUNT 100 SELECT * FROM SYS.OBJECTS SET ROWCOUNT 0 The count  100 will be considered for execution	Multiple TOP is not allowed however they can be nested. SELECT TOP 10 * FROM ( SELECT TOP 100 * FROM SYS.OBJECTS ) AS T The final result will have maximum of 10 rows
As this is executed as a seperate statement It can not be part of VIEW definition	It can be part of VIEW definition.
This is marked as Deprecated. Avoid using this	Always available in all versions

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan