Geek Answers Handbook

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

I read in .NET that using case 2 is faster than case 1 to check for rows in a table. so I did a performance test like count (1) vs rowcnt from sys.sysindexes, which I found The second option is much better.

My question is, is it good to use CASE 2 in production code when I need to count the number of rows in a table in stored procedures or special queries, is there any chance this case 2 could fail?

Edited: The number of rows in the table is about 20,000 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

+4
source share
2 answers

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

+1
source

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

I tried this and it is true.

While you have opened a transaction, your count(*) will block if you do not use one of the isolation levels of snapshots, otherwise it will give you the correct, fixed value.

In addition, it should be good, handle bulk load, etc.

+1
source

More articles:


All Articles