SET ROWCOUNT in SQL Server 2008 with T-SQL and SSMS

Today morning one of my junior .NET developer was trying to execute query and he was getting only few number of rows from result set since we have millions of records in that tables. He was frustrated and came to me for this reason. Actually this is not a big deal but due to lack of understanding and knowledge it becomes hard to find out why this was happening.

When I checked his SSMS->Tools->Options->Query Execution->General->Set RowCount, I found that there was a value 50 set so it was returning only 50 rows. Actually It suppose to be 0 to return all rows. This option is being set when we want only few rows out of the query and wanted to stop query execution once we achieve those rows.

This command is somehow similar to TOP clause but there is a difference which I will cover in later article.

Anyway, we saw above that how we can set ROWCOUNT from GUI now I will show you one simple script which will do the same.

Create Table emps

(

      name varchar(20),

      dept varchar(20)

)

Insert into emps (Name,Dept)

Select 'Ritesh','MIS' union all

Select 'Rajan','Acct' union all

Select 'Bihag','DBA'

GO

SET ROWCOUNT 2;

SELECT * FROM emps

--don't forget to set 0 again

--otherwise all query will return 2 rows

SET ROWCOUNT 0;

GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com