USE Northwind

-- Copy rows from orders to new table orders2
SELECT * INTO orders2 FROM orders

-- We need to see the number of pages read
SET STATISTICS IO ON

-- With no index: 21 pages read
SELECT * FROM orders2 WHERE OrderDate BETWEEN '1997-01-01' AND '1997-12-31'
GO

CREATE NONCLUSTERED INDEX ixorders2OrderDate ON orders2 (OrderDate)
GO

-- With forced usage of non-clustered index: 411 pages read (3 index pages to find the bookmarks, plus 408 pages for lookups)
-- We need to force SQL to use this index because it is smart enough to understand that it will hurt performance
SELECT * FROM orders2 WITH (INDEX(ixorders2OrderDate)) WHERE OrderDate BETWEEN '1997-01-01' AND '1997-12-31'
GO

DROP INDEX orders2.ixorders2OrderDate
CREATE CLUSTERED INDEX ixcorders2OrderDate ON orders2 (OrderDate)
GO

-- With clustered index: 12 pages read
SELECT * FROM orders2 WHERE OrderDate BETWEEN '1997-01-01' AND '1997-12-31'
GO

DROP TABLE orders2