A blog reader asked that why should we avoid COUNT (*) and why it is declared forbidden in an early post SQL SERVER: 9 Most Forbidden Things . Actual point was that we should avoid to use COUNT (*) to check records existence. How few developers check record existence:
-- Declare a variable to store intermediate results
DECLARE @TotalCount INT
-- Count total records and place resultant figure in variable
SELECT @TotalCount=COUNT(*) FROM Sales.SalesOrderDetail
IF @TotalCount >0
-- If records exists then
If we place SET STATISTICS IO ON before query to check logical reads we will get following results.
Table 'SalesOrderDetail'. Scan count 1, logical reads 228, physical reads 0, read-ahead reads 0
Now modify above statements as follow to get same results and check how costly the query is.
IF EXISTS(SELECT 1 FROM Sales.SalesOrderDetail)
Table 'SalesOrderDetail'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0
Difference between logical reads of both queries is huge and that is the reason we should avoid count (*)
If your target is to count all records for some other options use following query
WHERE index_id = 1
AND object_id = OBJECT_ID('Sales.SalesOrderDetail')
Table 'sysrowsets'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0
I urge you to execute above query and COUNT (*) query for largest table of your own database and check the difference of logical reads.