How to count rows in SQL Server

One of the fundamental aspects of working with SQL Server is understanding how to get the number of rows affected by a query. Whether you’re working with SELECT, INSERT, UPDATE, or DELETE statements, knowing the row count can provide critical insights into your database operations. In this blog post, we’ll explore different methods to retrieve the row count in SQL Server and discuss scenarios where each method is most effective.

1. Using COUNT(*) with SELECT Statements

When you need to find out how many rows exist in a table or result set, the COUNT(*) function is your go-to tool. This function counts all rows in a table or the rows returned by a query.

SELECT COUNT(*) FROM Employees;    

This query will return the total number of rows in the Employees table. If you want to count rows based on a specific condition, you can include a WHERE clause:

SELECT COUNT(*) FROM EmployeesWHERE Department = 'Sales';    

2. Retrieving Row Count for INSERT, UPDATE, and DELETE Statements

For DML operations like INSERT, UPDATE, and DELETE, you can use the @@ROWCOUNT function to get the number of rows affected by the last statement executed.

Example with INSERT:

INSERT INTO Employees (FirstName, LastName, Department)VALUES ('John', 'Doe', 'Sales');SELECT @@ROWCOUNT AS 'RowsInserted';    

This will return the number of rows inserted, which is particularly useful when inserting multiple rows at once.

Example with UPDATE:

UPDATE EmployeesSET Department = 'Marketing'WHERE Department = 'Sales';SELECT @@ROWCOUNT AS 'RowsUpdated';    

This query updates all employees in the Sales department to the Marketing department and returns the count of updated rows.

Example with DELETE:

DELETE FROM EmployeesWHERE Department = 'Sales';SELECT @@ROWCOUNT AS 'RowsDeleted';    

This deletes all employees in the Sales department and returns the number of rows deleted.

3. Using ROWCOUNT_BIG()

The ROWCOUNT_BIG() function works similarly to @@ROWCOUNT but returns the count as a bigint data type. This is useful for tables with a very large number of rows where the row count might exceed the range of an int.

UPDATE EmployeesSET Department = 'Marketing'WHERE Department = 'Sales';SELECT ROWCOUNT_BIG() AS 'RowsUpdated';    

4. Using SQL Server Management Studio (SSMS)

If you’re using SSMS, you can quickly see the row count for a SELECT query by executing the query and looking at the Messages tab in the results pane. SSMS displays the number of rows affected by the query there.

5. Estimating Row Count with sys.partitions

For very large tables, a quick estimate of the row count can be obtained from the sys.partitions system view. This method is faster than COUNT(*) but may not always be perfectly accurate.

SELECT SUM(p.rows) AS RowCountFROM sys.partitions pWHERE p.object_id = OBJECT_ID('Employees') AND p.index_id IN (0, 1);    

Understanding and efficiently retrieving the row count in SQL Server is essential for database management and Performance Tuning. Whether you’re counting rows in a table, monitoring the impact of data modification operations, or estimating row counts for large tables, SQL Server provides a variety of tools to meet your needs.

If you’re looking to optimize your SQL Server environment or need expert assistance with Performance Tuning, consider Stedman Solutions’ Managed Services. Our team of SQL Server specialists can help you achieve peak performance and reliability. For continuous monitoring and alerting, try our Database Health Monitor.

Feel free to reach out to Stedman Solutions for any SQL Server needs or questions. We’re here to help you succeed with your SQL Server projects.