Quickest way to get the row count of a table
Note: This is not provide a transactionally
consistent count
You often need to find out the number of rows in a table. However running a
SELECT COUNT(*) FROM TABLE can be very slow.
From SQL 2005 onwards the system catalogues manitain the
row count for a table, and unlike in previous versions it is maintain in real time and I have heard should always
be correct, if you find it isn't then please provide me a repro.
So the quickest way to get the row count is to run a query like
select top 1 rows
from sys.partitions
where
object_id =
object_id(@tablename)
This returns the rows figure from the catalogue view sys.partitions
[SS 14/1/2010 18:45 Guaranteed is probably not the
correct word, especially as BOL states it is approximate. I am awaiting
confirmation under what circumstances the number will be approximate]
For partitioned tables you have to go a bit further
select sum(rows) as row_count
from
sys.partitions
where index_id
in (0,1)
and
object_id =
object_id(@table)
Thanks the comments and emails highlighting my faux pas
[SS 14/1/2010 18:45 Update following email exchange with Rubén Garrigós]
He also pointed out that the count isn't transactional.This is very true as
it would cause a large amount of blocking. You can see this by running the
following
create table fred (col1 int)
go
begin transaction
set nocount on
declare @i int =
0
while @i < 100000
begin
insert into fred values
(@i)
set @i = @i +
1
end
And then running this in a different connection
select * from sys.partitions
where object_id = object_id ('fred')
You will see 100000 as the rows number.
So in summary the number you get is up to date but not transactionally
conistent, it doesn't adhere to any of the isolation levels. However if you have
a very large table that might not make a difference, the difference between
1000000 and 1000203 may not count, so only use this as a guide. Don't use
this in any calculations that your finance Director will be basing decisions
on.
The key point is that this number is maintained in realtime, it doesn't
require some back ground process to go through and update it as it did in
previous versions.