I got a very strange request: We have to constantly count the rows in a table can we increase the performance of count(*)

So lets analyze the problem step by step.

The table is a typical table the actual columns have no effect so I replaced them with just one column in the example to make things easier; Let's start by creating a heap

CREATE TABLE  MyTable

(

ID intNOTNULLIDENTITY(1,1),

Payload varchar(300)NOTNULL

)

and fill it with random data

DECLARE @i ASint= 1;

WHILE (@i < 1000)

BEGIN

INSERTINTO MyTable(Payload) VALUES (REPLICATE('ABC', 20 + @i % 80));

SET @i += 1;

END

Now lets see where we are on a heap

STATISTICSIOON

SELECT COUNT(*)FROM  MyTable

Table 'MyTable'. Scan count 1, logical reads 25, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

To count the rows SQL Server must do a full table scan and reads each page exactly once.

Let's see if an index will help us…

CREATE  CLUSTEREDINDEX CI_MyTable ON MyTable(ID); SELECT COUNT(*)FROM MyTable;

Table 'MyTable'. Scan count 1, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server now performs a clustered index scan which is in fact a table scan plus the overhead to find the first page. Actually a little bit more work than the table scan.

The trick to get it faster is to create an index that uses less space that the table (SQL Server will always select the smalest index to execute a COUNT(*))

The Smallest INDEX you can create on a table that has a clustered index is a secondary index that is composed just out of  the clustered key:

CREATE INDEX IX_ID ON MyTable(ID);

SELECT COUNT(*)FROM MyTable;

Table 'MyTable'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Now it just has to Scan the leaf nodes of the secondary index

Careful this kind of index has very limited use besides the example above.

Other uses would be for example: SELECT xyz WHERE uvw  IN (SELECT ID FROM MyTable)