TSQL 常量...使用变量还是文字?

问题描述

我只是在阅读我公司的代码指南，它说永远不要将变量视为 sql server 中的常量，而是使用文字.原因是当你在查询中使用变量时，sql server 无法建立一个好的执行计划.

I'm just reading my company's code guidelines and it says to never treat variables as a constant in sql server, use literals instead. The reasoning is that sql server can't build a good execution plan when you're using variables in the query.

有人知道这是否仍然正确吗?我们使用的是 MSSQL 2005，此文档可能是为 2000 年编写的.

Anybody know if this is still true? We're using MSSQL 2005 and this document may have been written for 2000.

推荐答案

这对于 SQL Server 2005 来说通常"是正确的，因为它现在尝试为尽可能多的查询预先生成执行计划.您的里程可能会有所不同，因为它取决于您的数据和您的 SARG(测试您的查询计划并查看)，但此 tecdoc(他们使用的是 SQL Server 2005 的预发布版本)指出:

This is "generally" true for SQL Server 2005 since it now tries to pre-generate execution plans for as many queries as possible. Your mileage may vary since it depends on your data and your SARG (test your query plans and see) but this tecdoc (they were using a pre-release version of SQL Server 2005) states:

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

避免使用局部变量查询

如果在查询中使用局部变量谓词而不是参数或字面上，优化器求助于降低质量的估计或猜测谓词的选择性.用查询中的参数或文字而不是局部变量，并且优化器通常能够选择一个更好的查询计划.例如，考虑这个使用本地的查询变量:

If you use a local variable in a query predicate instead of a parameter or literal, the optimizer resorts to a reduced-quality estimate, or a guess for selectivity of the predicate. Use parameters or literals in the query instead of local variables, and the optimizer typically will be able to pick a better query plan. For example, consider this query that uses a local variable:

这篇关于TSQL 常量...使用变量还是文字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！