title: Creating a Non Clustered Index with a DateTime column as key
body: <p>I am working on optimizing a query which is used very frequently on our system. The where clause is similar to </p>
  
  <pre><code>WHERE J.Visibility &gt; 11 
  and J.isactive='true' 
  and J.isdeleted='false' 
  AND (
         CutOffDate &gt; '2012-04-05 00:00:00.000' 
         OR J.CreatedOn &gt; '2011-10-08 00:00:00.000'
      ) 
  AND J.Country = 'India' 
  AND J.City='Bangalore' 
  AND (J.Type &gt; 0 AND J.Type &lt; 230)  
  AND J.Category in (20)
  </code></pre>
  
  <p>Already a NC index(Index1) with City, Type, Visibility, IsActive, IsDeleted existed. I created a new NC index with the above fields but adding CreatedOn, CutOffDate and Category in the beginning as well. </p>
  
  <p>So the keys on new Index (Index2) were City, Category, CreatedOn, CutOffDate, Type, Visibility etc. CreatedOn and CutOffDate were both in descending order. However when I ran the query, the actual execution plan still did an Index Scan of the Index1 rather than Index2. Given the DateTime conditions I would have thought Index2 would be the better choice and would result in Index seek. But that did not happen. </p>
  
  <p>While investigating why this could be happening, I came across <a href="http://support.microsoft.com/kb/253595" rel="nofollow">this MS article</a> and now I am wondering if indexes with datetime need to be created like mentioned in the article. I did not find this technique of creating indexes with datetime mentioned anywhere else when I searched on Google, thus wondering what do others do?</p>