In my last blog I talked about covering indexes.  In doing so I slightly touched up non-clustered indexes containing a pointer to the clustered index of the table.    There are often times when you want to cover a portion of the a composite clustered index.  When doing so SQL Server is smart enough to realize this, and makes adjustment. 

When creating a non clustered index on a table that is built on a clustered index (Not a heap) the leaf level of the index has a pointer to the back to the clustered index.  For example if you have an index  clustered index of col1, col2 and you put a non-clustered index on col3, this index would contain all three fields within the non clustered index, col3 for sorting and col1/2 to point back to the clustered index, again this is only at the leaf level and not the root or intermediate levels.

Where you can leverage this is when your query looks something like this:

Select col1, col2, col3 from table1

Where col3 > 5.

In this case assuming selectivity is high, SQL would use a partial scan against col3, and pick up the rest of the data without having to go back to clustered index, hence covering it.  Where SQL gets smart is when you want to use a field inside your composite index.  If you need to satisfy the below query

Select col1, col2, col3 from table1

Where col3 > 5 and col2 < 3

The following index might be optimal assuming col3 has a higher selectivity then col2

Create index nonclusteredtest on table1(col3, col2)

In this case at the root and intermediate levels col3 and col2 are included in that sort order.  When you get down to the leaf level SQL knows not to add a double entry for col2 one for the index and one for the clustered index.  Rather it's only in there one time and represents both indexes.  This is a significant space savings particularly if you have a wide field.