下面我们来看看EXECUTE , SP_EXECUTESQL的执行效率，首先把缓存清除执行计划，然后改变用@GroupName值SuperAdmin、CommonUser、CommonAdmin分别执行三次。然后看看其使用缓存的信息
DBCC FREEPROCCACHE;
DECLARE @Sql VARCHAR(200);
DECLARE @GroupName VARCHAR(50);
SET @GroupName = 'SuperAdmin'; --'CommonUser', 'CommonAdmin'
SET @Sql = 'SELECT * FROM Groups WHERE GroupName=' + QUOTENAME(@GroupName, '''')
EXECUTE (@Sql);
SELECT cacheobjtype, objtype, usecounts, sql
FROM sys.syscacheobjects
WHERE sql NOT LIKE '%cache%'
AND sql NOT LIKE '%sys.%';
DECLARE @Sql VARCHAR(200);
DECLARE @GroupName VARCHAR(50);
SET @GroupName = 'SuperAdmin'; --'CommonUser', 'CommonAdmin'
SET @Sql = 'SELECT * FROM Groups WHERE GroupName=' + QUOTENAME(@GroupName, '''')
EXECUTE (@Sql);
SELECT cacheobjtype, objtype, usecounts, sql
FROM sys.syscacheobjects
WHERE sql NOT LIKE '%cache%'
AND sql NOT LIKE '%sys.%';
如下图所示：
依葫芦画瓢，接着我们看看SP_EXECUTESQL的执行效率.
DBCC FREEPROCCACHE;
DECLARE @Sql NVARCHAR(200);
DECLARE @GroupName NVARCHAR(50);
SET @GroupName = 'SuperAdmin'; --'CommonUser', 'CommonAdmin'
SET @Sql = 'SELECT * FROM Groups WHERE GroupName=@GroupName'
EXECUTE SP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)', @GroupName;
SELECT cacheobjtype, objtype, usecounts, sql
FROM sys.syscacheobjects
WHERE sql NOT LIKE '%cache%'
AND sql NOT LIKE '%sys.%';
DECLARE @Sql NVARCHAR(200);
DECLARE @GroupName NVARCHAR(50);
SET @GroupName = 'SuperAdmin'; --'CommonUser', 'CommonAdmin'
SET @Sql = 'SELECT * FROM Groups WHERE GroupName=@GroupName'
EXECUTE SP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)', @GroupName;
SELECT cacheobjtype, objtype, usecounts, sql
FROM sys.syscacheobjects
WHERE sql NOT LIKE '%cache%'
AND sql NOT LIKE '%sys.%';
执行结果如下图所示：
Summary：EXEC 生成了三个独立的 ad hoc 执行计划，而用SP_EXECUTESQL只生成了一次执行计划，重复使用了三次，试想如果一个库里面，有许多这样类似的动态SQL，而且频繁执行，如果采用SP_EXECUTESQL就能提高性能。
