The Microsoft SQL Server T-SQL create index has the INCLUDE option (2005 and on) to include non-key column(s) in the index so that a specific query can be satisfied - covered - from the index without accessing table data. Composite index can also cover a query. Covering index speeds up the target query, however, due to larger size, may have negative impact on other queries.

-- MSSQL query with execution timing envelope
-- DBCC DROPCLEANBUFFERS forces pages to disk for consistent time measurements
-- SELECT INTO table create
USE tempdb;
SELECT * INTO Address
FROM AdventureWorks2008.dbo.Address
 
DBCC DROPCLEANBUFFERS
DECLARE @StartTime datetime = getdate();
 
-- TABLE SCAN (NonClustered)
-- COST 0.275
SET STATISTICS IO ON
SELECT a.PostalCode,
       a.City,
       a.StateProvinceID
FROM   dbo.Address a
WHERE  a.PostalCode LIKE '34%';
SET STATISTICS IO OFF
 
SELECT DurationMSEC = DATEDIFF(ms, @StartTime, getdate())
GO
-- logical reads 340
-- 76 msec
 
 
-- T-SQL create index with non-key columns INCLUDE
-- MSSQL SELECT will be covered for City & StateProvinceID (not for other colums)
CREATE INDEX idxPostalCodeCovering
ON dbo.Address (PostalCode)
INCLUDE ( City, StateProvinceID);
GO
-- Command(s) completed successfully.
 
 
-- SQL Server time query again after index create
DBCC DROPCLEANBUFFERS
DECLARE @StartTime datetime = getdate();
 
-- INDEX SEEK (NonClustered)
-- COST 0.0034
SET STATISTICS IO ON
SELECT a.PostalCode,
       a.City,
       a.StateProvinceID
FROM   dbo.Address a
WHERE  a.PostalCode LIKE '34%';
SET STATISTICS IO OFF
 
SELECT DurationMSEC = DATEDIFF(ms, @StartTime, getdate())
GO
-- logical reads 4,
-- 10 msec
 
 
DROP INDEX dbo.Address.idxPostalCodeCovering
GO
 
-- T-SQL create composite index
-- MSSQL SELECT will be covered for City & StateProvinceID (not for other colums)
CREATE INDEX idxPostalCodeCoveringComposite
ON dbo.Address (PostalCode,City, StateProvinceID);
GO
-- Command(s) completed successfully.
 
 
-- SQL Server time query again after index create
DBCC DROPCLEANBUFFERS
DECLARE @StartTime datetime = getdate();
 
-- INDEX SEEK (NonClustered)
-- COST 0.0034
SET STATISTICS IO ON
SELECT a.PostalCode,
       a.City,
       a.StateProvinceID
FROM   dbo.Address a
WHERE  a.PostalCode LIKE '34%';
SET STATISTICS IO OFF
 
SELECT DurationMSEC = DATEDIFF(ms, @StartTime, getdate())
GO
-- logical reads 4,
-- 13 msec
 
 
-- Cleanup
DROP TABLE dbo.Address