SQL server 关于索引中字段类型 的排列顺序 进而影响SQL语句的查询速率

同一段SQL语句，因表的索引中的 不同数据类型字段的顺序，进而产生不一样的结果及执行时间

select count(1) as totalcount from (SELECT * FROM (
 select distinct  sa.ProjectTypeCode, active.Id,active.ContractorId,active.CompanyName,active.CooperationTypes ,active.PeriodID,'' PeriodName ,active.Annex,active.IsMark ,active.MainContacts, active.AddressAndTel, active.LastModificationTime, active.ContractorEmployeeId,active.SprPlanDetailId,active.SPRCompleteDetailId , active.SprReportId from serviceActivities sa with (nolock) 
join ReportBusinessCooperation active with (nolock) on sa.ServiceActivitieId = active.Id 
 and sa.IsDeleted=0 and active.IsDeleted=0  
 and sa.ProjectTypeCode=26   
 and (1=1)
 AND sa.BehaviorHappenDate <= '2021/3/31 0:00:00' and sa.BehaviorEndDate >='2021/1/1 0:00:00'
 AND sa.ContractorId = 'f7368068-51db-c04e-d81d-39e7ccb79202'  
 AND  sa.SprReportId = '-1' and sa.SprPlanDetailId ='-1' and   sa.SPRCompleteDetailId ='-1'  and sa.ContractorId='f7368068-51db-c04e-d81d-39e7ccb79202'  
 join  (select * from  ServiceActivitiesProductRelation  aps  where  1=1  
 and exists ( select uu.id from [ServiceActivitiesProductRelation] uu where 
 '66bcd5c4-fb4f-3eb3-6df1-39f58512a4bf,10f2f9e9-94b4-c6bf-6856-39f41ee11b5c,-1' like '%'+uu.ProductId+'%'  
 and  '1decb0bd-347b-8715-736b-39f58515f6c7,26882107-fb33-04bf-61a6-39f422c2bf13,-1' like '%'+uu.ProductFormatId+'%'  
 and uu.id=aps.id )
 )
 rela on sa.id= rela.ParentId  and rela.IsDeleted=0 
  join (  select * from  PersonalAgreementShips  aps  where  1=1     
 and exists(  select  id  from(  select t.id, b.prov from(  select cspemp.id, convert(xml, '<root><v>' + replace(cspemp.Province, ',', '</v><v>') + '</v></root>') as prov  from PersonalAgreementShips cspemp with(nolock)     
  where   cspemp.ContractorID = 'f7368068-51db-c04e-d81d-39e7ccb79202'   and cspemp.CSPId='0b94ac99-d9fd-2dff-8b2a-39f22f015e46'   
 )t 
   outer apply  (select C.v.value('.', 'nvarchar(36)')prov from t.prov.nodes('/root/v')C(v)) b )conempprov   
  join (  select b.prov from( select convert(xml, '<root><v>' + replace('100000',',', '</v><v>') + '</v></root>') as prov  ) t outer apply ( select C.v.value('.','nvarchar(36)') prov from t.prov.nodes('/root/v')C(v))b  ) planprov on conempprov.prov = planprov.prov OR conempprov.prov = '100000' OR planprov.prov = '100000' where conempprov.Id = aps.Id ) 
   ) perag on sa.ContractorEmployeeId=perag.ContractorEmployeeId  and perag.IsDeleted=0  

) availabledata WHERE 1=1
 AND availabledata.ContractorId='f7368068-51db-c04e-d81d-39e7ccb79202'

) tab_num

表中索引的排列顺序：

SQL语句的查询结果：

 

 更改表中索引字段的排列顺序：

 

 

 

 

对比两图 表serviceActivities  的扫描次数也不尽相同（虽然逻辑读取次数多，但扫描次数少），进而影响了SQL语句的查询速率。

 结论：

1. 表的“扫描次数”与“表的逻辑读取次数”对表查询速度的影响，“扫描次数” >"逻辑读取次数"。
2. 在加过索引的表中数据类型排列顺序：数字类型的索引查找效率高于字符串类型 ;数字类型或值类型>定长字符串(char、nchar)>变长字符串(varchar、nvarchar)，

以上皆是自己通过查询结果自己总结归纳，如有错误欢迎指点修改。以免误人子弟。