Best way to count rows in a table

One ITKnowledge Exchange member is seeking the best way to determine the number of rows in a table -- but his peers wonder why they need to be counted at all. Get advice here.

The following question was posed to ITKnowledge Exchange members. Read the problem then peruse some of the responses.

Click here for the complete discussion.

Question

Member "manickam" writes: "To determine the number of rows we use a count(*) function on a table, but doing so hurts the performance of our tables with large rows. We will use sysindexes to get the number of rows in table, but sometimes the results of the two queries differs. Can anyone help?

eg: table_name = table1
number of rows = 20

query1: select count(*) from table1; results:20
select rows from sysindexes where name = table1;
results:20

Answer #1

Member "rlreid" writes: "Depends. sp_spaceused is a possibility, with caveat.

In my shop, the first question I would ask is, "Wy are you counting the rows"? I don't know your application but it is rare to actually need an exact count of the rows and it does take a table scan.

The sysindexes approach has nothing to recommend it. If an estimate is good enough, use "sp_spaceused" and look at the "rows" value. This is not always an exact count of the number of rows, but its close enough for a lot of things.

As to avoiding count? Some folks use "count" instead of "exists." If "exists" is all you need, it is much faster. Are you doing a count to do memory allocation? If the table is so big, why put it in local memory? And if there's a good answer to that, why not do reallocs as needed, say every 10,000 rows?

You need to understand why you want to do a count, and then see if you can avoid it. If you must have an exact count, then you must scan the table and count the rows (and be sure nothing changes while you do whatever it is you want to do with the count).

Otherwise, use sp_spaceused, or redesign to not need the count."

Answer #2

Member "Eolianne" writes: "It would be slightly faster if you did a SELECT COUNT(TABLEID) FROM TABLE instead of SELECT COUNT(*) if your table has a lot of columns."

Answer #3

Member "yfellow" writes: "DBCC UPDATEUSAGE. Please refer to BOL for more information."

Click here for the complete discussion.

This was first published in May 2006

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close