which is the most efficient query to find out the total number of rows in a table?

Hot Topic

SQL2005: Reource database
Why use anything other than varchar(max)
Decoding Decimal Form of HRESULT from ErrorCode
how to create sql server 2005 instance(after uninstalling sql server express)
OLE DB Command
Determining when to send the report based on information inside the report?!?
Full datetime evaluation
How do I force new databases to be created on another drive on same server?
Client-side programming patterns/idioms for SQL Mobile 2005?
Execute SQL from File: How Can I process the record set?

SQL Server

SQL express database + IIS
Client Failover Problems
RDL Spec 2005 pg 34 error
Can't debug DataFlow Script task
IP addresses: when SQL assigned multiple which does processing?
Single Database w/ Multiple Subscribers Merge
How to Connect to Analysis Services from Client Management Studio?
Stored Procedure using linked server...HELP
Assigning Keyboard Shortcut - Modifying default assignment for CTRL+S
General Network Error OpenXML

which is the most efficient query to find out the total number of rows in a table?

which is the most efficient query to find out the total number of rows in a table other than using - SELECT COUNT(*) ... in query

Answer this question

which is the most efficient query to find out the total number of rows in a table?

  • LuisLopezCarrete

  • Anilesh

    SQL Server keeps the row count in sysindexes and it

    can be retrieved there.



  • jmweekes

    Please do NOT rely on counters maintained in system tables. These are not guaranteed to be accurate or transactionally consistent. The only way to get an accurate count of the number of rows is to do a COUNT(*) query. Note that if you have multiple indexes on the table, SQL Server will automatically pick the smallest available index to count the rows. So the query will be as efficient as the size of the index.

  • Adam Cogan

    This is not really what you asked, but if you want to check to see if a query returned any results (you don't care how many), then IF EXISTS(SELECT 1 FROM .....) is quicker than COUNT(*) as it returns true as soon as a row is returned. COUNT(*) always counts all rows.

  • Shon

    It is used for internal purposes only. There is no guarantee that they will be maintained in real-time and the workings will not be documented. So you cannot rely on it for accuracy.

  • Agnes Pedoche

    Thanks for the reply. Are the counters in system tables not guaranteed to be accurate because of I/O problems like lost write or stale read

  • Matt150279

    I've posted this before
    http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=13097&SiteID=1



  • Amjith

    Why do you not weant to use the Count(*). I guess this is the best way to count the rows in the table. If it os too slow for you and you need immediate results rather than waiting for rleased locks you can get the *approximat* value with specific WITH(NOLOCK) after the table which you want to count. Sure this will count *all* even rows being involved in a transaction and might be deleted, but this would be a workaround.

    (In some cases you just need the approximat value.)


    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
  • which is the most efficient query to find out the total number of rows in a table?
Answer this question