T-SQL中多变量赋值的速度

问题描述

想象一下，我在存储过程的顶部有一大块初始化代码，其中包含许多变量赋值:

Imagine I have a chunk of initialisation code at the top of a stored procedure with a number of variable assignments:

SET @proc = 'sp_madeupname'
SET @magic_number = 42
SET @tomorrows_date = DATEADD(dd, 1, GETDATE())
...

显然将上述所有操作作为一个 SELECT 执行会更快:

Clearly doing all of the above as one SELECT would be faster:

SELECT
     @proc = 'sp_madeupname'
    ,@magic_number = 42
    ,@tomorrows_date = DATEADD(dd, 1, GETDATE())
...

但是要快多少?假设此存储过程作为循环的一部分执行数千次，是否会对性能产生重大影响?

But how much faster? Say if this stored procedure was executed as part of a loop, several thousand times, is it going to make any significant difference to the performance?

推荐答案

在这种情况下，SELECT 在执行多项任务时在性能方面胜出.

In this case, SELECT wins, performance-wise, when performing multiple assignments.

这里有一些关于它的更多信息:

Here is some more information about it:

SELECT 与 SET:优化循环

这篇关于T-SQL中多变量赋值的速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！