Total Rows on a table
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.
SELECT COUNT (*) FROM [dbo].[DimDate]
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.