Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
 Posted - 2004-05-27 : 04:12:32
|
| Since for the large tables count(*) is taking considerable time, following query was suggested to me by a friend.select max(rowcnt) from sysindexes where id in (select id from sysobjects where name='TableName')I find that this works fine most of the time. But some time i am not getting the proper count. I have not seen any where MS has recommended this. Is it a correct approach.?Thank you.------------------------I think, therefore I am |
|
|
srinivasanr
Starting Member
15 Posts |
Posted - 2004-05-27 : 05:48:31
|
| Hi,This will fetch you results very fast..SELECT rowcnt FROM sysindexes WHERE id = OBJECT_id('TableName')AND indid < 2Wallops!!! |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-05-27 : 06:38:47
|
quote:
Originally posted by ravilobo
some time i am not getting the proper count
I've heard that too.Found thisquote:
The number of rows value (rowcnt column) is maintained only for index number 0 or 1 (heap or clustered index) and it is replicated across all indexes on the table.http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp
andquote:
A table will only have an entry in sysindexes with an indid value of for 0 or 1, never both. That's the entry that we're interested in because its rowcnt field gives is the number of rows in the table...."If you don't need an exact answer..." That's because there are times when rowcnt is not the exact number of records in the table. This can be corrected by updating statistics on the table with:dbcc updateusagegohttp://db.ittoolbox.com/documents/document.asp?i=2551
I expect the DBCC stuff will take as long as a SELECT COUNT(*) :-(Kristen |
|
|
|
|
|
|
Record count for large tables