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 Friday, November 2, 2007 9:46 AM by Madhivanan
Filed under: , ,

Comments

# re: Different ways to count rows from a table

Saturday, November 3, 2007 9:07 AM 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

Monday, November 5, 2007 7:14 AM by Madhivanan

Thanks Tony

I updated WITH COUNT_ROWS in my post

# Is the rows figure stored in sysindexes accurate

Tuesday, November 6, 2007 10:01 PM 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

Wednesday, November 7, 2007 9:52 AM by GrumpyOldDBA

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

# Rowcounts on all tables in a DB | keyongtech

Sunday, January 18, 2009 4:53 PM by Rowcounts on all tables in a DB | keyongtech

Pingback from  Rowcounts on all tables in a DB | keyongtech

# How to list all non-empty tables | keyongtech

Thursday, January 22, 2009 8:52 AM by How to list all non-empty tables | keyongtech

Pingback from  How to list all non-empty tables | keyongtech