Why different values

Question

Why different values

ArthurC

SSCommitted

Points: 1685
More actions
June 18, 2007 at 2:58 pm

#179379

Hi all,
Anyone out there has an idea why there are different values between table Table Properties (Row count) and the return value from a select count(*)?
Thanks,
AC

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

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

ArthurC SSCommitted Points: 1685 More actions · Answer 1

I should add extra information about my question. It happens for those tables with large number of records, let say more than 1 millions. The values are matching in smaller size tables. Funny!!!

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

I don't think there should be differences.

while the aggregate usually don't count NULLs, count(*) counts everything including nulls and duplicates.

BOL says: "COUNT(*) returns the number of rows in a specified table without getting rid of duplicates. It counts each row separately. This includes rows that contain null values."

Regards,Yelena Varsha

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

I just ran Profiler for the opening of properties. It gives us the following for the Row Count (this is a part of Select ). If I read it correctly it adds number of rows for this table from sys.partitions. Maybe your big tables have more then one partition?

select ............................, ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and

spart.index_id < 2), 0) AS [RowCount]

Regards,Yelena Varsha

ArthurC SSCommitted Points: 1685 More actions · Answer 4

I cannot run a profiler because I am not the DBA, I am a Database Developer. How can I find out whether the tables has been partitioned? I ran the query you provided by it returned with an error "Invalid object name 'sys.partitions'." The size of the smallest big table is over 7 millions records. Again, the figures are matching with small tables.

I right click on a table and select Properties, then a Table Properties window pop-up. That is where I got the figure that is not matching from a SELECT COUNT(*) FROM table statement.

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 5

The difference between select count(*) and Table Properties (Row count) usually occured in 2000. Probably you upgraded your database from 2000. Check your compatibility level.

In order to fix this difference run DBCC UPDATEUSAGE - it will update rowcnt and rows in sys.sysindexes

ArthurC SSCommitted Points: 1685 More actions · Answer 6

Many thanks Mark, you right that the compatibility level of my SQL database is up to SQL Server 2000.