sql - 您是否应该避免在 JOIN 的 ON 语句中混合列和参数比较？

Question

在尝试优化使用 MERGE 的存储过程时，我偶然发现了这篇文章。我应该如何解释不包括与常量比较的粗体声明？

http://technet.microsoft.com/en-us/library/cc879317(v=sql.105).aspx

Specify only search conditions in the ON clause that determine the criteria for matching data in the source and target tables. That is, specify only columns from the target table that are compared to the corresponding columns of the source table. Do not include comparisons to other values such as a constant.

这是否意味着我应该避免使用如下所示的 ON 语句？

ON [Source].[CategoryId] = [Target].[CategoryId] AND [Source].[Color] = @Color

此建议仅适用于 MERGE 还是适用于所有类型的 JOIN？

Answer 1

参数OK，推荐。

您应该避免使用 ON 1=1 等条件加入

Query performance may be improved because the frequency of query compilations andrecompilations are reduced. The query optimizer does not apply thesimple parameterization process to MERGE statements. Therefore, MERGEstatements that contain literal values may not perform as well asindividual INSERT, UPDATE, or DELETE statements because a new plan iscompiled each time the MERGE statement is executed.

Parameterize all literal values in the ON clause and in the the WHEN clauses of the MERGE statement. For example, you can incorporate the MERGE statement into a stored procedure replacing the literal values with appropriate input parameters.