T-SQL动态查询（3）——静态SQL

接上文：T-SQL动态查询（2）——关键字查询   本文讲述关于静态SQL的一些知识和基础技巧。

简介：

什么是静态SQL？静态SQL是和动态SQL相对而言的，其实我们没必要过于纠结精确定义，只要大概知道什么算静态SQL即可。当一个语句特别是存储过程，语句不需要动态生成或拼接，除了参数之外我们都知道语句的最终形态时，就可以认为这是静态SQL，简单来说，我们大部分的处理动态查询条件的语句都属于静态SQL。

 

为什么使用静态SQL：

本文不是介绍静态SQL的入门文章，介绍静态SQL最主要是介绍其中的OPTION(RECOMPILE)查询提示。通过静态SQL可以解决简单的关于动态查询条件的问题。虽然有些情况下这种查询提示并不必要，但是静态SQL中的动态查询条件往往会包含这种提示。对于什么情况下可以不用，可以看上文：T-SQL动态查询（2）——关键字查询

使用带有OPTION(RECOMPILE)的静态SQL有以下优点：

• 对于中等复杂的搜索条件，可以得到排版紧凑，相对易于维护的代码。
• 由于查询每次都要重编译，得到的执行计划是针对当前查询条件进行优化的。
• 权限问题，这种方式在存储过程中总是可行的，也就是说只要用户有权限执行存储过程即可，不需要有直接操作表的权限。

当然，有优点就肯定有缺点：

• 当需求变得越来越复杂时，语句的复杂度会趋于非线性增长，甚至没有人会想到曾经一个简单的查询会变成如此复杂和难以理解。
• 如果查询被极其频繁地调用，过度编译、重编译会严重增加服务器的负担甚至导致服务器崩溃。

注意：OPTION(RECOMPILE)从SQL 2008 SP2/SQL 2008 R2 SP1及后续版本中才真正起效。

 本文处处：http://blog.csdn.net/dba_huangzj/article/details/50056419

基础技能：

回顾一下第一篇：T-SQL动态查询（1）——简介 。里面提到的存储过程模版sp_Get_orders，我们在这里暂且不讨论存储过程中的@employeestr和@employeetbl参数。修改一下存储过程中的WHERE ???为以下条件，并以sp_Get_orders_1为新存储过程名：

CREATE PROCEDURE sp_Get_orders_1
                 @salesorderid     int     = NULL,
                 @fromdate    datetime     = NULL,
                 @todate      datetime     = NULL,
                 @minprice    money        = NULL,
                 @maxprice    money        = NULL,
                 @custid      int          = NULL,
                 @custname    nvarchar(40) = NULL,
                 @prodid      int          = NULL,
                 @prodname    nvarchar(40) = NULL
AS
SELECT o.SalesOrderID, o.OrderDate, od.UnitPrice, od.OrderQty,
       c.CustomerID, per.FirstName as CustomerName,p.ProductID,
       p.Name as ProductName,  per.BusinessEntityID as EmpolyeeID
FROM   Sales.SalesOrderHeader o
INNER JOIN   Sales.SalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
INNER JOIN   Sales.Customer c ON o.CustomerID = c.CustomerID
INNER JOIN   Person.Person per on c.PersonID=per.BusinessEntityID
INNER JOIN   Production.Product p ON p.ProductID = od.ProductID
WHERE  (o.SalesOrderID = @salesorderid OR @salesorderid IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (od.UnitPrice >= @minprice OR @minprice IS NULL)
  AND  (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
  AND  (o.CustomerID = @custid OR @custid IS NULL)
  AND  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.Name LIKE @prodname + '%' OR @prodname IS NULL)
ORDER  BY o.SalesOrderID
OPTION (RECOMPILE)
GO

本文处处：http://blog.csdn.net/dba_huangzj/article/details/50056419

其中@xxxx IS NULL子句的作用是当一个输入参数为NULL时，相应的AND条件总为TRUE。因此，对WHERE子句起效的条件就是为非NULL的值。虽然看起来挺简单，但是带有或不带有最后一句OPTION(RECOMPILE)会在性能上有巨大差异。

这个提示指示SQL Server每次都重编译该查询，如果不使用这个查询提示，SQL Server默认会产生执行计划并缓存以供重用。记住每次重编译的另外一个结果就是不缓存执行计划。

但是由于参数嗅探的原因，执行计划通常会针对第一次传入的参数进行优化和产生执行计划并缓存。珍重特性很难支持其他不同的参数。当用户传入一个单一订单号时，为了获取最佳响应时间，我们希望优化器使用Sales.SalesOrderHeader表和Sales.SalesOrderDetail表上的SalesOrderID列的索引而忽略其他所以。但如果用户传入产品号或产品名时，我们希望使用Production.Product表上的ProductID列的索引，其他条件类似。

这正是使用OPTION(RECOMPILE)提示的原因。由于SQLServer被命令每次都重编译查询，所以没有必要缓存执行计划，SQLServer会把所有变量按常量形式处理，比如这样调用存储过程：

EXEC sp_Get_orders_1@salesorderid = 11000

SQL Server优化时实际上的WHERE子句为：

WHERE  (o.SalesOrderID = 11000 OR 11000 IS NULL)
  AND  (o.OrderDate >= NULL OR NULL IS NULL)
  AND  (o.OrderDate <= NULL OR NULL IS NULL)
  AND  (od.UnitPrice >= NULL OR NULL IS NULL)
  AND  (od.UnitPrice <= NULL OR NULL IS NULL)
  AND  (o.CustomerID = NULL OR NULL IS NULL)

SQL Server 内部会把这些NULL ISNULL的条件从语句中移除，所以本质上的WHERE子句为：

WHERE  o.SalesOrderID = 11000

这样，优化器不需要考虑其他因素，直接使用SalesOrderID上的所以来支持查询。同样，如果以这种方式调用存储过程：

EXEC sp_Get_orders_1@custid = 123

则实际WHERE条件变为：

WHERE  (o.SalesOrderID = NULL OR NULL IS NULL)
  AND  (o.OrderDate >= NULL OR NULL IS NULL)
  AND  (o.OrderDate <= NULL OR NULL IS NULL)
  AND  (od.UnitPrice >= NULL OR NULL IS NULL)
  AND  (od.UnitPrice <= NULL OR NULL IS NULL)
  AND  (o.CustomerID = 123 OR 123 IS NULL)
  ...

本文处处：http://blog.csdn.net/dba_huangzj/article/details/50056419

此时优化器觉得使用CustomerID上的所以更加有效。可以使用下面测试语句来测试各种执行计划：

EXEC sp_Get_orders_1@prodid  = 76
EXEC sp_Get_orders_1@prodid  = 76, @custid = 123
EXEC sp_Get_orders_1@fromdate = '20080205', @todate = '20080209'

    下面来看看两个比较有意思的调用方式：

首先创建一个索引：

use AdventureWorks2008R2
go
create index IX_SalesOrderHeader_OrderDate on Sales.SalesOrderHeader(OrderDate)

然后执行下面两个存储过程：

exec sp_Get_orders_1@fromdate='20050701',@todate ='20050701'
exec sp_Get_orders_1@fromdate='20050101',@todate ='20051231'