Index Architecture and Behavior - SQL Server Query Performance Tuning - page 123

Table 8-2. Sample Table

c1 c2

1 1

2 1

3 1

1 2

2 2

3 2

If a composite index is created on the columns ( c1 , c2 ), then the index will be ordered as shown in Table 8-3 .

Table 8-3. Composite Index on Columns ( cl , c2 )

c1 c2

1 1

1 2

2 1

2 2

3 1

3 2

As shown in Table 8-3 , the data is sorted on the first column ( c1 ) in the composite index. Within each value of the
first column, the data is further sorted on the second column ( c2 ).
Therefore, the column order in a composite index is an important factor in the effectiveness of the index. You can
see this by considering the following:

• Column uniqueness

• Column width

•
For example, suppose most of your queries on table t1 are similar to the following:

Column data type

SELECT * FROM t1 WHERE c2=12 ;
SELECT * FROM t1 WHERE c2=12 AND c1=ll ;

An index on ( c2 , c1 ) will benefit both the queries. But an index on ( c1 , c2 ) will not be helpful to both queries
because it will sort the data initially on column c1 , whereas the first SELECT statement needs the data to be sorted
on column c2 .
To understand the importance of column ordering in an index, consider the following example. In the
Person.Address table, there is a column for City and another for PostalCode . Create an index on the table like this:

CREATE INDEX IX_Test ON Person.Address (City, PostalCode);