导致我相信,对于T-SQL中的单个变量赋值,set
是处理问题的最佳方法,其原因有两个:
所以...
SELECT @thingy = 'turnip shaped'
变成
SET @thingy = 'turnip shaped'
但是,速度有多快?我真的会注意到差异吗?
最佳答案
单次运行SET更快。您可以很容易地证明这一点。它是否起作用取决于您,但是我更喜欢SET,因为如果所有代码都在执行的话,我看不到SELECT的意义。我更喜欢将SELECT限制在表, View 等的SELECT语句中。
这是一个示例脚本,运行次数设置为1:
SET NOCOUNT ON
DECLARE @runs int
DECLARE @i int, @j int
SET @runs = 1
SET @i = 0
SET @j = 0
DECLARE @dtStartDate datetime, @dtEndDate datetime
WHILE @runs > 0
BEGIN
SET @j = 0
SET @dtStartDate = CURRENT_TIMESTAMP
WHILE @j < 1000000
BEGIN
SET @i = @j
SET @j = @j + 1
END
SELECT @dtEndDate = CURRENT_TIMESTAMP
SELECT DATEDIFF(millisecond, @dtStartDate, @dtEndDate) AS SET_MILLISECONDS
SET @j = 0
SET @dtStartDate = CURRENT_TIMESTAMP
WHILE @j < 1000000
BEGIN
SELECT @i = @j
SET @j = @j + 1
END
SELECT @dtEndDate = CURRENT_TIMESTAMP
SELECT DATEDIFF(millisecond, @dtStartDate, @dtEndDate) AS SELECT_MILLISECONDS
SET @runs = @runs - 1
END
结果:
运行#1:
SET_MILLISECONDS
5093
SELECT_MILLISECONDS
5186
运行#2:
SET_MILLISECONDS
4876
SELECT_MILLISECONDS
5466
运行#3:
SET_MILLISECONDS
4936
SELECT_MILLISECONDS
5453
运行#4:
SET_MILLISECONDS
4920
SELECT_MILLISECONDS
5250
运行#5:
SET_MILLISECONDS
4860
SELECT_MILLISECONDS
5093
奇怪的是,如果您将运行次数提高到10,则SET开始落后。
这是10次运行结果:
SET_MILLISECONDS
5140
SELECT_MILLISECONDS
5266
SET_MILLISECONDS
5250
SELECT_MILLISECONDS
5466
SET_MILLISECONDS
5220
SELECT_MILLISECONDS
5280
SET_MILLISECONDS
5376
SELECT_MILLISECONDS
5280
SET_MILLISECONDS
5233
SELECT_MILLISECONDS
5453
SET_MILLISECONDS
5343
SELECT_MILLISECONDS
5423
SET_MILLISECONDS
5360
SELECT_MILLISECONDS
5156
SET_MILLISECONDS
5686
SELECT_MILLISECONDS
5233
SET_MILLISECONDS
5436
SELECT_MILLISECONDS
5500
SET_MILLISECONDS
5610
SELECT_MILLISECONDS
5266
关于performance - 在T-SQL中SELECT与SET的速度,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/189588/