SQL Server解惑——预定义语句与即席查询区别

在SQL Server中预定义语句（Prepared Statement）与即席查询（Ad Hoc Query）是啥区别呢？ 其实官方文档甚至没有一个非常明确的定义说明，像Oracle、MySQL等其它数据库，也没有即席查询这类概念。下面简单总结一下即席查询跟预定义语句。

 

即席查询（Ad Hoc Query）

 

什么是即席查询（Ad Hoc Query）呢？以单独的SQL语句的形式执行的查询就是即席查询，一般这样的SQL是硬编码形式，动态执行的。例如，你在SSMS中查询一个表中数据的SQL，但是存储过程或函数中同样的这么一个SELECT语句，就不叫即席查询了，而是存储过程/函数中的语句。

 

即席查询有个特点就是SQL语句必须一模一样，才能重用缓存的执行计划。

 

关于即席查询的一些英文介绍（都是非官方资料）

 

An Ad-Hoc Query is a query that cannot be determined prior to the moment the query is issued. It is created in order to get information when need arises and it consists of dynamically constructed SQL which is usually constructed by desktop-resident query tools.

 

An Ad-Hoc Query is hard coded or the dynamically executed, and the cached plan can only be re-used for a near identical statement.

 

例如下面SQL就是一个即席查询：

 

SELECT * FROM Person.Person WHERE BusinessEntityID=10;

 

另外，你在程序里面，拼接而成的SQL也是即席查询。

 

 

预定义语句（Prepared Statement）

 

 

预定义语句指SQL语句中使用了占位符替换了实际值的SQL语句，例如，像参数化查询这类SQL等等。这类语句可以重用，通过输入不同的值，完成不同的操作。这个有点类似Oracle中使用绑定变量的SQL

 

 

关于预定义语句的一些英文介绍（都是非官方资料）

 

 

A prepared query is paramaterized and can be reused for a range of different inputs.

Queries which substitute place holders in place of actual values are called Prepared statements

 

A prepared query is paramaterized and can be reused for a range of different inputs

 

举个例子，你在代码里面的的这样一个SQL语句，就是一个预定义语句。

 

 

command.CommandText = "SELECT * FROM dbo.Users WHERE UserID=@UserID AND Passowrd=@Passowrd";


command.Parameters.AddWithValue("@UserID", UserID);

command.Parameters.AddWithValue("@Passowrd", Passowrd);

 

 

那么下面，在SSMS中执行的SQL是即席查询还是预定义语句呢？  

 

DECLARE @BusinessEntityID INT;
SET @BusinessEntityID=10;
SELECT * FROM Person.Person WHERE BusinessEntityID=@BusinessEntityID;

 

 

   如下验证所示，你会发现这个SQL是即席查询 

SELECT  cp.[usecounts] ,
        cp.[refcounts] ,
        cp.[cacheobjtype] ,
        CASE cp.[objtype]
          WHEN 'Proc'       THEN 'Stored procedure'
          WHEN 'Prepared'   THEN 'Prepared statement'
          WHEN 'Adhoc'      THEN 'Ad hoc query'
          WHEN 'ReplProc'   THEN 'Replication-filter-procedure'
          WHEN 'UsrTab'     THEN 'User table'
          WHEN 'SysTab'     THEN 'System table'
          WHEN 'Check'      THEN 'Check constraint'
          ELSE cp.[objtype]
        END AS [object_type] ,
        cp.[size_in_bytes] ,
        ISNULL(DB_NAME(qt.[dbid]), 'resourcedb') AS [db_name] ,
        qp.[query_plan] ,
        qt.[text]
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.dm_exec_sql_text(cp.[plan_handle]) qt
        CROSS APPLY sys.dm_exec_query_plan(cp.[plan_handle]) qp
WHERE qt.text LIKE '%@BusinessEntityID%'
ORDER BY cp.[usecounts] DESC;
GO

 

 

这个是否有点颠覆了我们的认知？ 照理说，这里的SQL使用了参数化查询，应该是预定义语句（Prepared Statement）吧，但是在SSMS中执行这个SQL，就变成了即席查询，但是在C#等语言中这样运用的话，它就是预定义语句（Prepared Statement），很是纠结吧。原因也很简单，如下所示，你修改一下参数@BusinessEntityID的值，运行一次，你会发现，SQL Server并没有重用原先的执行计划，而是重新编译了。所以它符合即席查询的特征。 

DECLARE @BusinessEntityID INT;
SET @BusinessEntityID=12;
SELECT * FROM Person.Person WHERE BusinessEntityID=@BusinessEntityID;

 

 

 

 

在SSMS中执行下面SQL，继续用上面SQL验证，你会发现这个SQL语句的类型为预定义语句（Prepared Statement）

 

EXEC sp_executesql N'SELECT * FROM Person.Person WHERE BusinessEntityID=@BusinessEntityID', N'@BusinessEntityID INT', 10
GO

 

 

缓存的具体SQL如下：

 

(@BusinessEntityID INT)SELECT * FROM Person.Person WHERE BusinessEntityID=@BusinessEntityID

 

 

总结：

 

预定义语句（Prepared Statement）与即席查询（Ad Hoc Query）有一个区别就是它是否有占位符（参数化查询），而且能否在输入不同的参数时，能否重用缓存的执行计划。

 

 

参考资料：

 

https://stackoverflow.com/questions/38072550/what-is-the-difference-between-ad-hoc-and-prepared-query-in-sql-server-plan-cach