I have the following query
Select Pt.PRODID, PT.INVENTREFID, Inv.ItemName, PT.ItemID, Configid, Pt.QTYSCHED, PT.DLVDATE, Pt.CREATEDDATETIME, pt.SCHEDEND, CASE Left(PT.INVENTREFID, 3) WHEN 'SJB' THEN ST.SALESNAME WHEN 'WJB' THEN 'Sub - Assembly' ELSE 'Stock' END as CustomerName from Dynamicsv5Realtime.dbo.PRODTABLE PT Join Dynamicsv5Realtime.dbo.Inventdim ID On PT.InventdimId = ID.InventdimID and PT.Dataareaid = ID.dataareaid Join Dynamicsv5Realtime.dbo.INVENTTABLE Inv On Inv.itemid = PT.ItemId Left Join Dynamicsv5Realtime.dbo.SALESTABLE ST ON ST.SalesId = PT.INVENTREFID and ST.Dataareaid = PT.dataareaid where pt.PRODSTATUS in(2,3,4) and PT.DATAAREAID = 'AJB' Order by 7
when I was looking to improve it I came up with the following index
CREATE NONCLUSTERED INDEX [INDEX_2] ON [dbo].[PRODTABLE] ([INVENTREFID],[DATAAREAID],[INVENTDIMID],[PRODSTATUS]) INCLUDE ([PRODID],[ItemID],[QTYSCHED],[DLVDATE],[CREATEDDATETIME],[SCHEDEND])
However this was not as efficient as the following which the missing index DMV came up with
CREATE NONCLUSTERED INDEX [INDEX_1] ON [dbo].[PRODTABLE] ([DATAAREAID],[PRODSTATUS]) INCLUDE ([ITEMID],[QTYSCHED],[DLVDATE],[SCHEDEND],[INVENTREFID],[PRODID],[INVENTDIMID], [CREATEDDATETIME])
What I am wondering is when is it more efficient to put columns in the index itself rather than in the Include part of the index?