SQL Server - Counting rows

COUNT is the most common way to count the number of rows in a table at a given time. However, COUNT can cause problems with table scans and locks etc. and anyone else who wants to use the table will have to queue up to access it, which can cause problems especially if the query is run frequently.

A common way to solve this is to use the sys.partitions system table instead. In sys.partitions, all tables and almost all indexes (not e.g. full text and xml) have a partition even if you have not partitioned the table itself. The column index_id indicates which index id the partition belongs to. Id 0 stands for a heap and id 1 for the clustered index. More details about the id of each index can be found in the sys.indexes table for those who are interested. The number of rows for the partition is in the rows column, be sure to sum over all possible partitions for the index to get the correct value. Note that the number of rows in this table, according to the Microsoft documentation, is approximate so if the actual number of rows is needed this method will not work. However, keep in mind that COUNT is a snapshot and if the number of rows is constantly changing, in many cases this value can be used.

The table dbo.bigTable contains 60000000 (60 million) rows and if we test run COUNT(*) against it with STATISTICS IO turned on we get the following:

Table 'bigTable'. Scan count 9, logical reads 274538, physical reads 0, read-ahead reads 32, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Instead, we use sys.partitions to get the line count as below.

SELECT OBJECT_SCHEMA_NAME(object_id) AS [Schema namn], OBJECT_NAME(object_id) AS [Tabell namn], SUM(p.rows) AS [Rader]
FROM sys.partitions p
WHERE index_id < 2
AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1
AND object_id = OBJECT_ID('[dbo].[bigTable]')
GROUP BY object_id

We get the following:

Table 'sysrowsets'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This way generates very much less reads and not in dbo.bigTable but in sysrowsets so there is no risk of locks or other impact for other users.

COUNT har fördelen att man kan använda WHERE sats för att specificera vilka värden som ska räknas vilket inte går i andra lösningen. Om det är ett fåtal värden på argumenten är en möjlig lösning att skapa filtrerade ickeklustrade index på tabellen. Om man anger index_id för det nya indexet i frågan ovan i stället för alla heaps och klustrade index (index_id < 2) summeras alla rader för det indexet i stället.

A common use of COUNT() with a WHERE statement is to check if there are any values in a table and if so, do something e.g. if we check if there is a specific value in one of the columns and if so, execute a stored procedure.

IF (SELECT COUNT(*) FROM [dbo].[bigTable] WHERE column2 = 'One value') > 1
BEGIN
EXEC sp_minProcedure
END

This will generate the same number of reads (274538) as in our first test, but since we only want to check if there is any row with that value, we can use exists instead:

IF EXISTS(SELECT 1 FROM [dbo].[bigTable] WHERE column2 = 'One value') > 1

Table 'bigTable'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

But this way only works if we want to check that there are some rows with the value. If we instead try to check if there is more than 1 line, exists doesn't work anymore and we are back to COUNT and 274538 reads:

IF (SELECT COUNT(*) FROM [dbo].[bigTable] WHERE column2 = 'One value') > 1

But since we only want to know if there is more than one row with the value, with a little TSQL magic we can bring the number of reads down significantly by using TOP 2 in an inner SQL statement:

IF (SELECT COUNT(*) FROM (SELECT TOP 2 * FROM [dbo].[bigTable] WHERE column2 = 'One value') AS b) > 1

Table 'bigTable'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Please comment!

/Björn

​