February 16, 2006 at 3:15 pm
I have a Table "Table1" with two fields F1 and F2. F1 is having index and F2 don't have any index.
Which one is the best method to get total number of records in the table.
1. Select count(*) from Table1
2. Select count(1) from Table1
3. Select count(F1) from Table1
4. Select count(F2) from Table1
I was thinking that sp_spaceused is best way, but it does not give exact count and rowcount is coming always higher than actual number of rows in table.
Thanks,
Ramesh.
February 16, 2006 at 4:24 pm
As long as there is no WHERE clause on your query, then all 4 should produce the same execution plan and execute in the same time.
The plan will most likely be an index scan of the index on F1 (unless there is another index on the table that has a smaller column set).
February 17, 2006 at 1:37 am
Count(Field) will produce a count of the non-null values so you have to use count(*) or count(1) in any case.
Try running DBCC UPDATEUSAGE(0) then running sp_spaceused
February 17, 2006 at 5:27 am
What is the purpose to run Try running DBCC UPDATEUSAGE(0) then running sp_spaceused in this case.
February 17, 2006 at 7:27 am
I think DBCC UpdateUSage would update table statistics. sp_spaceused does not look into
actual table but looks into system tables to get count which are updated once we update
statistics.
Thanks,
Ramesh.
February 17, 2006 at 1:16 pm
If you look in sysindexes and look at any clustered indexes there is a rowcnt field that contains the approximate count for the number of rows in the tables to which the clustered index applies.
Amongst other things DBCC UPDATEUSAGE updates this value.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply