title: Why isn't SQL Server using my index?
body: <p>In our database we have this table with 200.000 rows</p>
  
  <pre><code>CREATE TABLE dbo.UserTask (
      UserTask_ID int NOT NULL IDENTITY (1, 1),
      UserTask_SequenceNumber int NOT NULL DEFAULT 0,
      UserTask_IdEntitat uniqueidentifier NOT NULL,
      UserTask_Subject varchar(100) NOT NULL,
      UserTask_Description varchar(500) NOT NULL,
              .....
              .....
      CONSTRAINT [PK_UserTask] PRIMARY KEY CLUSTERED 
      (
          [UserTask_ID] ASC
      ) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>
  
  <p>I have created an index on <code>UserTask_IdEntitat</code> column with</p>
  
  <pre><code>CREATE NONCLUSTERED INDEX IX_UserTask_IDEntitat ON dbo.UserTask 
  (
      UserTask_IDEntitat
  )
  </code></pre>
  
  <p>Executing the following query, execution plan shows us that index on <code>UserTask_IDEntitat</code> is used to do the query:</p>
  
  <pre><code>SELECT UserTask_ID
    FROM UserTask   
   WHERE UserTask_IdEntitat = @IdEntitat 
   ORDER BY UserTask_LastSendSystemDateTime desc
  </code></pre>
  
  <p>But If we add another column in the <code>Select</code> list, then the index is not used</p>
  
  <pre><code>SELECT UserTask_ID, UserTask_SequenceNumber, UserTask_IDEntitat, ....., UserTask_Subject
    FROM UserTask   
   WHERE UserTask_IdEntitat = @IdEntitat 
   ORDER BY UserTask_LastSendSystemDateTime desc
  </code></pre>
  
  <p>Why adding a column different from the primary key makes that the SQL Server execution plan doesn't use the index on the <code>UserTask_IDEntitat</code> column?</p>
  
  <p>Following this link <a href="http://bytes.com/topic/sql-server/answers/144592-sqlsever-not-using-index">http://bytes.com/topic/sql-server/answers/144592-sqlsever-not-using-index</a> it seems that the number of times that the filtered value is repeated on the column, It can make that the index is not used, but I have tried doing the query with an @IdEntitat value that is repeated 60.000 times and other that is repeated only 175 times and the results are the same, the index on <code>IDEntitat</code> column is ignored.</p>
  
  <p>This is taking me crazy!!!</p>
  
  <p>Thanks for your help.</p>