Create Index Example With Include

Query:

Execution Plan suggest Missing Index…

Missing Index (Impact 93.0): CREATE NONCLUSTERED INDEX [<name>] ON [dbo].[tbTable01] ([Col01], [Col02], [Col03], [Col04])

SELECT 
	COUNT(*) 
FROM 
	tbTable01
WHERE 
	Col01 = @ipv_iUserId 
	AND 
	Col02 BETWEEN @dtStartDate AND @dtEndDate 
	AND 
	Col03 = 1 
	AND 
	Col04 in (2016, 2017)

Index:

Good

CREATE NONCLUSTERED INDEX [idx_tbTable01_Col04_Col02] ON [dbo].[tbTable01]
(
	[Col04] ASC,
	[Col02] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [fgGroup01]
GO

Better

Include Col01 and Col03 to eliminate key lookup step.

CREATE NONCLUSTERED INDEX [idx_tbTable01_Col04_Col02] ON [dbo].[tbTable01]
(
	[Col04] ASC,
	[Col02] ASC
)
INCLUDE (Col01, Col03) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [fgGroup01]
GO

Note:

84 records = 12 minutes