title: At what cardinality does SQL Server switch to an index scan (vs. seek)
body: <p>Assuming that a table contains sufficient information to warrant an index seek, at what cardinality will SQL Server (or PostgreSQL) opt for an index scan?</p>
  
  <p>The reason I ask this is I previously posted a question (<a href="https://stackoverflow.com/questions/4575230/two-radically-different-queries-against-4-mil-records-execute-in-the-same-time/4576303#4576303">link</a>) in which two queries performed at the same speed, yet one didn't attempt to use the index on the processed columns. After SQL Server suggested I put a <em>covering index</em> that <em>included</em> the columns being queried (it suggested this for both queries), I started looking for reasons as to why it would make such a strange suggestion.</p>
  
  <p>I experimented with making the indexes covering and composite, but both executed in the same time (we're talking 3 million rows).</p>
  
  <p>Finally I concluded it was because of the ultra-high cardinality of the data. Every row is unique. I am deducing this caused SQL server to choose an index scan. However, the query stated "WHERE Col1 > ? AND Col2 &lt; ?", so this is a little confusing.</p>
  
  <p>My questions are:</p>
  
  <ol>
  <li>At what cardinality will a RDBMS always opt for an index scan?</li>
  <li>Can anyone explain why SQL Server would not use the index when the WHERE statement would indicate this would make sense?</li>
  </ol>
  
  <p>I have attached the execution plan.
  <img src="https://i.stack.imgur.com/BPQlU.png" alt="alt text"></p>