SET与SELECT - 有什么区别？ [英] SET vs. SELECT - What's the difference?

问题描述

有人可以在T-SQL中标识 SET 和 SELECT 之间的功能/性能差异（如果有的话） ？在什么情况下我应该选择哪一种？

---

更新：

感谢所有回复。正如少数人指出的那样，这篇文章由 Narayana Vyas Kondreddi 有很多很好的信息。阅读完文章后，我也仔细阅读了网页，发现这个缩略版本由 Ryan Farley 提供了亮点，并认为我会分享它们：

1. SET是
   变量赋值的ANSI标准，SELECT不是。


2. SET只能在
   a time，SELECT可以一次执行多个
   赋值。
   
3. 如果从查询赋值，SET
   只能赋值一个标量值。如果
   查询返回多个值/行
   ，则SET将引发错误。 SELECT
   会将其中一个值赋给
   变量，并隐藏返回
   多个值的事实（所以
   您可能永远不会知道为什么
   是在其他地方出错 - 有趣的解决这个问题）


4. 当从查询中分配时，如果
   没有返回值，那么SET将
   赋给NULL，其中SELECT不会
   完成赋值工作（因此
   变量不会从
   更改为之前的值）
   
5. 就速度差异而言 -
   在
   SET和SELECT之间没有直接差异。然而，SELECT的
   能够一次性完成多个分配
   ，这使得它比SET小
   的优势。

有关更多详细信息和示例，请参阅：

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?

---

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:

1. SET is the ANSI standard for variable assignment, SELECT is not.
2. SET can only assign one variable at a time, SELECT can make multiple assignments at once.
3. 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)
4. 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)
5. 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 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.

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

这篇关于SET与SELECT - 有什么区别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！