The SET ROWCOUNT limits the result set to the number of rows specified. The SQL Server stops the query processing once the specified number of rows are reached. The ROWCOUNT effects DML statements. However it is recommended to use TOP with Insert, Update and Delete statements as ROWCOUNT won’t affect these statements in future releases.
An example of ROWCOUNT is shown below.
All the queries above return 10 rows as the ROWCOUNT value is set to 10. To disable the ROWCOUNT options set it to 0 as SET ROWCOUNT 0.
The ROWCOUNT can’t be used in a function; however it does affect the rows returned by select statement referring a function. Consider below example.
CREATE FUNCTION [dbo].fngetobjectsbytype ( @type varchar(100) ) RETURNS TABLE AS RETURN ( SELECT * FROM sys.objects where type=@type )
The above query creates an inline table valued function which returns the type of database object depending on @type parameter. The ROWCOUNT affects the rows returned by the select statement referring the function as shown in below snapshot.
To get more details on SET ROWCOUNT refer to http://msdn.microsoft.com/en-us/library/ms188774.aspx