SQL Server使用变量和参数以及语句执行时执行计划的差异

T-SQL语句之间传输数据有以下一些途径：
1）本地变量
2）存储过程中的参数
3）应用程序变量
4）参数标记
上面4种变量中，比较常用的是本地变量和存储过程中的参数。

本地变量指在查询前declare参数，并且set设值之后，在查询语句中直接使用声明的参数，而不是直接使用其值。

存储过程中的参数有两种，一种是定义在存储过程里面，类似上面的本地变量，一种是定义在存储过程外面。这两种参数，都是在执行存储过程时，将参数值传入进去。不过两种定义方法各有优缺点，下一章将进行深入探讨。

应用程序变量主要指编程语言C，C++，Basic和Java等，它们都有自己的一套变量来存储值。应用程序一般通过数据库的API把T-SQL语句返回的值存储到自己的变量中，然后再使用这些值。

参数标记是放置在 T-SQL 语句中的输入表达式位置的一个问号 (?)。参数标记绑定了应用程序变量，似的应用程序变量中的数据可以用作T-SQL语句中的输入。参数标记还允许存储过程输出参数和返回代码绑定到应用程序变量。

本专题主要介绍本地变量和存储过程中的参数的定义，使用以及语句执行时执行计划的差异。

注： 下面每种情况执行之前都执行DBCC FREEPROCCACHE清除了缓存，生产环境避免执行该语句。

不使用变量和参数

直接在SSMS中赋值，SQL Server在编译时知道具体的值，给出的执行计划很准确。
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE 'Man%';

开启SQL Server Profiler抓取SP:CacheHit和SP:CacheMiss事件，可以发现where条件换个数据后，SQL Server会根据当前的值重新生成执行计划。

使用本地变量

首先我们来看看本地变量的定义及使用方法。
/* Also allowed:
DECLARE @find varchar(30) = 'Man%';
*/
DECLARE @find varchar(30);
SET @find = 'Man%';
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;
使用本地变量，SQL Server在执行语句时，根据变量的预估值进行编译，给出的执行计划比较准确。

抓取SQL Server Profiler，会发现使用本地变量每次执行都会重新编译，生成新的执行计划。

将变量定义在存储过程里面

示例：
--local variable
create procedure sniff(@find varchar(30)) as
DECLARE @findIn varchar(30);
set @findIn=@find
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @findIn;
go
与本地变量一样，它的值在存储过程语句执行过程中得到，SQL Server在运行时不知道变量的值，会根据一个预估值进行编译，给出一个折中的执行计划。
exec sniff 'Man%';

exec sniff 'U%';

将变量定义在存储过程外面

示例：
create procedure sniff2(@find varchar(30)) as
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;
go
当调用存储过程时，必须要给他带入值。第一次运行存储过程时，SQL Server根据带入的变量值进行编译，给出准确的执行计划。以后再次调用存储过程，SQL Server会重用第一次的执行计划，不会进行重编译，以达到较高的效率，这是存储过程的一大优势。
exec sniff2 'Man%';


exec sniff2 'U%';

不少客户在SQL语句中使用本地变量，会出现同一语句有时快有时慢的情况，从上面的测试应该可以得到答案了。使用存储过程，最大的好处就是可以重用执行计划，大大提高执行效率，但是这一优势同时也是存储过程的最大劣势，首次编译的执行计划如果不适用于新的参数值，有可能会让语句执行时间反很多倍，这就是著名的parameter sniffing，下一章我们将继续深入探讨。