mssql sqlserver 索引列与sql写法之间的说明


摘要:
在数据库中,索引非常的重要,有了索引,我们可以快速的获取数据,
下文将从不同sql写法中,分析使用索引列查找数据时,索引的各种不同查找方式
如下所示:



实验环境:
sqlserver 2008 R2
数据表:
[20180627Tmp]
拥有索引:
ix_tmp 包含列tmpZiDuan
拥有1041175行数据


将使用 索引查找功能的索引写法,
包含 全值匹配、不等于写法、or写法、in写法

select tmpZiDuan from [20180627Tmp] where tmpZiDuan =’B9BB6696-D7BC-40C9-99A8-00006A05F1AC’
select top 100 tmpZiDuan from [20180627Tmp] where tmpZiDuan !=’B9BB6696-D7BC-40C9-99A8-00006A05F1AC’ –索引查找
select top 100 tmpZiDuan from [20180627Tmp] where tmpZiDuan =’B720964E-862A-4E99-913F-000033E571C4′–索引查找
or tmpZiDuan =’F6A8CFDD-132C-4D93-A4A5-00004067D31C’
select top 100 tmpZiDuan from [20180627Tmp] where tmpZiDuan in
(
’61D889DF-A1F4-4C96-A862-00004597A850′,’A3A590D3-0807-4D89-B4EE-000049E51F88′
)
—索引查找

将使用 索引扫描功能获取相关数据写法,
包含左右匹配、左匹配、右匹配、索引列计算后匹配、is not null写法

select top 100 tmpZiDuan from [20180627Tmp] where tmpZiDuan like ‘%07%’

select top 100 tmpZiDuan from [20180627Tmp] where tmpZiDuan like ‘%07’

select top 100 tmpZiDuan from [20180627Tmp] where tmpZiDuan like ‘07%’

select top 100 tmpZiDuan from [20180627Tmp] where len(tmpZiDuan) = 36

select top 100 tmpZiDuan from [20180627Tmp] where left(tmpZiDuan,1) = ‘a’

select top 100 tmpZiDuan from [20180627Tmp] where tmpZiDuan is not null

将使用 常量查找方式,
包含is null 写法

select top 100 tmpZiDuan from [20180627Tmp] where tmpZiDuan is null