title: Can I optimize a SELECT DISTINCT x FROM hugeTable query by creating an index on column x?
body: <p>I have a huge table, having a much smaller number (by orders of magnitude) of distinct values on some column <code>x</code>.</p>
  
  <p>I need to do a query like <code>SELECT DISTINCT x FROM hugeTable</code>, and I want to do this relatively fast.</p>
  
  <p>I did something like <code>CREATE INDEX hugeTable_by_x ON hugeTable(x)</code>, but for some reason, even though the output is small, the query execution is not as fast. The query plan shows that 97% of the time is spent on Index Scan of <code>hugeTable_by_x</code>, with an estimated number of rows equal to the size of the entire table. This is followed by, among other things, a Hash Match operation.</p>
  
  <p>Since I created an index on column <code>x</code>, can I not expect this query to run very quickly?</p>
  
  <p>Note that I'm using Microsoft SQL Server 2005. </p>