SQL SERVER – Why my result set gives me constant number of rows ? (SET ROWCOUNT)

Feeds:: Posts; Comments

« SQL SERVER – How to Refresh SSMS IntelliSence Local Cache

SQL SERVER – How to filter databases in sp_MSforeachdb »

SQL SERVER – Why my result set gives me constant number of rows ? (SET ROWCOUNT)

February 9, 2014 by Muhammad Imran

I came across this query from one of my SQL Server developers while he was doing some R&D in SQL Server and suddenly the SQL Server started giving him fixed number of result sets (5 rows of each table/views) as shown in the picture below.

First of all he tried all the way to fix it including the restart of development SQL Server. It seems, he had to re-install his SQL Server ;). He called me, if I can help him to fix it. I asked him few queries and started doing the troubleshooting and finally fixed the issue. Let me reproduce the same issue and fix it step by step.

Step 1 :
Open SSMS (SQL Server Management Studio) and go to Tools >> Options menu as shown in the image below.

Step 2 :
Once you select options menu, an options dialogue box will pop up. Now you need to select “Query Execution” from left hand pane and make SET ROWCOUNT =0 as shown in the image below. By mistake my developer put 5 in the SET ROWCOUNT and it returns 5 number of rows in each result sets for each table and view. Once you are done press OK button.