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 |
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
No comments:
Post a Comment