Search This Blog

COUNT(*) for larger tables

Quick one:
As I wrote in one of my previous posts, I am not a fan of running COUNT(*) on any table. I prefer these alternative methods that depend on meta data rather than running a scan on table just to get a count. No matter the size of the table, you can get the row count using meta-data view.
This post is only about COUNT_BIG(*), so:
When you need to perform COUNT(*) on tables that have more than 4 billion rows (or 2 billion if they only use positive integers [greater than 0]), you need BIGINT version of the COUNT(*).
1
2
3
SELECT COUNT(*) FROM DBA.TableName -- smaller tables
 
SELECT COUNT_BIG(*) FROM DBA.TableName  -- very large tables

Sql Server: Get count(*) on all tables with very little overhead (without table scan), by querying metadata


Running COUNT(*) on a table results in Table scan or Index scan; Both are notpreferred. Meta-data views could help avoid that.
  • Option 1: Query ‘dm_db_partition_stats’ view
1
2
3
4
5
6
7
8
9
10
11
12
SELECT T.name AS [Table_Name]
     , I.name AS [Index_Name]
     , SPS.row_count AS [Record Count]
 
FROM sys.tables AS t
INNER JOIN sys.indexes AS i
    ON t.object_id = i.object_id
    AND I.index_id IN (1,0)
INNER JOIN sys.dm_db_partition_stats AS SPS
    ON SPS.object_id = t.object_id
    AND SPS.index_id = I.index_id
GO
  • Option 2: Query ‘sysindexes’ view. Please note that this is a depricated view.
1
2
3
SELECT OBJECT_NAME(id), rowcnt, * FROM sysindexes
WHERE indid IN (1,0)
ORDER BY OBJECT_NAME(id)
  • Option 3: Query ‘sys.partitions’ view.
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT OBJECT_NAME(object_id) AS [ObjectName]
, SUM(rows) AS [RowCount]
, data_compression_desc AS [CompressionType]
FROM sys.partitions WITH (nolock)
WHERE index_id < 2 --ignore the partitions from the non-clustered index if any
AND Object_name(object_id) NOT LIKE N'sys%'
AND Object_name(object_id) NOT LIKE N'queue_%'
AND Object_name(object_id) NOT LIKE N'filestream_tombstone%'
AND Object_name(object_id) NOT LIKE N'fulltext%'
AND Object_name(object_id) NOT LIKE N'ifts_comp_fragment%'
GROUP BY object_id, data_compression_desc
ORDER BY Sum(rows) DESC
OPTION (recompile);
Since we are only querying meta data, the results are instantaneous.