1. 存储过程。
2. 标量用户自定义函数。
3. 多步表值函数。
4. 触发器

SELECT abc, def FROM myview
SELECT a, b, c FROM mytablefunc(9)

CREATE PROCECURE Outer_sp AS
...
EXEC Inner_sp
...

CREATE PROCEDURE Some_sp AS
DECLARE @sql    nvarchar(MAX),
        @params nvarchar(MAX)
SELECT @sql = 'SELECT ...'
...
EXEC sp_executesql @sql, @params, @par1, ...

USE Northwind
GO

CREATE PROCEDURE List_orders_1
AS
SELECT *
FROM Orders
WHERE OrderDate > '20000101'
GO

CREATE PROCEDURE List_orders_2 @fromdate DATETIME
AS
SELECT *
FROM Orders
WHERE OrderDate > @fromdate
GO

CREATE PROCEDURE List_orders_3 @fromdate DATETIME
AS
DECLARE @fromdate_copy DATETIME

SELECT @fromdate_copy = @fromdate

SELECT *
FROM Orders
WHERE OrderDate > @fromdate_copy
GO

EXEC List_orders_1
EXEC List_orders_2 '20000101'
EXEC List_orders_3 '20000101'

前两个存储过程的执行计划如上图“查询2”所示，使用了索引查找操作，而第三个存储过程使用的是聚集索引扫描操作，如上图“查询3”所示。此时SQL Server进行了全表扫描（注意，聚集索引的叶节点存储了数据本身，索引聚集索引扫描和表扫描是一样的），为什么会发生这种情况呢？为了明白优化器的决定，一般做法是检查影响的预估行数，如果把鼠标移到查找和扫描两个操作符上，可以看到下面两个结果：

其中值得关注的是“预估影响行数”（Estimated Number of Rows）。对于前两个存储过程，SQL Server预估只会返回1行数据，但是对于第三个存储过程，预估返回249行。这个差异导致了优化器选择不同的执行计划。对于一个表返回少量数据而言，索引查找+键值查找（Key Lookup）是很好的策略，但是对于相对较大规模的返回结果而言，开销就会增加，此时SQL Server底层更愿意使用直接扫描一次全表来降低开销。对于返回表的大部分甚至全部行而言，表扫描/聚集索引扫描会更加高效。因为此时SQL Server只需要访问一次表数据，而查找加键值查找，数据页中的每一行都会触发一次访问。在Northwind数据库中的Orders表，有830行数据，而SQL Server预估要返回249行，所以它觉得扫描是最佳选择。

预估数据的出处？

前面说了为什么会出现不同的执行计划：因为预估数量的不同。但是这又仅仅引出了下一个问题：为什么会不同呢？这也是本系列的关键点之一。对于第一个存储过程，日期是常量，意味着SQL Server只需要考虑这个值的情况。通过查询Orders表的统计信息，即可知道预估行数。但是统计信息毕竟是表的取样数据，所以SQL Server不能确定查询是否有数据返回，所以它认为只返回1行数据。对于第二个存储过程，查询使用了一个变量，更准确地说是一个参数。当优化器进行优化时，SQL Server知道这个存储过程会传入2000-01-01这个值。但是并不知道后续操作，所以它也不确定后续是否真的会用这个参数值。尽管如此，优化器根据输入值计算出对应的预估行数，也和第一个存储过程一样，只有1行，这种策略叫做参数嗅探（parameter sniffing）。对于第三个存储过程，就有所不同了，输入值被复制到一个本地变量，但是在SQL Server产生执行计划时，它真不知道这个值最终会是怎么样子的，所以使用一个标准假设，也就是假设会有30%的命中率，830行的30%就是249行。针对这种情况，有一个变种情景，如下面的第四个存储过程：

USE Northwind
GO

CREATE PROCEDURE List_orders_4 @fromdate DATETIME = NULL
AS
IF @fromdate IS NULL
	SELECT @fromdate = '19900101'

SELECT *
FROM Orders
WHERE OrderDate > @fromdate

在这个存储过程中，参数是可选的，如果不传参数，默认就是null，即返回所有订单数，也就是等价于EXEC List_orders_4，它的执行计划和第一、第二个存储过程一样。使用索引查找+键值查找，尽管返回所有订单数据。如果你查看索引查找操作符的属性，可以看到除了“实际影响行数”（actual number of rows）之外其他都和第二个存储过程一样。在编译这个存储过程时，SQL Server并不知道@fromdate值的改变，假设@fromdate值为NULL。因为在关系数据库中，NULL代表着“未知”，也就是说，如果@fromdate在运行过程中依旧是这个值的话，查询可能返回任意数据。如果SQL Server把这值作为最后的输入，那么它会构造一个只有常量的扫描操作而完全不需要访问表（可以使用SELECT * FROM Orders WHERE OrderDate > NULL 来检验）。但是SQL Server又不得不生成一个满足不管@fromdate在运行时传入什么值都能返回正确结果的执行计划。另外一方面，SQL Server没有义务创建一个对所有值都是最佳的执行计划，所以它假设没有任意值返回，SQL Server因此决定使用索引查找。（但是依旧是返回1行，因为SQL Server永远不会使用0行作为预估行数）这是一个关于参数嗅探的反效果的例子，在实践中，更加合理的方式已更改是改写成下面第五个存储过程的样子：

USE Northwind
GO

CREATE PROCEDURE List_orders_5 @fromdate DATETIME = NULL
AS
DECLARE @fromdate_copy DATETIME

SELECT @fromdate_copy = coalesce(@fromdate, '19900101')

SELECT *
FROM Orders
WHERE OrderDate > @fromdate_copy

关键点：

• SQL Server 的缓冲池（buffer cache)已满，SQL Server需要把“过时”的内容清除出去。这个缓存池通常包含了数据和查询计划。
• 执行了ALTER PROC/PROCEDURE命令。
• 执行了对存储过程的sp_recompile命令。
• 执行了DBCC FREEPROCCACHE命令。
• SQL Server重启，因为缓冲池是存储在内存中，重启会清空内存。
• 使用sp_configure或SSMS修改了某些影响查询计划的配置参数。

• 语句中涉及的表定义被修改。
• 语句中涉及的表上的索引变更，包含使用ALTER INDEX或DBCC DBREINDEX重建索引。
• 语句涉及的表的统计信息更新或新建统计信息。这些统计信息可能被SQL Server自动创建和更新，也可能被DBA操作。
• 语句涉及的表被执行了sp_recompile。

EXEC List_orders_2 '19900101'

USE Northwind
GO

CREATE PROCEDURE List_orders_6
AS
SELECT *
FROM Orders
WHERE OrderDate > '12/01/1998'
GO

SET DATEFORMAT dmy
GO

EXEC List_orders_6
GO

SET DATEFORMAT mdy
GO

EXEC List_orders_6
GO

SELECT qs.plan_handle, a.attrlist
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
CROSS  APPLY (SELECT epa.attribute + '=' + convert(nvarchar(127), epa.value) + '   '
              FROM   sys.dm_exec_plan_attributes(qs.plan_handle) epa
              WHERE  epa.is_cache_key = 1
              ORDER  BY epa.attribute
              FOR    XML PATH('')) AS a(attrlist)
WHERE  est.objectid = object_id ('dbo.List_orders_6')
  AND  est.dbid     = db_id('Northwind') 

plan_handle                                                                                 attrlist
------------------------------------------------------------------------------------------- --------------------------------------------------
0x05000D009D13BF5E30DF8F550300000001000000000000000000000000000000000000000000000000000000  compat_level=120   date_first=7   date_format=2
0x05000D009D13BF5ED0678F590300000001000000000000000000000000000000000000000000000000000000  compat_level=120   date_first=7   date_format=1

USE Northwind
GO

EXEC sp_recompile List_orders_2
GO

SET DATEFORMAT dmy
GO

EXEC List_orders_2 '12/01/1998'
GO

SET DATEFORMAT mdy
GO

EXEC List_orders_2 '12/01/1998'
GO

第一句sp_recompile是为了刷掉旧的计划缓存确保对演示没有影响。这一次，参数值是一样的，但是由于日期格式不一样，所以第一个查询实际上是使用了1998-01-12而第二个查询实际上使用了1998-12-01。

SELECT convert(binary(4), 4347)

上面可以看到，如果你用应用程序连接SQL Server，那么ARITHABORT是OFF的，但是使用SSMS连接，却是ON，所以应用程序和SSMS执行的同一个存储过程也使用不同的缓存条目，但是SQL Server也会编译存储过程，嗅探当前参数值，然后得到可能和应用调用的存储过程不同的查询计划。那么现在差不多可以回答一开始的问题了。后续章节会做更加深入的探讨，但是最常见的关于“在应用程序中很慢，在SSMS中很快”的原因是参数嗅探和不同的ARITHABORT默认值。

SET ANSI_NULLS, QUOTED_IDENTIFIER OFF
go
CREATE PROCEDURE stupid @x int AS
IF @x = NULL PRINT "@x is NULL"
go
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
go
EXEC stupid NULL

结果将是： @x is NULL

1. 记得上面表格中的前六个是仅仅为了向后兼容，所以建议设为OFF。虽然不是强制，但是通常而言，设为OFF很少会有问题。
2. 对于ARITHABORT，在SQL 2005及其后续版本中，ANSI_WARNING是否为ON也不对其有任何影响。在SSMS中，可以通过设置来修改。但是这回在使用SSMS连接SQL Server时改变ARITHABORT的默认设置，并且不能让你的应用跑得更快，但是最起码你不会因为在SSMS中得到不同的性能而感到困惑。

USE Northwind
GO

CREATE PROCEDURE List_orders_7 @fromdate DATETIME,
	@ix BIT
AS
SELECT @fromdate = dateadd(YEAR, 2, @fromdate)

SELECT *
FROM Orders
WHERE OrderDate > @fromdate

IF @ix = 1
	CREATE INDEX test ON Orders (ShipVia)

SELECT *
FROM Orders
WHERE OrderDate > @fromdate
GO

EXEC List_orders_7 '19980101',
	1

实际执行计划如下：

EXEC List_orders_7 '19960101', 0

USE Northwind
GO

DROP INDEX test
	ON Orders

DROP PROCEDURE List_orders_7

USE Northwind
GO

CREATE PROCEDURE List_orders_8
AS
DECLARE @fromdate DATETIME

SELECT @fromdate = '20000101'

SELECT *
FROM Orders
WHERE OrderDate > @fromdate

CREATE INDEX test ON Orders (ShipVia)

SELECT *
FROM Orders
WHERE OrderDate > @fromdate

DROP INDEX test
	ON Orders
GO

EXEC List_orders_8
GO

DROP PROCEDURE List_orders_8

USE Northwind
GO

CREATE PROCEDURE List_orders_9
AS
DECLARE @ids TABLE (a INT NOT NULL PRIMARY KEY)

INSERT @ids (a)
SELECT OrderID
FROM Orders

SELECT COUNT(*)
FROM Orders O
WHERE EXISTS (
		SELECT *
		FROM @ids i
		WHERE O.OrderID = i.a
		)

CREATE INDEX test ON Orders (ShipVia)

SELECT COUNT(*)
FROM Orders O
WHERE EXISTS (
		SELECT *
		FROM @ids i
		WHERE O.OrderID = i.a
		)

DROP INDEX test
	ON Orders
GO

EXEC List_orders_9
GO

DROP PROCEDURE List_orders_9

USE Northwind
GO

CREATE TYPE temptype AS TABLE (a INT NOT NULL PRIMARY KEY)
GO

CREATE PROCEDURE List_orders_10 @ids temptype READONLY
AS
SELECT COUNT(*)
FROM Orders O
WHERE EXISTS (
		SELECT *
		FROM @ids i
		WHERE O.OrderID = i.a
		)
GO

DECLARE @ids temptype

INSERT @ids (a)
SELECT OrderID
FROM Orders

EXEC List_orders_10 @ids
GO

DROP PROCEDURE List_orders_10

DROP TYPE temptype

• 不总是参数嗅探的错