c1b04bbd   
   From: esquel@sommarskog.se   
      
   yioann (yioann@gmail.com) writes:   
   > We have a very large table (>10 millions of rows) with all the   
   > appropriate indexes. The problem arises when we need to get some   
   > monthly statistics in a way like the following:   
   >   
   > SELECT Month(pdate),count(*)   
   > from large_table   
   > where pdate >='20090101'   
   > group by Month(pdate)   
   >   
   > Since a function is applied on the column pDate, the index is not   
   > used. Is there any way to avoid this without redesigning the database?   
      
   Hm, if there is an index on pdate, it would most certainly be used   
   since it covers the query. Furthermore, since the condition in the   
   WHERE clause is clearcut, SQL Server knows to seek only that part of   
   the index. There is no function on pdate in the WHERE clause.   
      
   What it doesn't understand is that all values to be aggregated comes in a   
   single sequence, so it may use a hash aggregation for the GROUP BY. Still   
   I would exepct this query to be decently fast. At least if the index on   
   pdate is non-clustered. If there is only a clustered index on pdate,   
   the situation is bleaker. In that case, you may want to add a NC index   
   on the column as well.   
      
   All this assumed that is your actual problematic query. If the actual   
   query looks different and include more columns or tables, most of   
   what I said do not apply.   
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)