Count of rows in a table

Here are some methods to return the row count from a table: –

SELECT row_count = SUM(row_count)
FROM sys.dm_db_partition_stats
WHERE [object_id] = OBJECT_ID(N'Sales.SalesOrderHeader') AND
index_id < 2; --0 = Heap; 1 = Clustered index.
SELECT row_count = SUM(rows)
FROM sys.partitions
WHERE [object_id] = OBJECT_ID(N'Sales.SalesOrderHeader') AND
index_id < 2; --0 = Heap; 1 = Clustered index.
EXECUTE sys.sp_spaceused 'Sales.SalesOrderHeader';
EXECUTE dbo.sp_DataProfile 'Sales.SalesOrderHeader',0;
SELECT COUNT(*) FROM Sales.SalesOrderHeader;

The first three rely on the dynamic management views sys.dm_db_partition_stats and sys.partitions, where the latter two will actually scan the table or index to count the rows.

sp_DataProfile is a great free (see EULA for terms) tool, to give various statistics for a table and it’s columns, one of which is row count.

A direct count against the table will give an accurate row count but will hold locks potentially blocking other connections. For most scenarios, sp_spaceused or a DMV query is accurate enough. It is typically not accurate if other connections are modifying data and if that is the case, a direct table count might give you an accurate count that will soon be stale anyway.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s