Steve Wilson Steve Wilson - 20 days ago 6
SQL Question

Is it possible to get the number of rows from a table in O(1) time?

In SQL server, obviously one way of getting the number of rows in a table is

SELECT COUNT(*) FROM MyTable


but I assume that's
O(n)
time where
n
is the number of rows. Is there any metadata I can access that has the number of rows stored?

Answer

Yes, you can use sys.partitions, it might not be the exact number, but it's extremely fast:

SELECT SUM(rows) 
FROM sys.partitions
WHERE [object_id] = OBJECT_ID('dbo.MyTable')
AND index_id IN (0,1);