参数化（二）：执行查询的方式

前面一篇我介绍了执行计划缓存以及执行之前批处理经过的流程。这篇将用几个最普通的例子介绍查询的几种执行方式。

请看下面这个我使用的这个查询:

SELECT
    Id,Name,LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = N'IL';

???? 这是一个简单的检索指定国家的顾客的查询。现在我们来测试前面这个查询，并且展示七个不同的查询方式。同时介绍执行方法对计划缓存和计划重用的影响。

???? 为了检测影响，我们使用下面的视图监视:

CREATE VIEW
    dbo.CachedPlans
(
    QueryText,QueryPlan,ExecutionCount,ObjectType,Size_KB,LastExecutionTime
)
AS
SELECT

QueryText           = QueryTexts.text,QueryPlan         = QueryPlans.query_plan,ExecutionCount      = CachedPlans.usecounts,ObjectType          = CachedPlans.objtype,Size_KB           = CachedPlans.size_in_bytes / 1024,LastExecutionTime    = last_execution_time

FROM

sys.dm_exec_cached_plans AS CachedPlans

CROSS APPLY

sys.dm_exec_query_plan (plan_handle) AS QueryPlans

CROSS APPLY

sys.dm_exec_sql_text (plan_handle) AS QueryTexts

INNER JOIN

sys.dm_exec_query_stats AS QueryStats

ON

CachedPlans.plan_handle = QueryStats.plan_handle;

???? 这个视图检索所有的当前在计划缓存中的计划，包含批处理的文档和计划以及每个计划的最终执行时间。使用下面这个查询来检查计划缓存的内容，只查询本次计划:

SELECT
    *
FROM
    dbo.CachedPlans
WHERE
    QueryText LIKE N'%Customers%'
AND
    QueryText NOT LIKE N'%sys.dm_exec_cached_plans%'
ORDER BY
    LastExecutionTime ASC;

因此， 最为普通的方式查询就是下面这种:

SELECT
    Id,LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = N'IL';

??? 这就是非参数化T-SQL查询。这个查询不能利用参数，用不同的国家编码查询时会产生独立的执行计划。如果使用不同的国家查询，就会有独立计划在缓存中，并且执行的计数为1。如下：

第二种方式是用非参数化动态执行查询，具体如下:

DECLARE
@Country????? AS NCHAR(2)??????????????? = N'IL',@QueryText??? AS NVARCHAR(MAX);
SET @QueryText =
N'
SELECT
Id,LastPurchaseDate
FROM
Marketing.Customers
WHERE
Country = N''' + @Country + N''';
';
EXECUTE (@QueryText);

在这种情况下，在@QueryText变量中的动态查询，动态部分是在用字符拼接进去。然后使用EXECUTE 语句。查询被传递给查询处理器这点与非参数化查询一样。与非参数化查询一样，这种查询也不适用参数，因此如果用不同的国家编码，还是产生独立的执行计划。

?? 现在，我们建立一个动态查询，这次为国家创建一个参数，并且传递参数给系统存储过程。

DECLARE
@Country????? AS NCHAR(2)??????????????? = N'IL',@QueryText??? AS NVARCHAR(MAX),@Parameters?? AS NVARCHAR(MAX);
SET @QueryText =
N'
SELECT
Id,LastPurchaseDate
FROM
Marketing.Customers
WHERE
Country = @pCountry;
';
SET @Parameters = N'@pCountry AS NCHAR(2)';
EXECUTE sys.sp_executesql
@statement??? = @QueryText,@params?????? = @Parameters,@pCountry???? = @Country;

??? @pCountry 是动态批处理范围内的参数。@Parameters 变量保存所有的批处理中的参数。这个批处理产生一个参数化计划。如果用不同的国家编码运行这个代码，会重用相同的准备计划，因为每个执行就是一个相同的批处理，只有参数不同。

接下来，让我们看一下在应用程序中相同的执行计划。例如在C#中，可以建一个查询文本，然后把这个文本赋值CommandText然后执行。

如下：

SqlConnection Connection = new SqlConnection(Properties.Settings.Default.ConnectionString);
SqlCommand Command = new SqlCommand();
Command.CommandType = CommandType.Text;
Command.CommandText = "SELECT Id,LastPurchaseDate FROM Marketing.Customers WHERE Country = N'" + textBox1.Text + "';";
Command.Connection = Connection;
Connection.Open();
Command.ExecuteReader();
Connection.Close();

本质上这是与一个用EXECUTE 语句执行的动态非参数化查询是一样的，并且用不同的国家编码执行计划缓存内容是一样的:

但是也可以在动态查询中嵌入参数，并且定义这些参数，就想我们用sys.sp_executesql 执行存储过程一样。

SqlConnection Connection = new SqlConnection(Properties.Settings.Default.ConnectionString);
SqlCommand Command = new SqlCommand();
Command.CommandType = CommandType.Text;
Command.CommandText = "SELECT Id,LastPurchaseDate FROM Marketing.Customers WHERE Country = @pCountryId;";
Command.Parameters.Add("@pCountryId",SqlDbType.NChar);
Command.Parameters["@pCountryId"].Size = 2;
Command.Parameters["@pCountryId"].Value = textBox1.Text;
Command.Parameters["@pCountryId"].Direction = ParameterDirection.Input;
Command.Connection = Connection;
Connection.Open();
Command.ExecuteReader();
Connection.Close();

实际上，当我们运行这个应用程序的代码时，它被转译成完全相同的sys.sp_executesql 的执行计划…

<table style="width: 902px;" border="1" cellspacing="0" cellpadding="2">