Create Indexes With Included Columns

SQL Server Performance Tuning:  Create indexes with included columns. I recently did some tuning work on a query that has computed columns in the SELECT. While the index that was being used by the query did contain columns used in the query’s WHERE clause performance was still not as fast as it could be. I checked the execution plan and could see that there were some parallelism operators, a non-clustered index scan with a key lookup on some computed columns that are part of the SELECT but not in the WHERE clause. After adding the computed columns as included columns to the index being used, the parallelism and key look up went away and the query really sped up.

CREATE NONCLUSTERED INDEX [ix_SQLRX_IncludeColIndex] ON [dbo].[Rebate]

([BranchId],[SupplierId],[ItemId],[ReceiveDt])

INCLUDE ([OnHandQty],[TotCostAmt],[PayDiscAmt],[EOMAmt],[EOQAmt],[EOYAmt],[RebateAmt])

ON [PRIMARY]

GO

More information from Microsoft here: https://msdn.microsoft.com/en-us/library/ms190806.aspx

& https://msdn.microsoft.com/en-us/library/ms189292.aspx