SQL ROWCOUNT

The SQL Server ROWCOUNT Set Function causes the server to stop the query processing after the specified numbers are returned. The basic syntax of the SQL ROWCOUNT 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 demonstration

Source Table 1

What is the use of SQL Server ROWCOUNT?

In this example, we show you how the SQL 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]
Counting Total records 1

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

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

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.

SQL SET ROWCOUNT OFF Example

Use the SET ROWCOUNT 0; to stop setting this one or to make it off.

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

Use SQL 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 it as 5 and selected TOP 6. Here, it returned the first 5 records because, once it reaches 5, the query execution will stop.

SQL ROWCOUNT 4
Categories SQL