Joe Celko's SQL for Smarties, 3rd Edition

33.12. Order Indexes Carefully

Consider the Personnel table again. There may be a difference among these CREATE INDEX statements:

1.  CREATE INDEX XDeptDiv
    ON Personnel (dept, division);

2.  CREATE INDEX XDivDept
    ON Personnel (division, dept);

3.  CREATE CLUSTERED INDEX XCDeptDiv
    ON Personnel (dept, division);

4.  CREATE CLUSTERED INDEX XCDivDept
    ON Personnel (division, dept);

In cases 1 and 2, some products build an index only on the first column and ignore the second column. This is because their SQL engine is based on an older product that allows only single-column indexing, and the parser is throwing out the columns it cannot handle. Other products use the first column to build the index tree structure and the secondary columns in such a way ...