Finding rows count in a table without Select...

Question

Post reply

Finding rows count in a table without Select...

Manish Mittal

SSC Veteran

Points: 207
More actions
March 24, 2010 at 7:27 am

#386572

Comments posted to this topic are about the item Finding rows count in a table without Select...

Viewing 15 posts - 1 through 15 (of 21 total)

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

Raju Lalvani Say Hey Kid Points: 695 More actions · Answer 1

Raju Lalvani

Say Hey Kid

Points: 695

March 26, 2010 at 2:12 am

#1140179

Hi, but are the results correct or approximate?

Johnc SSC Enthusiast Points: 159 More actions · Answer 2

This is an interesting (and much quicker) method but I found the figures returned don't correlate exactly with results from 'select count(*) from <table name>'

e.g.

Select count = 14459745; SP_ROWCOUNT = 14459739

Select count = 1907910; SP_ROWCOUNT = 1907541

This may not be too important in whole table rowcounts, but do you know why this is?

Cheers

John

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 3

Thats because statistics for the table is not updated

Fire 'update statistics tablename' and check the results again.

Regards,
Raj

http://Strictlysql.blogspot.com

Johnc SSC Enthusiast Points: 159 More actions · Answer 4

OK thanks. Of course that makes it a little less quick if you have to do an update statistics on the table before you can query it :ermm:

Johnc SSC Enthusiast Points: 159 More actions · Answer 5

Well I just ran update statistics and re-ran SP_ROWCOUNT. I still got the same figures, so not only does update stats take longer than select count... it doesn't update the rowcount stats either :exclamationmark:

Maybe I'll stick with select count...

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 6

Hello,

The script is fine but using MS_foreach is undocumented ! :hehe:

I prefer to use:

SELECT table_name,table_rows FROM (

select so.name as table_name, si.rowcnt as table_rows,ROW_NUMBER() OVER (PARTITION BY SO.NAME ORDER BY si.rowcnt DESC) AS RC

from sysobjects so

join sysindexes si on so.id = si.id and so.type = 'U'

)F WHERE RC = 1

ORDER BY table_name

HTH,

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 7

Hello,

The script is fine but using MS_foreach is undocumented ! :hehe:

I prefer to use:

SELECT table_name,table_rows FROM (

select so.name as table_name, si.rowcnt as table_rows,ROW_NUMBER() OVER (PARTITION BY SO.NAME ORDER BY si.rowcnt DESC) AS RC

from sysobjects so

join sysindexes si on so.id = si.id and so.type = 'U'

)F WHERE RC = 1

ORDER BY table_name

HTH,

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 8

@Johnc,

If you need accuarate numbers then go for count(*). If approximate stuff wud do then use sp_spaceused/SP_ROWCOUNT provided in the article.

Probably your Update stats took a sample scan. Try,

Update statistics tablename with fullscan.

In case if you need to understand statistics, please read two excellent articles

written by the great Gail Shaw 🙂

http://sqlinthewild.co.za/index.php/2008/11/04/what-are-statistics-and-why-do-we-need-them/

http://sqlinthewild.co.za/index.php/2008/11/13/identifying-inaccurate-statistics/

Regards,
Raj

http://Strictlysql.blogspot.com

Chris Howarth-536003 SSCommitted Points: 1775 More actions · Answer 9

I prefer to use system objects that aren't marked for removal from a future release of SQL Server, I prefer not to use undocumented stored procs, and I prefer accuracy in my results:

SELECTQUOTENAME(OBJECT_SCHEMA_NAME(ps.object_id))

+ '.' + QUOTENAME(OBJECT_NAME(ps.object_id)) AS TableName,

SUM(ps.row_count) AS row_count

FROM sys.dm_db_partition_stats ps

WHERE ps.index_id <= 1

AND OBJECTPROPERTY(ps.object_id, 'IsMSShipped') = 0

GROUP BY ps.object_id

ORDER BY TableName

Chris

garyg-755799 SSC Veteran Points: 228 More actions · Answer 10

From my testing of these scripts, the row counts are only accurate if the table has a clustered index.

Gary

Greg Milner SSCrazy Points: 2425 More actions · Answer 11

I believe the WHERE clause here:

Where indid < 2

means that it's on the clustered index. So wouldn't it alway be accurate regardless of statics, since that's the actual physical layout of the rows?

Let me know if that's not correct.

Thanks.

G. Milner

Greg Milner SSCrazy Points: 2425 More actions · Answer 12

Hi, Manish.

I turned an email I wrote -- based on your post -- to a colleague (ETL designer) into a blog post on my site here, and cited you. Hope that's OK.

Thanks.

G. Milner

garyg-755799 SSC Veteran Points: 228 More actions · Answer 13

Regarding the clause "Where indid < 2", if you have a clustered index there will be a row in sysindexes with indid = 1. If you do not have a clustered index, there will be a row in sysindexes = 0.

From my testing, only the tables with a clustered index (indid = 1) are accurate.

Gary

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 14

If you need more accurate results, try using DBCC UPDATEUSAGE first. If you haven't done it in awhile, it could take some good bit of time and will take less time the more you use it.

--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)