引用，总结自本文:

SET is the ANSI standard for variable assignment, SELECT is not. SET can only assign one variable at a time, SELECT can make multiple assignments at once. 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) 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) 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是ANSI标准，而SELECT不是。还要注意，当没有找到值时，在下面的示例中SET和SELECT的不同行为。

declare @var varchar(20)
set @var = 'Joe'
set @var = (select name from master.sys.tables where name = 'qwerty')
select @var /* @var is now NULL */

set @var = 'Joe'
select @var = name from master.sys.tables where name = 'qwerty'
select @var /* @var is still equal to 'Joe' */

在编写查询时，应该记住这个区别:

DECLARE @A INT = 2

SELECT  @A = TBL.A
FROM    ( SELECT 1 A ) TBL
WHERE   1 = 2

SELECT  @A
/* @A is 2*/

---------------------------------------------------------------

DECLARE @A INT = 2

SET @A = ( 
            SELECT  TBL.A
            FROM    ( SELECT 1 A) TBL
            WHERE   1 = 2
         )

SELECT  @A
/* @A is null*/

除了一个是ANSI和速度等，有一个非常重要的区别，总是对我很重要;不仅仅是ANSI和速度。由于这个重要的疏忽，我修复的bug数量很大。我一直在检查代码时寻找这一点。

-- Arrange
create table Employee (EmployeeId int);
insert into dbo.Employee values (1);
insert into dbo.Employee values (2);
insert into dbo.Employee values (3);

-- Act
declare @employeeId int;
select @employeeId = e.EmployeeId from dbo.Employee e;

-- Assert
-- This will print 3, the last EmployeeId from the query (an arbitrary value)
-- Almost always, this is not what the developer was intending. 
print @employeeId; 

Almost always, that is not what the developer is intending. In the above, the query is straight forward but I have seen queries that are quite complex and figuring out whether it will return a single value or not, is not trivial. The query is often more complex than this and by chance it has been returning single value. During developer testing all is fine. But this is like a ticking bomb and will cause issues when the query returns multiple results. Why? Because it will simply assign the last value to the variable.

现在让我们用SET做同样的事情:

 -- Act
 set @employeeId = (select e.EmployeeId from dbo.Employee e);

您将收到一个错误:

子查询返回多于1个值。当子查询跟在=，!=，<，<=，>，>=之后，或者当子查询用作表达式时，不允许这样做。

这很神奇，也很重要，因为为什么要将一些微不足道的“最后一项结果”分配给@employeeId呢?选择你将永远不会得到任何错误，你将花费几分钟，几个小时的调试。

也许，您正在寻找单个Id, SET将迫使您修复查询。因此你可以这样做:

-- Act
-- Notice the where clause
set @employeeId = (select e.EmployeeId from dbo.Employee e where e.EmployeeId = 1);
print @employeeId;

清理

drop table Employee;

总之，使用:

SET:当你想给一个变量赋一个值，而你的变量只对应一个值时。 SELECT:当你想给一个变量赋多个值时。变量可以是表，临时表或表变量等。

环绕select with()中的所有内容。 确保你只返回一个项目 如

ET @sql_update =  (select left(@sql_update, len(@sql_update)-1))
SET @Telephone2 = (SELECT REPLACE(LTRIM(REPLACE(@Telephone2, '0', ' ')), ' ', '0'))