I recently faced an issue where my query returns only five, whereas the table contains 542 records. The select statement does not have a where clause to restrict the number of rows in the output.
If I check the record count, it returns 542 rows.
I have not used any predicate (WHERE) clause in the earlier SELECT statement. Therefore, the query must return all rows in its output.
What is the issue? The SQL Server Management Studio SET ROWCOUNT value can be a problem.
Launch Microsoft SQ Server Management Studio and navigate to Tools -> Options -> Query Execution -> SQL Server -> General and check the value for the SET ROWCOUNT section as shown below.
As shown above, my SSMS client configuration specifies value 5 for SETROWCOUNT. Therefore, it returns a maximum of 5 rows in the query output.
By default, it uses a value 0 in the SET ROWCOUNT section. That means SQL Server displays all satisfied query results in its default configuration of SSMS.
Note: You need to restart SQL Server Management Studio after modifying the SET ROWCOUNT value in SSMS.
As shown below, after making the change in SSMS configuration, the query returns 542 rows in its output.
Note:
I recommend you to always use the Microsoft SQL Server Management Studio latest version.