How many rows in that table?

How many of us have typed ‘select count(*) from TABLE’ when we want to know how many rows in a table?

There is another way, by querying sys.partitions. 

I’ve compared the performance and IO.

 

   1:  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

   2:   

   3:  SET STATISTICS TIME ON;

   4:   

   5:  SET STATISTICS IO ON;

   6:   

   7:  SELECT COUNT(1)

   8:  FROM   MYBIGTABLE

   9:   

  10:  DECLARE @objid AS BIGINT;

  11:   

  12:  SELECT @objid = object_id

  13:  FROM   sys.tables

  14:  WHERE  name = 'MYBIGTABLE';

  15:   

  16:  SELECT SUM(rows)

  17:  FROM   sys.partitions

  18:  WHERE  index_id IN (0, 1)

  19:         AND [object_id] = @objid;

  20:   

  21:  SET STATISTICS TIME OFF;

  22:   

  23:  SET STATISTICS IO OFF;  

The results from STATISTICS IO and TIME are as follows;

Table 'MYBIGTABLE'. Scan count 1, logical reads 115328, physical reads 0, read-ahead reads 115328, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 27628 ms,  elapsed time = 61186 ms.

So over a minute.

For the query on sys.partitions;

Table 'syssingleobjrefs'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 4, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 140 ms.

(1 row(s) affected)
Table 'sysrowsets'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 35 ms.

So 140+35=175ms, so less than a quarter of a second compared with 61 seconds.

Much much much much much much faster!