Counting rows in an SQL table

19 Jun 2006 7:07 PM

Art

Hi,

I'm trying to count rows in each of the tables in an SQL DB. I tried the
following ExecuteScalar command:

"Select Count(*) from " & mTableName

The problem is that one of the tables is huge and I'm getting a time out.

I thought I could do this with a stored procedure using the TableName as a
parmeter, but I couldn't get this to work. I thought that I could somehow
use the metadata (maybe from GetSchema) to just ask for the row count. I
couldn't figure that out either.

Can anyone help me out with this?

Art

19 Jun 2006 7:52 PM

Brian Gideon

Art,

Try the following query.

SELECT
   rowcnt
FROM
   sysindexes
WHERE
   indid <= 1
   and [id] = OBJECT_ID('YourTableNameGoesHere')

I don't believe the value you get back is guarenteed to be accurate
though. You may have to execute DBCC UPDATEUSAGE to get the value to
match a SELECT COUNT(*) statement.

You'd get a better response if you posted this question in a SQL Server
group instead.

Brian

Art wrote:
Show quoteHide quote

> Hi,
>
> I'm trying to count rows in each of the tables in an SQL DB. I tried the
> following ExecuteScalar command:
>
> "Select Count(*) from " & mTableName
>
> The problem is that one of the tables is huge and I'm getting a time out.
>
> I thought I could do this with a stored procedure using the TableName as a
> parmeter, but I couldn't get this to work. I thought that I could somehow
> use the metadata (maybe from GetSchema) to just ask for the row count. I
> couldn't figure that out either.
>
> Can anyone help me out with this?
>
> Art

19 Jun 2006 8:05 PM

Art

Brian,

Thanks -- I'll give it a try. The reason I didn't post this in a SQL Server
forum is that I thought that the answer might be a .net ADO method.

For my current purpose I just need orders of magnitude of the tables, so
that if your method gives me close answers, that will be good enough.

Thanks again.

Show quoteHide quote

"Brian Gideon" wrote:

> Art,
>
> Try the following query.
>
> SELECT
>    rowcnt
> FROM
>    sysindexes
> WHERE
>    indid <= 1
>    and [id] = OBJECT_ID('YourTableNameGoesHere')
>
> I don't believe the value you get back is guarenteed to be accurate
> though. You may have to execute DBCC UPDATEUSAGE to get the value to
> match a SELECT COUNT(*) statement.
>
> You'd get a better response if you posted this question in a SQL Server
> group instead.
>
> Brian
>
>
> Art wrote:
> > Hi,
> >
> > I'm trying to count rows in each of the tables in an SQL DB. I tried the
> > following ExecuteScalar command:
> >
> > "Select Count(*) from " & mTableName
> >
> > The problem is that one of the tables is huge and I'm getting a time out.
> >
> > I thought I could do this with a stored procedure using the TableName as a
> > parmeter, but I couldn't get this to work. I thought that I could somehow
> > use the metadata (maybe from GetSchema) to just ask for the row count. I
> > couldn't figure that out either.
> >
> > Can anyone help me out with this?
> >
> >                     Art
>
>

19 Jun 2006 8:24 PM

Art

Brian,

I just wanted to let you know that I tried it and it appears to work great!
Thanks again.

Art

20 Jun 2006 11:11 AM

Andrew Morton

Art wrote:

> I just wanted to let you know that I tried it and it appears to work
> great! Thanks again.

Although you've found a suitable method, it might be worth pointing out that
you don't have to retrieve everything from the database to count it the
rows: count(1) gives the same answer as count(*).

Andrew

20 Jun 2006 1:24 PM

Brian Gideon

Andrew,

SQL Server generates the exact same execution plan for either one. At
least it did for me and I tried it on a dozen tables each with
different kinds of indexes. It was always doing an index or table
scan.

Brian

Andrew Morton wrote:
Show quoteHide quote

> Although you've found a suitable method, it might be worth pointing out that
> you don't have to retrieve everything from the database to count it the
> rows: count(1) gives the same answer as count(*).
>
> Andrew

20 Jun 2006 2:26 PM

Andrew Morton

Brian Gideon wrote:

> Andrew,
>
> SQL Server generates the exact same execution plan for either one. At
> least it did for me and I tried it on a dozen tables each with
> different kinds of indexes. It was always doing an index or table
> scan.

Agreed. I can't remember exactly where I read about using count(1) instead
of count(*), and I was unfortunate to find it rather than an article saying
"don't bother". Apparently it's written in quite a lot of places. I, for
one, won't be writing it again.

Andrew

20 Jun 2006 1:30 PM

Andrew Morton

Andrew Morton wrote:

> Although you've found a suitable method, it might be worth pointing
> out that you don't have to retrieve everything from the database to
> count it the rows: count(1) gives the same answer as count(*).

Hmmm... that may not be at all useful. I tried Query Analyzer with both
methods with "set statistics io on" and there didn't seem to be any
difference.

Andrew