A quick little tip I found a couple of years ago. Ever use the count(*) syntax and it takes forever to count all the rows in a table? I had this problem when I would sometimes run counts on a bunch of tables to check that my replication was working correctly. Here is a much, much quicker way using the DMV sys.dm_db_partition_stats:
SELECT SUM(row_count) as TotRows FROM sys.dm_db_partition_stats WHERE object_name(object_id) = 'YourTableName' AND index_id < 2
Running the count(*) on one of my large tables took 33 seconds. The above statement took 1 second.
If you want to do a record count on all tables in a database:
SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count FROM sys.dm_db_partition_stats st WHERE index_id < 2 ORDER BY st.row_count DESC
Another little known way to get table counts quickly is in SSMS click View -> Object Explorer Details, then click on the “Tables” folder for any database. By default you will see all the table names, but not the row count. To get that, right-click on a column header and select “Row Count”.