我期待这两个SELECT具有相同的执行计划和性能。由于有一个前导通配符LIKE，我期望进行索引扫描。当我运行这个并查看计划时，第一个SELECT行为符合预期（通过扫描）。但第二个SELECT计划显示索引查找，并且运行速度快 20 倍。
Code:
-- Uses index scan, as expected:
SELECT 1
FROM AccountAction
WHERE AccountNumber LIKE '%441025586401'
-- Uses index seek somehow, and runs much faster:
declare @empty VARCHAR(30) = ''
SELECT 1
FROM AccountAction
WHERE AccountNumber LIKE '%441025586401' + @empty
问题：
当模式以通配符开头时，SQL Server 如何使用索引查找？
奖金问题：
为什么连接空字符串会改变/改进执行计划？
Details:
Accounts.AccountNumber
Accounts.AccountNumber列可以为空varchar(30)
表和索引定义：
CREATE TABLE [updatable].[AccountAction](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccountNumber] [varchar](30) NULL,
[Utility] [varchar](9) NOT NULL,
[SomeData1] [varchar](10) NOT NULL,
[SomeData2] [varchar](200) NULL,
[SomeData3] [money] NULL,
--...
[Created] [datetime] NULL,
CONSTRAINT [PK_Account] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_updatable_AccountAction_AccountNumber_UtilityCode_ActionTypeCd] ON [updatable].[AccountAction]
(
[AccountNumber] ASC,
[Utility] ASC
)
INCLUDE ([SomeData1], [SomeData2], [SomeData3]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE CLUSTERED INDEX [CIX_Account] ON [updatable].[AccountAction]
(
[Created] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
笔记： 这里是actual两个查询的执行计划。对象的名称与上面的代码略有不同，因为我试图使问题保持简单。
这些测试（数据库 AdventureWorks 2008R2）显示了会发生什么：
SET NOCOUNT ON;
SET STATISTICS IO ON;
PRINT 'Test #1';
SELECT p.BusinessEntityID, p.LastName
FROM Person.Person p
WHERE p.LastName LIKE '%be%';
PRINT 'Test #2';
DECLARE @Pattern NVARCHAR(50);
SET @Pattern=N'%be%';
SELECT p.BusinessEntityID, p.LastName
FROM Person.Person p
WHERE p.LastName LIKE @Pattern;
SET STATISTICS IO OFF;
SET NOCOUNT OFF;
Results:
Test #1
Table 'Person'. Scan count 1, logical reads 106
Test #2
Table 'Person'. Scan count 1, logical reads 106
The results from SET STATISTICS IO shows that LIO are the same.
But the execution plans are quite different:
在第一个测试中，SQL Server 使用Index Scan显式但在第二个测试中 SQL Server 使用Index Seek这是一个Index Seek - range scan。在最后一种情况下，SQL Server 使用Compute Scalar运算符来生成这些值
[Expr1005] = Scalar Operator(LikeRangeStart([@Pattern])),
[Expr1006] = Scalar Operator(LikeRangeEnd([@Pattern])),
[Expr1007] = Scalar Operator(LikeRangeInfo([@Pattern]))
并且，Index Seek操作员使用Seek Predicate（优化）对于range scan (LastName > LikeRangeStart AND LastName < LikeRangeEnd）加上另一个未优化的Predicate (LastName LIKE @pattern).
LIKE '%...' 如何在索引上查找？
我的回答：这不是“真实的”Index Seek。它是Index Seek - range scan在这种情况下，它具有相同的性能Index Scan.
另请参阅以下之间的区别Index Seek and Index Scan（类似的辩论）：那么……是搜索还是扫描？ http://sqlblog.com/blogs/paul_white/archive/2011/02/16/so-is-it-a-seek-or-a-scan.aspx.
Edit 1: The execution plan for OPTION(RECOMPILE) (see Aaron's recommendation please) shows, also, an Index Scan (instead of Index Seek):
