by Njål

Quick row count from Microsoft SQL Database

 

Recently, I’ve been involved in a cloud provider project where I had to do a row count on a Microsoft SQL Database table with more than 50.000.000 rows in it.

Doing

SELECT COUNT(*) FROM myTable

took about 10 seconds – since it performs a full table scan.

 

Here’s an alternative way (still accurate) of doing it that takes under 1 sec:

SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('myTable')
AND (index_id=0 or index_id=1);

 

You can run this from Microsoft Entity Framework as well:

long total = dbContext.Database.SqlQuery<long>("SELECT SUM (row_count) FROM sys.dm_db_partition_stats WHERE object_id=OBJECT_ID('myTable') AND (index_id=0 or index_id=1);").First();