473,320 Members | 2,073 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Counting rows in an SQL table

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
Jun 19 '06 #1
7 4353
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


Jun 19 '06 #2
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.

"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


Jun 19 '06 #3
Art
Brian,

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

Art
Jun 19 '06 #4
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
Jun 20 '06 #5
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:
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


Jun 20 '06 #6
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
Jun 20 '06 #7
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
Jun 20 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Matt Schroeder | last post by:
Does anyone know how to count how many rows are in a mysql table? This is what I have, but it doesn't work right: <? $db = mysql_connect("localhost", "username", "password");...
1
by: Lord Merlin | last post by:
How do I add up the values of the data from all of the rows returned? i.e - I have a DB, with 148 rows of client info, and I want to addup all the scores. Each entry's score differes. Here is the...
0
by: DavidC | last post by:
Anyone know how to count rows in a table depending on 2 columns, and different values within the second column. Basically i am trying to obtain the resultset example below from the table example...
16
by: walexand | last post by:
I use the database mysql v.4. My problem is... I have a select like: select * from user where language = "de"; the result are then: id name =================== 1 max
3
by: Viswanatha Thalakola | last post by:
Hello, Can someone point me to getting the total number of inserts and updates on a table over a period of time? I just want to measure the insert and update activity on the tables. Thanks....
2
by: Adam | last post by:
In my MYISAM table I have an index (Index_A) on 2 fields (Field_A, Field_B). There are millions of rows in the table. The cardinality of Index_A is 53. I think a query to count the number of rows...
3
by: Megan | last post by:
hi everybody- i'm having a counting problem i hope you guys and gals could give me some help with. i have a query that retrieves a bevy of information from several different tables. first let...
3
by: David | last post by:
Hi, I have a table that for ease has this data in: R1, R2, R....z --------------------- A | 12 A | 22 A | 30 B | 0 B | -1 B | -3
0
by: josur | last post by:
Hello, Im a new user of SSAS and at the moment im trying to browse a cube to output the total of contacts within a criteria. I have one measure(fact table) named Contacts Count and 4...
2
by: akadeco | last post by:
Hi I need to write a script that will allow me to check the value of any link in a table cell. The desired result would be something like this: row.cell.innerHTML, allowing me to call the contents...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.