多列复合索引，就是指由多个字段组成的索引。多列复合索引的使用是相当普遍的，并且，在查询中，用多列复合索引来指定搜索范围的边界也是相当常用的。然而，微软sql server 在处理这类索引时，却有个重要的缺陷。
举个例子来说明问题，假设某个表T有索引 ( cityid, sentdate, userid), 现在有个分页列表功能，要获得大于某个多列复合索引V0的若干个记录的查询，用最简单表意的方式写出来就是 V >= V0, 如果分解开来，就是
cityid > @cityid0 or (cityid = @cityid0 and (sentdate > @sentdate0 or (sentdate = @sentdate0 and userid >= @userid0))),
当你写出上述查询时，你会期待sql server会自动的把上述识别为V >= V0类型的边界条件，并使用index seek操作来实施该查询。然而，微软的sql server 并没有那么聪明(其他的sql server如何还不得知), 当它遇到这样sql时，sql server就会采用index scan来实施，结果是您建立好的索引根本就没有被使用，如果这个表的数据量很大，那所造成的性能下降是非常大的。
对于这个问题，我曾经提交给微软的有关人士，他们进一步要求我去一个正式的网站上去提交这个缺陷，我懒得去做。
不过，对这个缺陷，还是有个办法能够绕过去的，只要把上面给出的条件变变形，sql server还是能够变回到是用index seek, 而不是低性能的index scan. 具体请看我的原文吧：
The seek predicate of the form "x > bookmark_of_x" is needed in paging query. The compiler has no difficulty to parse it correctly if x is a single column index, or two columns index, however, if x is a three columns index or more, then the compiler will have a hard time to recognize it. This failure will result in that the seek predicate ended up in residue predicate, which results in a much worse execution plan.
To illustrate the point, take a example,
Create table A( a int, b int, c int, d float, primary key (a, b, c))
now check the plan for the query:
select c, d from A where (a> 111 or a= 111 and
(b > 222 or b = 222 and c > 333))
you can see a table scan op is used, and the where clause ended up in residue predicate.
However, if you rewrite the query in a equivalent form:
select c, d from A where a> 111 or a= 111 and b > 222 or a= 111 and b= 222 and c >333
Then the compiler can choose an index seek op, which is desired.
The problem is, the compiler should be able to recognize the first form of seek predicate on multiple columns index, it saves the user having to pay extra time to figure out a get-around, not to mention the first form is a more efficient form of same expression.