Published on

December 7, 2010

Counting Rows in SQL Server Tables

When working with SQL Server, it is often necessary to determine the number of rows in a table. The most common approach is to use the SELECT COUNT(*) statement, but this method has its limitations. Firstly, it requires a table scan, which can be time-consuming and resource-intensive for large tables. Secondly, it does not provide an easy way to obtain row counts for all tables in a database.

Fortunately, SQL Server provides a system function called sp_spaceused that can help overcome these challenges. When executed without any parameters, sp_spaceused returns usage information about the current database. However, when provided with a specific object name, such as a table name, it also returns the number of rows along with the amount of space used by the table and its indexes.

Under the hood, sp_spaceused retrieves row count information from system objects, such as the sysindexes table in SQL Server 2000 or the sys.dm_db_partition_stats DMV in SQL Server 2005 and later versions. Since the counts are obtained from system objects, there is no need for a table scan, solving the first problem.

To address the second problem of obtaining row counts for all tables in a database, you can either use a cursor to iterate through each table and call sp_spaceused or directly query the system objects. Here are examples of both approaches:

Row Counts Using sysindexes (SQL Server 2000)

-- Shows all user tables and row counts for the current database
-- Remove OBJECTPROPERTY function call to include system objects
SELECT o.NAME, i.rowcnt
FROM sysindexes AS i
INNER JOIN sysobjects AS o ON i.id = o.id
WHERE i.indid < 2 AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
ORDER BY o.NAME

Row Counts Using DMVs (SQL Server 2005 and later)

-- Shows all user tables and row counts for the current database
-- Remove is_ms_shipped = 0 check to include system objects
-- i.index_id < 2 indicates clustered index (1) or hash table (0)
SELECT o.name, ddps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id
WHERE i.index_id < 2 AND o.is_ms_shipped = 0
ORDER BY o.NAME

It is important to note that the row count information obtained from these system objects is not dependent on updated statistics. This means that even if statistics become outdated, the row counts will remain accurate. To verify this, you can compare the counts obtained using the SELECT COUNT(*) method with the counts from the system objects. In most cases, they should match perfectly.

In conclusion, when you need to determine the number of rows in a SQL Server table, it is recommended to use the system objects sysindexes or sys.dm_db_partition_stats instead of the SELECT COUNT(*) method. This approach eliminates the need for a table scan and provides accurate row counts for all tables in the database.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.