使用索引和include

CREATE INDEX idx_customerMissingOrNoShipment ON #customerMissingOrNoShipment(BpaMainPkey)
INCLUDE(CustomerID,MissingValidFrom,MissingValidThru)
//一般on,where,and条件用的多的字段放索引里
--select 和 order by、join
--and 的语句里的字段 放在索引里

的字段 按照顺序放在include里 --索引是根据顺序触发的

 

--join on的用include
--and的用 index
INNER JOIN BpaRole WITH (NOLOCK) ON BpaRole.BpaMainPKey = BpaMain.PKey
    AND BpaRole.Status <> 'd'
    AND BpaRole.SalesOrg = @salesOrg

--sql执行计划推荐的加索引
USE [PGTSTCustCAS103DB]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[BpaRole] ([SalesOrg],[Status])
INCLUDE ([BpaMainPKey],[BpaRoleMetaPKey])
GO

 

--select的include和on的 索引先后顺序,  join在select后

SELECT BpaAddress.Street + ' '+ BpaAddress.City + ', ' + BpaAddress.CountryState + ' ' + BpaAddress.ZipCode    AS [Ship-To Address] FROM BpaAddress

LEFT OUTER JOIN BpaAddress WITH (NOLOCK) ON BpaAddress.BpaMainPKey = customerWithMissingOrNoShipment.BpaMainPkey
    AND BpaAddress.Status <> 'd'

/*
USE [PGTSTCustCAS103DB]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[BpaAddress] ([Status])
INCLUDE ([CountryState],[ZipCode],[City],[Street],[BpaMainPKey])
GO
*/