Row Count

Question

Row Count

MuditGupta

SSCrazy

Points: 2596
More actions
October 2, 2008 at 7:54 am

#199199

Hi,
I am trying to get # of rows for a table.
I used sp_spaceused, through which I get a number which is different compared to number I get when I use SELECT * FROM TABLE.
Can anyone help me in understanding process going on behind these commands? AND which way is more accurate?
I know SELECT * is a performance-kill. But this is a small table so,it doesn't matter..
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

sp_spaceused gets it's row count from the partition statistics rather than counting the actual numbers of records. If your statistics are not up to date, the number may be wrong.

MuditGupta SSCrazy Points: 2596 More actions · Answer 2

Ok, it makes sense now.

Thanks.

It seems same is true for checking Table properties...When we right click on a table from object explorer.

the row count is same as the one I get from sp_spaceused.

jacob sebastian SSChampion Points: 11822 More actions · Answer 3

I once wrote a function that returns the number of rows that one or more tables contain. You can find the code here: http://www.sqlserverandxml.com/2008/01/find-row-count-of-one-or-more-tables.html

.

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 4

jacob sebastian (10/3/2008)

I once wrote a function that returns the number of rows that one or more tables contain. You can find the code here: http://www.sqlserverandxml.com/2008/01/find-row-count-of-one-or-more-tables.html%5B/quote%5D

Other approaches

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx

Madhivanan

Failing to plan is Planning to fail

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

sql-oholic (10/2/2008)
Hi,
I am trying to get # of rows for a table.
I used sp_spaceused, through which I get a number which is different compared to number I get when I use SELECT * FROM TABLE.
Can anyone help me in understanding process going on behind these commands? AND which way is more accurate?
I know SELECT * is a performance-kill. But this is a small table so,it doesn't matter..
Thanks

The reason why sp_spaceused shows a different number is because things have to happen to update the number. To force the update, you can use DBCC UPDATEUSAGE ('database_name','table_name') WITH COUNT_ROWS as Madhivanan's link advises. You can also use any one of the other tricks. The real key is that all of the methods are going to be slow if you don't have at least one proper index which should probably be a clustered index.

Using sp_space used is the least accurate method.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MuditGupta SSCrazy Points: 2596 More actions · Answer 6

Thank you ALL for your time and inputs. It has helped me in understanding the concepts...

Thanks