SQL Index Creation Tutorial (Part 3): Composite Index Construction

Example:

SELECT *
FROM Person.Address_TEST
WHERE City = 'Prague'
ORDER BY PostalCode DESC;

Estimated Execution Plan:

1. Open the index properties and select the Predicates section:

2. Copy the content:

3. Paste it into a prepared query and clean up the unnecessary text:

4. Select the Output List:

5.Copy the content:

6. Paste it into the prepared query, clean it up, and replace semicolons with commas:

7. From the INCLUDE section, remove City (because it is already in the index key) and remove PostalCode, which should be moved into the index key with the DESC attribute because it appears in the Sort operator:

Sort operator and its properties:

8. This produces the final index:

Conclusion

This example is simple, but I often receive very complex queries that don’t need to be analyzed in full.
Instead, I display the estimated or actual execution plan and identify the most expensive operations and the objects that are not optimal. These are usually:

• Table Scan
• Index Scan
• Clustered Index Seek while a nonclustered index is missing
• Sort

I then use the properties of these operators to copy the necessary information into the index‑creation query.

Most of the time, it’s not complicated — you just need to be well‑oriented in the execution plan and able to spot its weak points.

I recommend studying this book:
SQL Server Execution Plans 3rd Edition – Redgate Software