title: select count(1) vs select rowcnt from sysindexes performance and use?
body: <p>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.</p>
  
  <p>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?</p>
  
  <p>Edited: No of rows in table nearly 20000 in this case</p>
  
  <pre><code>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 &lt; 2 -- 5%
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/1lyZf.png" alt="enter image description here"></p>