| Author |
Topic |
|
smeira
Starting Member
6 Posts |
 Posted - 2008-03-11 : 18:46:54
|
Hi,What catalog table has the  number of rows of a table ? |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-03-11 : 20:08:36
|
| select count(*) from table is the only reliable way. |
 |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-11 : 21:39:16
|
quote:
Originally posted by LoztInSpace
select count(*) from table is the only reliable way.
What makes you say that?the information exists in the server..sp_spaceused ('tablename') will return the information the OP wants.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-12 : 02:39:32
|
And soon Madhivanan will tell you about another approach.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2008-03-12 : 14:59:51
|
quote:
Originally posted by dataguru1971What makes you say that?the information exists in the server..sp_spaceused ('tablename') will return the information the OP wants.
But sp_spaceused relies on statistics being up to date, doesn't it? Or am I remembering wrong... (It's entirely possible I am, of course.  ) |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-12 : 16:04:24
|
I was more surprised by "only reliable way." There has to be more than 1 reliable way...
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-03-12 : 21:03:29
|
quote:
Originally posted by dataguru1971
What makes you say that?
... I was more surprised by "only reliable way." There has to be more than 1 reliable way...
Why would it be necessary to have two ways of achieving the same thing?quote:
Originally posted by WenW
But sp_spaceused relies on statistics being up to date, doesn't it? Or am I remembering wrong...
sp_spaceused is not transactional and does not take into account all the current activity. It appears to be a read-uncommitted type of operation - i.e. not reliable
I maintain that as count(*) is part of the language it is the only way guaranteed to provide the count in the context of ACID. The count of rows in a table is only ever relevant in the context of your transaction. Every other "row count" is under the covers housekeeping/internals and is either an approximation and will not take transactions into account. Hope this clears up my point. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-03-12 : 21:08:09
|
here's another "reliable" wayselect count(666) from table
elsasoft.org |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-12 : 21:14:21
|
quote:
Originally posted by LoztInSpace The count of rows in a table is only ever relevant in the context of your transaction. Every other "row count" is under the covers housekeeping/internals and is either an approximation and will not take transactions into account. Hope this clears up my point.
makes sense. Thanks for making that point!
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2008-03-13 : 16:09:27
|
quote:
Originally posted by LoztInSpacesp_spaceused is not transactional and does not take into account all the current activity. It appears to be a read-uncommitted type of operation - i.e. not reliable
Ummm... Isn't that what I said?  |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-03-13 : 21:08:02
|
| Yes. I was sort of tightening up the explanation but I managed to make it look like I was contradicting you, which I wasn't. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-13 : 21:58:32
|
| sp_spaceused or any other methods that relies on sysindexes is not reliable as sysindexes can be inaccurate. That's why we have DBCC UPDATEUSAGE. But as soon as data changes, the inaccuracies in sysindexes are there again.So the only accurate way to get the table count is to use COUNT.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Number of table rows in the catalog