SET与SELECT - 有什么区别？

Can someone please identify the functional/performance differences, if any, between SET and SELECT in T-SQL? Under what conditions should I choose one over the other?

有人可以在T-SQL中识别SET和SELECT之间的功能/性能差异(如果有的话)吗?我应该在什么条件下选择一个?

UPDATE:
Thanks to all who responded. As a few people pointed out, this article by Narayana Vyas Kondreddi has lots of good info. I also perused the net after reading the article and found this condensed version by Ryan Farley which offers the highlights and thought I would share them:

更新:感谢所有回复的人。正如一些人所指出的那样,Narayana Vyas Kondreddi的这篇文章有很多好消息。阅读完文章之后我也仔细阅读了这篇文章,并发现了Ryan Farley的精简版,它提供了亮点并认为我会分享它们:

SET is the ANSI standard for variable assignment, SELECT is not.

SET是变量赋值的ANSI标准,SELECT不是。

SET can only assign one variable at a time, SELECT can make multiple assignments at once.

SET一次只能分配一个变量,SELECT可以一次分配多个赋值。

If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned (so you'd likely never know why something was going wrong elsewhere - have fun troubleshooting that one)

如果从查询中分配,SET只能分配标量值。如果查询返回多个值/行,则SET将引发错误。 SELECT会将其中一个值分配给变量,并隐藏返回多个值的事实(因此您可能永远不会知道其他地方出现问题的原因 - 请对其进行故障排除)

When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from it's previous value)

从查询中分配时,如果没有返回值,则SET将分配NULL,其中SELECT将不进行赋值(因此变量不会从其先前的值更改)

As far as speed differences - there are no direct differences between SET and SELECT. However SELECT's ability to make multiple assignments in one shot does give it a slight speed advantage over SET.

就速度差异而言 - SET和SELECT之间没有直接的区别。然而,SELECT能够在一次拍摄中进行多次分配确实比SET具有轻微的速度优势。

5 个解决方案

#1

SET is the ANSI standard way of assigning values to variables, and SELECT is not. But you can use SELECT to assign values to more than one variable at a time. SET allows you to assign data to only one variable at a time. So that in performance is where SELECT will be a winner.

SET是为变量赋值的ANSI标准方法,而SELECT则不是。但您可以使用SELECT一次为多个变量赋值。 SET允许您一次仅将数据分配给一个变量。因此,在性能方面,SELECT将成为赢家。

For more detail and examples refer to: Difference between SET and SELECT when assigning values to variables

有关更多详细信息和示例,请参阅:将值分配给变量时,SET和SELECT之间的差异

#2

SQL Server: one situation where you have to use SELECT is when assigning @@ERROR and @@ROWCOUNT as these have to be set in the same statement (otherwise they get reset):

SQL Server:必须使用SELECT的一种情况是分配@@ ERROR和@@ ROWCOUNT,因为这些必须在同一语句中设置(否则它们会被重置):

SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

(SET only works with one value at a time)

(SET一次只能使用一个值)

#3

Set is ANSI standard for assigning values to variables.

Set是ANSI标准,用于为变量赋值。

Select can be used when assigning values to multiple vairables.

将值分配给多个可变数据时可以使用选择。

For more details please read this detailed post by Narayana Vyas

有关详细信息,请阅读Narayana Vyas的详细信息

#4

set and select both assign values to variables. Using select you can assign values to more than one variable

设置并选择为变量赋值。使用select可以将值分配给多个变量

some thing like

就像是

select @var1=1,@var2=2

where as using set you have to use separate set statements (its an ANSI way of assigning values) i.e.

使用set时你必须使用单独的set语句(它是一种ANSI分配值的方式),即

set @var1=1

set @var2=2

I hope this helps

我希望这有帮助

cheers

#5

Common question, canned answer:

常见问题,罐头答案:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/25/defensive-database-programming-set-vs-select.aspx

#1