Don’t be fooled by the title of this post: while counting the number of rows in a table is a trivial task for you, it is not trivial at all for SQL Server.
Every time you run your COUNT(*) query, SQL Server has to scan an index or a heap to calculate that seemingly innocuous number and send it to your application. This means a lot of unnecessary reads and unnecessary blocking.
Jes Schultz Borland blogged about it some time ago and also Aaron Bertrand has a blog post on this subject. I will refrain from repeating here what they both said: go read their blogs to understand why COUNT(*) is a not a good tool for this task.
The alternative to COUNT(*) is reading the count from the table metadata, querying sys.partitions, something along these lines:
SELECT SUM(p.rows)
FROM sys.partitions p
WHERE p.object_id = OBJECT_ID('MyTable')
AND p.index_id IN (0,1); -- heap or clustered index
Many variations of this query include JOINs to sys.tables, sys.schemas or sys.indexes, which are not strictly necessary in my opinion. However, the shortest version of the count is still quite verbose and error prone.
Fortunately, there’s a shorter version of this query that relies on the system function OBJECTPROPERTYEX:
SELECT OBJECTPROPERTYEX(OBJECT_ID('MyTable'),'cardinality')
Where does it read data from? STATISTICS IO doesn’t return anything for this query, so I had to set up an Extended Events session to capture lock_acquired events and find out the system tables read by this function:
Basically, it’s just sysallocunits and sysrowsets.
It’s nice, short and easy to remember. Enjoy.
Archived WordPress comments (14)
Historical comments from the original site; this archive is read-only.
Jeff, thanks for stopping by and for the good laugh.
select count(*) from VarOrFormValue
gives
23650227
and
SELECT OBJECTPROPERTYEX(OBJECT_ID('VarOrFormValue'),'cardinality')
gives
NULL
Clearly I'm missing something obvious????
SELECT *
FROM sys.sysindexes
WHERE id = OBJECT_ID('dbo.YourTableNameHere')
AND indid IN (0,1)
;
The "indid" of 0 or 1 will pick up either on the heap or the clustered index depending, of course, on whether there's a clustered index or not. This could easily be converted to a function to provide similar utility to the 2012 function of OBJECTPROPERTYEX().
SELECT rowcnt
FROM sys.sysindexes
WHERE id = OBJECT_ID('cpr.VarOrFormValue')
AND indid IN (0,1)
24,444,019 in 75 ms first time, about 25ms thereafter
select count(*) from cpr.VarOrFormValue
24,444,019 in 14,569 ms first time, about 750ms thereafter.
That makes it worthwhile using.
But I'm hesitant to use it, because we have a table where
COUNT_BIG(*) >> OBJECTPROPERTYEX(...,'cardinality')
How can that be?
If it makes no meaningful difference, then no harm done. It's one of those things that cause no harm and increases in benefit as the data scales up.
The only time that you might have to do a significant test to see which is best is if you have something that needs to do a COUNT tens or hundreds times per second.. A COUNT on a short table may be more efficient for such heavy hits.
SELECT
t.[name]
, t.[object_id]
, p.[rows] AS [sys.partitions]
, CONVERT(int,OBJECTPROPERTYEX(t.[object_id],'Cardinality')) AS [OBJECTPROPERTYEX]
FROM sys.tables t
INNER JOIN sys.partitions p ON p.[object_id] = t.[object_id] AND p.[index_id] IN (0,1)
WHERE t.[is_memory_optimized] = 1
ORDER BY t.[name]
I get the following results:
name object_id sys.partitions OBJECTPROPERTYEX
------------------------------ ----------- -------------------- ----------------
DemoSalesOrderDetailSeed 1556200594 538 0
DemoSalesOrderHeaderSeed 1588200708 31465 0
Product_inmem 2039678314 504 0
SalesOrderDetail_inmem 468196718 121317 0
SalesOrderHeader_inmem 212195806 31465 0
SpecialOffer_inmem 1895677801 16 0
SpecialOfferProduct_inmem 132195521 538 0