Sybase : How to count number of rows in a table – demystified

Today I’ve encountered a great forum post explaining how to count the number of rows in a table “the right way” by Derek Asirvadem.

So, it’s not:

SELECT COUNT(*) FROM mytable

it is not even:

SELECT COUNT(1) FROM mytable

can be slightly better by hinting the usage of an index:

SELECT COUNT(*) FROM mytable (INDEX column_1_index)

the right way is to do:

SELECT ROW_COUNT(DB_ID(), OBJECT_ID("mytable") [, partition_id ])

because it uses sybstats, no I/O.

I knew about it, but the documentation discouraged me from using this approach. Take a look:

sp_spaceuseddisplays estimates of the number of data pages, space used by a specified table or by all tables in the current database, and the number of rows in the tables. sp_spaceused computes the rowtotal value using the rowcnt built-in function. This function uses a value for the average number of rows per data page based on a value in the allocation pages for the object. This method is very fast, but the results are estimates, and update and insert activity change actual values. The update statistics command, dbcc checktable, and dbcc checkdb update the rows-per-page estimate, so rowtotal is most accurate after one of these commands executes. Always use select count(*) if you need exact row counts.

What I’ve found out reading the forum post, is, that if you really, really, need an exact value in current moment, you should perform these just before ROW_COUNT():

EXEC sp_flushstats [Table]
-- or
UPDATE TABLE STATISTICS [Table]

Thanks Derek!

1 thought on “Sybase : How to count number of rows in a table – demystified

Leave a Reply

Your email address will not be published.