sql-server - SQL Server 参数化查询 - 显式 SQL 语句与 sp_executesql

Question

对于参数化查询，使用显式 SQL 语句与 sp_executesql 之间是否存在性能(或任何其他)差异？

例如SQL语句:

DECLARE @objectName SYSNAME, @objectType CHAR(2)

SELECT @objectName = N'sysrscols', @objectType='s' 

SELECT * 
FROM sys.objects 
WHERE [name] = @objectName AND [type] = @objectType

同样通过sp_executesql:

EXEC sys.sp_executesql
   N'select * from sys.objects where [name] = @objectName and [type] = @objectType',
       N'@objectName SYSNAME, @objectType CHAR(2)',
       @objectName = N'sysrscols', @objectType = 's'

为什么 Entity Framework 使用 sp_executesql？当使用 SQL Server Profiler 查看它生成的查询时，如果它们只是 SQL 语句，那么阅读它们会容易得多。

Answer 1

细微差别是第一个查询没有参数化。它是一批在查询中使用局部变量的 SQL 语句。这批语句作为不带参数的批处理请求发送到 SQL Server，并在其中编译和执行。您可以从跟踪(探查器或扩展事件)中看到事件类型是 *batch* 而不是 *rpc*。

当 SqlClient 应用程序(包括 EF 生成的 SQL 语句)使用参数化查询(通过将参数添加到 SqlCommand.Parameters 集合和 SQL 语句中的参数名称)时，将请求作为一个RPC(远程过程调用)请求，其中参数值的定义和传递与 SQL 语句本身分开。跟踪事件将显示 rpc_* 而不是 sql_batch_*。 SqlClient API 为此使用 sp_executesql。

参数化查询有很多好处，无论是由 EF 生成还是直接在代码中生成。这些包括:

• 更安全，防止 SQL 注入(inject)
• 消除在字符串中转义引号的需要
• 避免以特定方式格式化日期字符串文字
• 不需要小数点分隔符
• 通过促进计划缓存重用来提高性能

我要补充一点，您在 RPC 调用的 Profiler 跟踪中看到的文本只是底层 TDS 协议(protocol)请求的逆向工程。如果运行网络跟踪，您将看不到对跟踪中看到的 sp_executesql 语句的引用。但参数化查询的要点是参数值以 native 格式单独传递给 SQL Server。