關於 SQL Server 執行 like 時的索引使用的有趣情形

今天在複習SQL效能調校內容時遇到一個有趣的情形，我不確定這是不是對效能真的有幫助或也許只是SQL Server的一個bug，總之，先記錄下來，詳情如下，

--測試環境:
--SQL Server 2008 Standard Edition x86
--SQL Server 2005 SP2 Developer Edition x86
use AdventureWorks
go
--在Person.Contact建立個Covering Index
create index icl_LastName_Title on Person.Contact(LastName, Title)
 include(ContactID, FirstName)
go
--1. 會用索引搜尋(seek)
select 
 ContactID, Title, LastName, 
 FirstName
from Person.Contact
where LastName like 'Adams%'
 and Title like 'Mr%'

--2. 會用索引掃描(scan), 所以每個教學在測完1,2後提醒大家like時不要用前面的'%'
select 
 ContactID, Title, LastName, 
 FirstName
from Person.Contact
where LastName like '%Adams%'
 and Title like '%Mr%'

--3. 會用索引掃描(scan), 這個先放著,是為了4,5對照用
select 
 ContactID, Title, LastName, 
 FirstName
from Person.Contact
where LastName like '%%'
 and Title like '%%'

--等等要用的變數
declare @LastName nvarchar(50)
declare @Title nvarchar(8)
set @LastName='' --'Adams'
set @Title='' --'Mr.'

--4. 常用的選擇性查詢條件的做法, 會用索引掃描(scan)
select 
 ContactID, Title, LastName, 
 FirstName
from Person.Contact
where (LastName like '%'+@LastName+'%' or @LastName='') 
 and (Title like '%'+@Title+'%' or @Title='')

--5. *有趣的事在這邊*
--   把4.的「or @xxx=''」拿掉後, 目的還是相同的, 
--   而且@xxx在空字串''的情況下, 5.和3.應該是一樣的, 
--   但是, 5.會用索引搜尋(seek)+巢狀迴圈內部聯結(join)
select 
 ContactID, Title, LastName, 
 FirstName
from Person.Contact
where LastName like '%'+@LastName+'%'
 and Title like '%'+@Title+'%'

go

跳轉後可以看執行計劃的抓圖...