Alternative way to get the tables row count

Question

Alternative way to get the tables row count

SSCommitted

Points: 1810

December 14, 2004 at 9:21 am

I have read some IT publications were journalists say that there could be an alternative and effective way to count a table’s rows; rather than use SELECT COUNT(*) FROM MYTABLE …………[1] is

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('MYTABLE') AND indid < 2  …… [2]

However, unfortunately, some times it does not work fine for me.

I used DTS in Vb6.0  to load Data Form Text File to SQL SERVER 2000 DB.

After that if I used the method [2] or right click on the table from EnterPrise Manager then click on Properties then it shows rows as 1841977

Whereas the query [1] produced the result  as  1842251.

Can any one help me to get rid from this confusion?

Thanks in advance

Muntasir

..Better Than Before...

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

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

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 1

Update the statistics on the table. This can get out of synch. Also, you should have a clustered index on the table (indid =1), which ensures accuracy most of the time.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 2

sysindexes is used strictly for a close enough row count for the indexes themselves. It was not meant to be queried off of for row count. You could possibly add an IDENTITY field and then do MAX on that but again if rows are DELETED this woulod be off as well....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

jxflagg Hall of Fame Points: 3817 More actions · Answer 3

It takes time for SQL Server to update the usage. You can kick off the process yourself with DBCC UPDATEUSAGE.

Gopal-122691 Mr or Mrs. 500 Points: 517 More actions · Answer 4

Try this.

declare @rc int

EXEC sp_table_validation 'tablename', @rc OUTPUT, @rowcount_only=1

select @rc

hfjordan SSC Veteran Points: 261 More actions · Answer 5

hfjordan

SSC Veteran

Points: 261

December 15, 2004 at 10:07 am

#533989

What about sp_spaceused?

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

sp_spaceused uses the numbers from sysindexes, so it will be just as inaccurate. sysindexes can be seriously inaccurate. Ever see databases with negative free space? I just had a problem today where DBCC UPDATEUSAGE dropped the rowcount on a table from over 1 million to 127. It was performing abysmally (before UPDATEUSAGE) because it was choosing bad execution plans based on the bad count.

One good use I've found for sysindexes is for checking the progress of a large insert. Even if the destination table is locked exclusively, "SELECT reserved / 128.0 AS MegaBytes WHERE ID = OBJECT_ID('MyTable') AND indid < 2" will show how much data is in the table. The rowcnt field will not be updated until the insert completes, but reserved is updated continuously. As long as the MegaBytes value is increasing you know your remote query hasn't gotten hung up.

muntasiriub SSCommitted Points: 1810 More actions · Answer 7

Hi,

It is working like as below:

dbcc updateusage ('Mydatabase','MYTABLE') WITH COUNT_ROWS

GO

select rows FROM sysindexes WHERE id = OBJECT_ID('MYTABLE') AND indid < 2

…[2]

But it takes longer time than select count(*) from MYTABLE …[1]

For instance;

My table has 15338371 rows

And [1] takes 30 seconds, Where as [2] takes 38 seconds on average.

So is there any way to check whether sysindexes is updated or not, then I may use the logic

If UPDATED THEN

select rows FROM sysindexes WHERE id = OBJECT_ID('MYTABLE') AND indid < 2

Else

dbcc updateusage ('Mydatabase','MYTABLE')

WITH COUNT_ROWS

GO

select rows FROM sysindexes WHERE id = OBJECT_ID('MYTABLE') AND indid < 2

..Better Than Before...

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

As others have said, sysindexes is for approximation only, it is not guaranteed to show the exact number of rows at any given time.

If your process requires that you find an exact count you can trust at all times, then the only way to get that is to do what you started with -

'select count(*) from myTable'.

/Kenneth

muntasiriub SSCommitted Points: 1810 More actions · Answer 9

muntasiriub

SSCommitted

Points: 1810

December 21, 2004 at 11:09 am

#534649

ok

Thanks to u ALL

..Better Than Before...