count of rows in table is not accurate.

Question

Post reply

count of rows in table is not accurate.

hemant789

Ten Centuries

Points: 1052
More actions
July 29, 2009 at 6:55 am

#208839

I am using below mentioned query to find out the count of rows in table.
select a.name,indid, b.rowcnt from sysobjects a
join sys.sysindexes b
on a.id=b.id
where a.name='test'
and INDID IN (0,1,255)
but the result of above query and below mentioned query are not same.
select count(1) from test
Can anyone explain the reason?
I want to get total number of rows in table without using count function.

Viewing 11 posts - 1 through 10 (of 10 total)

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

i'm not sure but see if updating statistics of the table helps...

Pradeep Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

The count in sysindexes can be wrong and should never be considered anything other than an approximate value

Since you're on 2005, try using the row count in sys.partitions. It's also not guaranteed to be always 100% accurate but it should be close. If you want a 100% accurate count, you'll have to count the table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Allister Reid SSCrazy Points: 2666 More actions · Answer 3

Allister Reid

SSCrazy

Points: 2666

July 29, 2009 at 7:06 am

#1031639

Does sp_spaceused work for 2005?

ps. One Orange Chip Points: 29422 More actions · Answer 4

it works even in 2008 but will show only the disk space used in the table.

Pradeep Singh

Allister Reid SSCrazy Points: 2666 More actions · Answer 5

Are you sure? http://msdn.microsoft.com/en-us/library/ms188776(SQL.90).aspx... reason I asked was I don't have a 2005 instance to test, but according to BOL it gives number of rows.

///edit...

You have to include object name e.g.

sp_spaceused 'test';

ps. One Orange Chip Points: 29422 More actions · Answer 6

it does provide the number of rows but i'm not sure whether it picks up the results from the statistics or the actual count. If it provides result from the statistics, chances are it wont be accurate all the time.

Pradeep Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

It comes from neither statistics nor an actual count. A look at the definition of sp_spaceused shows that the row count is calculated from sys.dm_dm_partition_stats.

It's probably the same as sys.partitions, mostly accurate but not 100% guaranteed to be so.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

ps (7/29/2009)
i'm not sure but see if updating statistics of the table helps...

Not update statistics. The counts in sysindexes are not statistics based, they come from the storage engine. You need updateusage.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ps. One Orange Chip Points: 29422 More actions · Answer 9

Thanks Gail. 🙂

Definately i need to google more on this topic 😉

Pradeep Singh

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

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