Number of rows in a table

Hi,
Is there a quicker way to get the number of rows in a table other than the
select statement? I did see that there is a table object but there is no
detailed examples in the books online. Thanks for any help.
EllieP.S. I should have mentioned that my select statement selects a recordset of
the entire table and then gets the recordcount.
"Ellie" <nospam@.nospam.net> wrote in message
news:Op3cPtDcGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Is there a quicker way to get the number of rows in a table other than the
> select statement? I did see that there is a table object but there is no
> detailed examples in the books online. Thanks for any help.
> Ellie
>|||try this..
hope this helps..
select rowcnt from sysindexes where indid in(0,1) and id =
object_id('<table_name>')|||If you need to get an accurate count in SQL 2000, use SELECT COUNT(*). This
is more efficient that the recordset method, unless you need the recordset
for other reasons anyway.
The sysindexes query omnibuzz suggested will return an approximate count in
SQL 2000. The count is accurate in SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellie" <nospam@.nospam.net> wrote in message
news:O7AxfvDcGHA.4896@.TK2MSFTNGP03.phx.gbl...
> P.S. I should have mentioned that my select statement selects a recordset
> of the entire table and then gets the recordcount.
> "Ellie" <nospam@.nospam.net> wrote in message
> news:Op3cPtDcGHA.4032@.TK2MSFTNGP02.phx.gbl...
>|||Thanks Dan. You are right. Thanks for pointing it out.
But I have a doubt here. If auto_update_statistics is on for the database
can we be sure of the rowcount or we still cannot rely on it'
and How do you we make it reliable in SQL Server 2005. By giving the async
statistics update option'
--
"Dan Guzman" wrote:

> If you need to get an accurate count in SQL 2000, use SELECT COUNT(*). Th
is
> is more efficient that the recordset method, unless you need the recordset
> for other reasons anyway.
> The sysindexes query omnibuzz suggested will return an approximate count i
n
> SQL 2000. The count is accurate in SQL 2005.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ellie" <nospam@.nospam.net> wrote in message
> news:O7AxfvDcGHA.4896@.TK2MSFTNGP03.phx.gbl...
>
>|||You should run DBCC Updateusage before querying on sysindexes table
Madhivanan|||Updating of statistics doesn't have anything to do with having updated rowco
unt in the sysindexes
(or 2005 counterpart) tables/views. These are two separate things.
If auto update statistics would also be the one to update rowcnt, then it wo
uld have to be done
reading all rows, which would be disastrous for a clustered index on a table
with, say 10,000,000
rows.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:F4E4270C-B43B-4B20-81FF-944D88CC3885@.microsoft.com...
> Thanks Dan. You are right. Thanks for pointing it out.
> But I have a doubt here. If auto_update_statistics is on for the database
> can we be sure of the rowcount or we still cannot rely on it'
> and How do you we make it reliable in SQL Server 2005. By giving the async
> statistics update option'
> --
>
>
> "Dan Guzman" wrote:
>|||sp_spaceused @.updateusage=true will correct the row count. However, there
is no telling how long the count will remain good.
In SQL 2005, the engine does a better job of maintaining the sysindexes row
count as changes occur. Theoretically, @.updateusage is not needed to fix
the row count in SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:F4E4270C-B43B-4B20-81FF-944D88CC3885@.microsoft.com...
> Thanks Dan. You are right. Thanks for pointing it out.
> But I have a doubt here. If auto_update_statistics is on for the database
> can we be sure of the rowcount or we still cannot rely on it'
> and How do you we make it reliable in SQL Server 2005. By giving the async
> statistics update option'
> --
>
>
> "Dan Guzman" wrote:
>|||I think this explains to me why the Rows count that's displayed in
Enterprise Manager on the Table/Properties screen is sometimes
incorrect. I'm using SQL Server 8.
Are SQL Server 8 and SQL Server 2000 the same version? I'm running on
XP/Pro.
Ron|||Yes, version 8 = SQL 2000 and version 9 = SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"RonL" <sal_paradise_93@.yahoo.com> wrote in message
news:1146933523.780782.253580@.j33g2000cwa.googlegroups.com...
>I think this explains to me why the Rows count that's displayed in
> Enterprise Manager on the Table/Properties screen is sometimes
> incorrect. I'm using SQL Server 8.
> Are SQL Server 8 and SQL Server 2000 the same version? I'm running on
> XP/Pro.
> Ron
>sql