USE Northwind

--Inspect indexes on Customers table in Nortwind
sp_helpindex customers

--The Execution Plan shows a clustered index scan is used
--the CustomerID gives the sort order
SELECT * FROM customers

--Here the City index is used, not PK_Customers clustered index
SELECT customerid, city from customers 
ORDER BY city

--Inspect the Execution Plan to see the PK_Customers index is used.
SELECT companyname, contactname, city, country, phone 
FROM customers

--Create a composite index to cover the next query
DROP INDEX customers.contact
GO

--Create an index on the Contact table
CREATE UNIQUE NONCLUSTERED INDEX Contact 
ON customers (city, companyname, contactname, country, phone) 

--Notice in  query Execution Plan no sort required as index sorted by City
SELECT companyname, contactname, city, country, phone FROM customers ORDER BY city

--Now a sort is required as index not sorted by Country
SELECT companyname, contactname, city, country, phone 
FROM customers ORDER BY country