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’
–don’t forget to set 0 again
–otherwise all query will return 2 rows
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of

One Response to “SET ROWCOUNT in SQL Server 2008 with T-SQL and SSMS”

  1. Madhivanan Says:

    Note that SET ROWCOUNT will be removed from future versions of SQL Server. Always make use of TOP clause hereafter.Read more about it here

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: