How to quickly check the number of rows in a table in SQL Server
A common task in both database administration and development is to query the number of rows in a table. In this article I'll show you four different ways of querying the number of rows in a table efficiently.
Method 1 - The Intuitive
Perhaps the most intuitive and easiest way to check the number of rows in a table is to use the COUNT() function, such as SELECT COUNT(<field_name>) FROM <table_name>. Although this method returns a precise number of rows, it becomes slow in proportion to the number of rows in the table. This is due to the fact that the query engine will inevitably read ALL the rows in the table, and it doesn't matter whether the field used is a clustered or non-clustered index because there is no index benefit. Furthermore, if run on a table with a high workload, this type of query can cause excessive blocks. For this reason, Method 1 is only recommended for tables with few rows and/or in test or non-production environments.
SELECT COUNT(id) from Employees;
Method 2 - Fast but not precise
Selecting the rows field from the sys.sysindexes table is the fastest way to query the number of rows. However, such speed sacrifices the accuracy of the number of rows value, since it depends on whether the statistics are up to date. In addition, the sys.sysindexes table is in the process of being decommissioned and will be removed in a future version of Microsoft SQL Server.
SELECT rows FROM sys.sysindexes
WHERE id = OBJECT_ID ('<table_name>')
AND indid < 2;
Method 3 - Behind the scenes
This method is how SQL Server Management Studio queries the number of rows in a table under the hood. Look in Table Properties > Storage > Row count. It uses three different tables to show a more precise number of rows at the cost of adding JOINs to the query. Two parameters are required: (1) the name of the table and (2) the name of the schema.
SELECT CAST(p.rows AS float)
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int)
AND p.index_id=idx.index_id
WHERE ((tbl.name=N'<table_name>'
AND SCHEMA_NAME(tbl.schema_id)='<schema_name>'));
Method 4 - Fashionable
This is perhaps the most cost-effective method because it returns an approximate number of rows using a simple query to a Dynamic Management Views, in this case to sys.dm_db_partition_stats. DMVs provide the user with a mechanism for viewing information from the Database Engine, called SQLOS (SQL Operating System), which was introduced in SQL Server 2005, in a fast, simplified and lightweight way.
SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('<table_name>')
AND (index_id = 0 or index_id = 1);
BONUS:
Method 5 - The Old Way Who Still Works
Why not comment on sp_spaceused? This system procedure was inherited from the Sybase code (if you don't know, SQL Server is derived from the Sybase code base). In addition to the number of rows, it also returns reserved disk space and disk space used by a table. In particular, I don't like using SQL Server's legacy procedures (but I'm not saying they're bad or don't work) but rather DMVs, as in Method 4, which are more modern and offer light and simplified access.
EXEC sp_spaceused N'dbo.Employees';
I know this is only SQL but I like it.