CODEWITHSUNDEEP
sqlsql-serverperformancesql-server-2008
Registered User
Registered User

Reputation: 1564

select count(1) vs select rowcnt from sysindexes performance and use?

I read on NET that using case 2 is more fast than case 1 to check no of rows in a table. so i did a performance test of both count(1) vs rowcnt from sys.sysindexes I found 2nd one is far better.

I've a question is it good to use CASE 2 in production code to whenever i need to to count no of rows in a table in Stored procedures or ad-hoc queries, is there any chances that case 2 may fail?

Edited: No of rows in table nearly 20000 in this case

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

--CASE 1
SELECT count(1) from Sales.Customer c -- 95%

--CASE 2
SELECT rowcnt
from sys.sysindexes s
WHERE id=object_id('Sales.Customer') AND s.indid < 2 -- 5%

enter image description here

Upvotes: 4

Views: 1669

Answers (2)

muhmud
muhmud

Reputation: 4604

According to this http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/9c576d2b-4a4e-4274-8986-dcc644542a65/ it reflects uncommitted data.

I've tried this, and it's true.

While you've got the transaction open, your count(*) would block if you weren't using one of the snapshot isolation levels, otherwise it would give you the correct, committed value.

Apart from that, it should be fine, handles bulk load, etc.

Upvotes: 1

Thilo
Thilo

Reputation: 262584

That system table only has the total number of rows in the table. So you cannot use it if you need to count any subset (i.e. have a WHERE clause).

Upvotes: 1

Related Questions