title: Why Sql Indexed View always use Clustered Index
body: <p>I need some pointer on how to debug the following problem.</p>
  
  <p>Environment: SQL Server 2005 Enterprise.</p>
  
  <p>I have an indexed view with contains clustered index and multiple non-unique, non-clustered index. However when I execute the query, SQL server always perform Clustered index scan instead of index seek on my key.</p>
  
  <p>Here is a simplify version.</p>
  
  <pre><code>CREATE VIEW MyIndexedView WITH SCHEMABINDING
  SELECT a.Col1, b.Col2, c.Col3, d.Col4
  FROM a JOIN b on a.id = b.id 
         JOIN c on a.id = c.id
         JION d on c.id = d.id
  </code></pre>
  
  <p>There is a clustered index on Col1, and non-unique, non-clustered on Col2, Col3.</p>
  
  <p>When I run the following query</p>
  
  <pre><code>SELECT a.Col1, b.Col2, c.Col3 FROM MyIndexedView WITH(NOEXPAND) WHERE b.Col2='blah'
  </code></pre>
  
  <p>and look at execution plan, I see SQL server run Clustered index scan on a.Col1 instead of perform index seek on Col2.</p>
  
  <p>I tried to recreate the view and index.</p>
  
  <p>Updated:
  I did some additional testing and running these two queries side by side in Query Analyzer.</p>
  
  <pre><code>    a) SELECT a.Col1, b.Col2, c.Col3 
         FROM MyIndexedView WITH(NOEXPAND) WHERE b.Col2='blah'
  
      b) SELECT a.Col1, b.Col2, c.Col3
         FROM MyIndexedView WHERE b.Col2 = 'blah'
  </code></pre>
  
  <p>Query 'a' will take 95% of the time and use Cluster Indexed scan. Query 'b' will only take 5% of the time and use Index Seek on col2.  I try to swap the order of queries (run b first and a later) yield the same percentage.</p>
  
  <ul>
  <li>This little experiment confirm that if sql use index seek it will be faster then cluster index scan.</li>
  <li>Second I though if I don't include "WITH(NOEXPAND)" then SQL server will not use index on Indexed view.  (Maybe I should start another question on the exact step to create indexed view).</li>
  </ul>