Counting rows in a table quickly

One of my friends asked me if there is a way to find total rows in a table without using COUT(*) as it takes very long time for big table. Well COUNT(*) is the most accurate way of counting rows from a table.

However you can also use any of these methods to find the total rows if you want to know the approximate count very quickly.

1 Use sp_spaceused system stored procedure

EXEC sp_spaceused mytable

2 Use sys.partitions system view


select rows from sys.partitions
where object_id=object_id('mytable') and index_id<1

3 Use sys.dm_db_partition_stats  system view


select row_count from sys.dm_db_partition_stats
where object_id=object_id('mytable') and index_id<1

All of the above will get you the count quickly.

NOTE : The count is approximate and to have the accurate count you need to run

DBCC UPDATEUSAGE('mydatabase','mytable')

which will reports and corrects the row count inaccuracies. After running this you can run the above methods to get correct count

 

 

Advertisements

2 thoughts on “Counting rows in a table quickly

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s