Approximate row count?

Is there any way to use the statistics to get an approximate row count on a
large table without having to do a SELECT COUNT(*)?EXEC sp_spaceused 'table_name'
SELECT MAX(rowcnt) FROM sysindexes WHERE id = OBJECT_ID('table_name')
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
> Is there any way to use the statistics to get an approximate row count on
> a
> large table without having to do a SELECT COUNT(*)?|||Those will be more accurate, of course, if you first issue DBCC
UPDATEUSAGE(0) ...
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
> Is there any way to use the statistics to get an approximate row count on
> a
> large table without having to do a SELECT COUNT(*)?|||Try:
select
rowcnt
from
dbo.sysindexes
where
id = object_id ('MyTable')
and
indid in (0, 1)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
Is there any way to use the statistics to get an approximate row count on a
large table without having to do a SELECT COUNT(*)?|||Hi,
DBCC UPDATEUSAGE command will lock the table.
Use the below command to get the approximate table count:-
sp_spaceused <table_name>
Inacse if you have more descrepency then use the command UPDATEUSAGE which
scans the data pages in the table and makes any
necessary corrections to the sysindexes table regarding the storage space
used by each table
sp_spaceused <table_name>,@.updateusage = 'TRUE'
The above command will locks, so execute when the table access is less,
Thanks
Hari
SQL Server MVP
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
> Is there any way to use the statistics to get an approximate row count on
> a
> large table without having to do a SELECT COUNT(*)?|||Thanks for the quick response. Is there any way to do something similar, but
find out how many rows fit a particular value for an indexed (or nonindexed)
column?
"Aaron Bertrand [SQL Server MVP]" wrote:

> EXEC sp_spaceused 'table_name'
> SELECT MAX(rowcnt) FROM sysindexes WHERE id = OBJECT_ID('table_name')
>
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
>
>|||> Thanks for the quick response. Is there any way to do something similar,
> but
> find out how many rows fit a particular value for an indexed (or
> nonindexed)
> column?
No, values are stored in the table, so you need to query the table.
What is the actual problem? Does SELECT COUNT(*) take too long? How many
rows are there, is there a clustered index, and what kind of hardware is it?
I am currently looking at a table on a system here, with 70,000,000 rows,
and SELECT COUNT(*) comes back in a few seconds. Granted, I use the NOLOCK
hint for ad hoc stuff like this, and it is on enterprise-class hardware
(quad zeon, 16gb ram, emc clariion). But design is definitely going to have
a big impact here.
A|||> The above command will locks, so execute when the table access is less,
Yes Hari, I didn't mean to imply that anyone should run DBCC UPDATEUSAGE
before every query.sql