Which is the best way to count number of records in a table.

In SQL Server 2005 and above, which query is the fastest and most reliable for returning an accurate record count?

• Select Count(*) from DB1.Table1
• Select Convert(bigint,[rows]) from Sys.sysindexes where id=Object_Id(DB1.Table1) and inid<2;
• select cast(p.rows as float) from sys.tables 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'Table1' and SCHEMA_NAME(tbl.schema_id)='DB1'
• Select Sum(row_count) from Sys.dm_db_partition_stats where object_Id=OBJECT_ID('DB1.Table1') and (index_id=0 or index_Id=1)

Reviewing the execution plan of all 4 queries as a batch reveals that Query 1 is the slowest costing about 90% of the total run time.

Query 3 is the next slowest costing about 9% of the total run time.

Queries 2 and 4 cost approximately the same amount of the total run time but Query 2 uses the sysindexes table, which may not be as reliable because the accuracy will be determined by when the statistics have been updated. In addition, Microsoft says sysindexes will be removed in future versions.

This makes Query 4 the fastest and most reliable way for returning an accurate record count.