From: Erland Sommarskog on 13 Dec 2009 09:45
Ronnie R (RonnieR(a)discussions.microsoft.com) writes:
> Thanks Jay, that is clear. Thanks
> So in this final example....
> WHERE INDEX4='foo' AND INDEX2='bar'
> a index on INDEX1 with included columns (INDEX2,INDEX3,INDEX4) would
> likely be used, as INDEX1 part of the index is ignored, INDEX2 used,
> INDEX3 ignored, INDEX4 used. Would you agree?
No. First of all it is not that likely that the optimizer would
use this index at all, as it would have to scan the entire index
to find the rows. But if the optimizer would use the index, it would
of course evalaute all columns present in the index against the WHERE
Erland Sommarskog, SQL Server MVP, esquel(a)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