分配变量时 SET 还是 SELECT? [英] SET versus SELECT when assigning variables?

问题描述

在 T-SQL 中赋值变量时 SET 和 SELECT 语句有什么区别?

解决方案

引用，摘自这篇文章:

<块引用>

1. SET 是变量赋值的 ANSI 标准，SELECT 不是.
2. SET 一次只能赋值一个变量，SELECT 可以一次赋值多个.
3. 如果从查询中分配，SET 只能分配一个标量值.如果查询返回多个值/行，则 SET 将引发错误.SELECT 会将其中一个值分配给变量并隐藏返回多个值的事实(因此您可能永远不会知道为什么其他地方会出错 - 祝您愉快地排除故障)
4. 当从查询中赋值时，如果没有返回值，则 SET 将赋值为 NULL，其中 SELECT 根本不会进行赋值(因此变量不会从以前的值改变)
5. 就速度差异而言 - SET 和 SELECT 之间没有直接差异.然而，SELECT 能够一次性完成多个任务，这确实使它比 SET 具有轻微的速度优势.

What are the differences between the SET and SELECT statements when assigning variables in T-SQL?

解决方案

Quote, which summarizes from this article:

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 its 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 还是 SELECT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！