Submit My Question - Recent Questions - Recent Answer

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

[150 byte] By [dpeeth] at [2007-12-19]

«« report server compatability issues
»» Cannot view XML input using XSL style sheet

# 1

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

JensSuessmeyer at 2007-9-9 >

top of Msdn Tech,SQL Server,Transact-SQL...

# 2

SQL Server keeps the row count in sysindexes and it

can be retrieved there.

joeydj at 2007-9-9 >

# 3

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.

Shughes at 2007-9-9 >

# 4

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.

UmachandarJayachandran-MS at 2007-9-9 >

# 5

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?

dpeeth at 2007-9-9 >

# 6

See

http://toponewithties.blogspot.com/2004/08/let-us-count-them.html

Roji.P.Thomas at 2007-9-9 >

# 7

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.

UmachandarJayachandran-MS at 2007-9-9 >

# 8

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

Eisa at 2007-9-9 >

SQL Server

Transact-SQL

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

it's Archive about '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?

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Site Classified