Madhivanan's TSQL Blog

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

Browse by Tags · View All

sql_server	217
t-sql	211
tsql	116
sqlserver	96
BRH	78
#SQLServer	66
#TSQL	56
SQL Server	34
function	11
SSMS	9

Archive · View All

August 2007	17
August 2010	8
June 2012	7
June 2011	7
November 2007	7
August 2012	6
May 2012	6
November 2011	6
August 2011	6
October 2011	6

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (389) · Preferences

Different ways to count rows from a table

Nov 2 2007 9:46AM by Madhivanan

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

Select count(*) from table_name

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

select sum(1) from table_name

select count(1) from table_name

select rows from sysindexes where object_name(id)='table_name' and indid<2

exec sp_spaceused 'table_name' 

DBCC CHECKTABLE('table_name')

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.

Tags: t-sql, sql_server, row count,

Previous: Undocumented DATE and TIME related functions
Next: Easy way to generate number table

Madhivanan

3 · 39% · 12472

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

3 Comments

Nice to know there are so many different ways to get a count. Doing things in a different and a unique way makes us stand apart from rest of the crowd. Great info Madhivanan.

commented on Jun 25 2010 1:16PM

Dattatrey Sindol (Datta)

43 · 4% · 1333

Hello! Based on this post, and with some independent research, I have written a brief article here - http://beyondrelational.com/blogs/nakul/archive/2011/04/04/measuring-the-number-of-rows-in-a-table-are-there-any-alternatives-to-count.aspx

The article attempts to compare the known alternatives of COUNT(*) in terms of performance (time) and accuracy. Hope you like it.

Thanks & Regards, Nakul Vachhrajani

Be Courteous. Drive responsibly. http://beyondrelational.com/blogs/nakul/default.aspx

commented on Mar 12 2011 2:47PM

Nakul Vachhrajani

4 · 33% · 10680

Datta and Nakul, thanks for your valuable feedbacks

commented on Oct 5 2012 3:55AM

Madhivanan

3 · 39% · 12472

Your Comment

Sign Up or Login to post a comment.

"Different ways to count rows from a table" rated 5 out of 5 by 2 readers +Madhivanan