SQL Server Row Count – The Fastest Way

What's the fastest way to get the number of rows in a table in SQL Server 2005 or SQL Server 2008?  Performing a SELECT COUNT(*) on the tables will work but could have a serious performance impact if the tables are of any meaningful size.  Alternatively, a few lines of code hitting one of the system tables can get you the answer with minimal performance impact.  SQL Server consultants use the following script:

SELECT
OBJECT_SCHEMA_NAME(object_id)
,OBJECT_NAME(object_id)
,SUM(Rows) AS NumOfRows --sum the rows if there are multiple partitions
FROM
sys.partitions
WHERE
index_id < 2 --ignore the partitions from non-clustered indexes if any
GROUP BY
OBJECT_ID
ORDER BY
NumOfRows DESC

By Phil Steffek

Phil Steffek is a professional sumo wrestler who is hoping to represent the U.S. in the next Olympics. During the off season Phil is a Data Architect working on transactional databases, data warehouse implementations, and all aspects of business intelligence. Also, Phil is not really a sumo wrestler. You can get to know a bit more about my non-sumo doings by checking out my LinkedIn Page

Leave a comment

Your email address will not be published. Required fields are marked *