title: Get records count from sql server table in the most optimized way
body: <p>I want to get the records count from an sql server table with more than 100 000 records, I'm using the <code>Count(Id)</code> method, but it take a lot of time (about 7 to 8 secondes), is there any way to make it faster, knowing that I am using a where clause in my <code>Select</code></p>
  
  <pre><code>SELECT Count(StudentID) WHERE Type = 1 AND Name Like %Sid%
  </code></pre>
  
  <p>I found that we can use the following method </p>
  
  <pre><code>SELECT rows FROM sysindexes 
  WHERE id = OBJECT_ID('dbo.StudentID') AND indid &lt; 2
  </code></pre>
  
  <p>But unfortunately I can not add my WHERE clause to this. Is there away to do it ?</p>
  
  <p>My Index definition for the Type field : <strong>(Non unique, non cluster)</strong></p>