For training related to .net,OOPS,design patterns, MVC,WCF and .net fundamentals
contact at sukeshmarla@gmail.com or Click Here

Click here for my training feedbacks

Followers

Follow Us



Share your Interview questions here


For software development queries contact SukeshMarla@Gmail.com

Follow by Email

Learn everything about JSON in a day

27 October 2010

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.