Different ways to count rows from a table

The most reliable and suggested method is to use count(*)

1 Select count(*) from table_name

But other than that you can also use one of the followings


2 select sum(1) from table_name
3 select count(1) from table_name
4 select rows from sysindexes where object_name(id)='table_name' and indid<2
5 exec sp_spaceused 'table_name'
6 DBCC CHECKTABLE('table_name')

PS

To update rows column of sysindexes table, you need to run this before executing methods 4 and 5

DBCC UPDATEUSAGE ('database_name','table_name') WITH COUNT_ROWS.

Published 02 November 2007 09:46 by Madhivanan
Filed under: , ,

Comments

# re: Different ways to count rows from a table

03 November 2007 09:07 by tonyrogerson

Hi Madhivanan,

Don't forget you need the WITH COUNT_ROWS option in order to update the row counts in the system tables otherwise it will just update the reserved and used columns.

Also note, 4 and 5 are never guarenteed to be correct so can be wrong again soon after DBCC UPDATEUSAGE.

1 and 3 are the same, the optimiser will give the same plan underneath.

Tony.

# re: Different ways to count rows from a table

05 November 2007 07:14 by Madhivanan

Thanks Tony

I updated WITH COUNT_ROWS in my post

# Is the rows figure stored in sysindexes accurate

06 November 2007 22:01 by SimonS Blog on SQL Server Stuff

It wasn't guaranteed to be in SQL 2000 and earlier, but work was done in SQL 2005 that should mean that

# re: Different ways to count rows from a table

07 November 2007 09:52 by GrumpyOldDBA

agree with Simon - sys.partitions is the better way to go. sysindexes may not be supported going forward?