If you want to get the row count of a table, you may think the best option is just throw down the old SELECT COUNT() But as DBA or a Developer you should know there’s a better way! Using either the DMVs or the Object Catalog Views so we aren’t putting the lock or doing a scan on index/page to a production table.


FROM [dbo].[DimDate]

DMV option:

SELECT row_count
FROM sys.dm_db_partition_stats
WHERE   object_id = OBJECT_ID('[dbo].[DimDate]')

Object Catalog View:

SELECT  rows
FROM    sys.partitions
WHERE   object_id = OBJECT_ID('[dbo].[table]')

Object Explorer Details

This is a upsetting one, where it can show the wrong results, so dont rely on it!

There’s a connect for the issue (Link below) which was for SQL 2008 r2 but it still remains in 2012.