Finding rows count in a table without Select...

Question

Post reply

Finding rows count in a table without Select...

Viewing 7 posts - 16 through 21 (of 21 total)

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

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

DBCC UPDATEUSAGE - doesn't that only have to be run on SQL 2000 or upon upgrade from 2000 -> 2005?

I thought the issue had been corrected in 2005 onwards?

DBCC UPDATEUSAGE BOL quote:

"In SQL Server 2005, these values are always maintained correctly. Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 may contain invalid counts."

As I mentioned before, using sys.dm_db_partition_stats is the most reliable, future-proof method.

Chris

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

Chris Howarth-536003 (3/27/2010)
DBCC UPDATEUSAGE - doesn't that only have to be run on SQL 2000 or upon upgrade from 2000 -> 2005?
I thought the issue had been corrected in 2005 onwards?
DBCC UPDATEUSAGE BOL quote:
"In SQL Server 2005, these values are always maintained correctly. Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 may contain invalid counts."
As I mentioned before, using sys.dm_db_partition_stats is the most reliable, future-proof method.
Chris

Actually, that's exactly correct, Chris. I've been stuck in a 2k world for much too long. Being in a bit of a hurry, all I saw was a reference to sysindexes and the word "inaccurate" and didn't realize folks were talking of 2k5+.

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

Johnc SSC Enthusiast Points: 159 More actions · Answer 3

DBCC UPDATEUSAGE - doesn't that only have to be run on SQL 2000 or upon upgrade from 2000 -> 2005?

Actually that may provide the explanation. My original post was about inaccurate counts in a 2000 database I had migrated to 2008 and I didn't know about the caveat, so that may well be the answer.

Thanks Jeff, Chris (and All)

Yanko Nikolov SSC Enthusiast Points: 187 More actions · Answer 4

Hello

This is the cool example, but you do not need to write own procedure to determine the tables row count. Just try this:

exec SP_MSFOREACHTABLE @command1=N' exec SP_SPACEUSED ''?'' '

Regards

Yankov Yanko

krassimir.kotzev Newbie Points: 7 More actions · Answer 5

And th equestion again: How recent are your statistics, indexes, etc. ? If you can not tell, better use count(*) to get the numbers. It is slow but accurate.

Z.A.T SSC Enthusiast Points: 135 More actions · Answer 6

Z.A.T

SSC Enthusiast

Points: 135

April 15, 2010 at 3:56 am

#1151981

Well....

just used

sp_spaseused <tableName>

Dmitriy Burtsev Ten Centuries Points: 1188 More actions · Answer 7

exec sp_helptext 'sp_spaceused'

It is query sys.dm_db_partition_stats