SQL ROWCOUNT

The SQL ROWCOUNT Set Function causes the SQL server to stop the query processing after the specified numbers returned. The basic syntax of the ROWCOUNT in SQL Server is as shown below:

SET ROWCOUNT { number (or number variable) }

-- For example,
SET ROWCOUNT 4;

We are going to use the below-shown data for this SQL Server demonstration

SQL ROWCOUNT 1

What is the use of SQL ROWCOUNT?

In this example, we show you how the SQL Server SET ROWCOUNT will affect the queries. Before we get into the primary SQL Server example, let me use the COUNT Function to find the number of rows that our employee table holds.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
FROM [Employee];

SELECT COUNT(*) AS [Number of Records] FROM [Employee]
SQL ROWCOUNT 1

From the above screenshot, you can see that the table contains 14 records. Now, let us use the ROWCOUNT to restrict the records to 7.

SET ROWCOUNT 7;
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
FROM [Employee];

As you can see, the query is returning seven records. It is because, when the query processing reaches to 7th record, it will stop processing the other records.

SQL ROWCOUNT 2

NOTE: Though this ROWCOUNT will not affect the DELETE, UPDATE, and INSERT statements, it is good practice to avoid this statement. Instead of this, you can use the TOP Clause

SET SQL ROWCOUNT OFF Example

Use the SET ROWCOUNT 0; to set the ROWCOUNT stop

SET ROWCOUNT 0;
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
FROM [Employee];
SQL ROWCOUNT 3

Use Sql Server ROWCOUNT ON TOP Clause Example

If you use both the TOP Clause and the ROWCOUNT, then this function will override the TOP Clause

SET ROWCOUNT 5;
GO
SELECT TOP 6 [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
FROM [Employee];

As you can see from the below screenshot, we set the ROWCOUNT as 5 and selecting TOP 6. Here, it returned the first 5 records because, once it reaches 5, the query execution will stop.

SQL ROWCOUNT 4