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
Advertisements

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 herehttp://beyondrelational.com/blogs/madhivanan/archive/2009/03/04/beware-of-the-usage-of-set-rowcount.aspxMadhivananhttp://beyondrelational.com/blogs/madhivanan


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: