Tech Sharing

Tech Sharing

Advertisement

Get the number of row in database table

By on April 22, 2009

Normally to get the number of row for a particular table, where we will do is as the following query:


select count(*) from Table_name


Yes, you may get your result very fast if your table is very small, but if your table is more then 100k rows, then you may face some delay on that. What the above query do is actually it run the full table scan to get the row count. The scaning can be Logically or Physically. Logically mean that the SQL server only scan from the catch memory, whereas physically scan mean that it really scan through your table.


Actually there is anther alternative way to get the total row count for a table. You can ultilize the sysindexes system table for this purpose.


There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of above one:


SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID(‘table_name’) AND indid < 2


There are physical read and logical read operations. A logical read occurs if the page is currently in the cache. If the page is not currently in the cache, a physical read is performed to read the page into the cache. To see how many logical or physical read operations were made, you can use SET STATISTICS IO ON command.


The example:

SET STATISTICS IO ON

GO

SELECT count(*) FROM tbTest

GO

SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘tbTest’) AND indid < 2

GO

SET STATISTICS IO OFF

GO





Result:

———–

10000


(1 row(s) affected)


Table ‘tbTest’. Scan count 1, logical reads 32, physical reads 0, read-ahead reads 0.

rows

———–

10000


(1 row(s) affected)


Table ‘sysindexes’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.






Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive

Comments

No Responses to “Get the number of row in database table”

Write a Comment

Captcha * Time limit is exhausted. Please reload CAPTCHA.

%d bloggers like this: