SQL赋值SET和SELECT的区别

注意以下代码都是在SQL SERVER2005中运行通过。

事实上SQL Server数据库的开发者在SQL Server 7.0（1999)版本之前都是用SELECT赋值的，
但在SQL Server 7.0 之后，推出了SET方法赋值，而且Microsoft在其联机帮助文档中明确提出
推荐使用SET方法赋值。
这让开发者很迷惑，Microsoft也没有明确说明为什么SET方法是被推荐的。
本文会讲明SET和SELECT的区别，而且让你明白什么时候用SET，什么时候用SELECT。

以下代码为使用SET和SELECT赋值。

DECLARE @Variable1 AS int, @Variable2 AS int

/* 使用 SELECT */
SELECT @Variable1 = 1

/* 使用 SET */
SET @Variable2 = 2

下面就可以说说区别了：
1.假如你是标准SQL的开发者，那么请使用SET吧，因为SET是ANSI标准的SQL语句，SELECT不是。
2.你可以使用SELECT一次给两个以前变量赋值，但是SET不能。

DECLARE @Variable1 AS int, @Variable2 AS int

/* 一次赋两个变量 */
SELECT @Variable1 = 1, @Variable2 = 2

/* 一次一个变量 */
SET @Variable1 = 1
SET @Variable2 = 2

到目前来说，这没有问题，但是如果你曾经写过处理错误的T-SQL语句。你可能会意识到系统变量
@@ERROR and @@ROWCOUNT必须要在一句SQL语句中捕获。并且是在DML语句（INSERT, UPDATE, DELETE等）
之后立即捕获。如果不是这样，这样系统变量会马上重新设置为0。如果这时你还是使用校准SET来赋值，
那你就麻烦了。
如下例子说明这个问题：

DECLARE @Error int, @RowCount int
SELECT price/0 FROM dbo.titles
SET @RowCount = @@ROWCOUNT
SET @Error = @@ERROR
SELECT @Error AS Error
GO

以上代码如果在pubs数据库中运行，@@ERROR值会显示为0，但实际是显示 division by zero，错误号为8134.
在这样的情况下，我们可以忘掉SET语句，使用SELECT吧。

DECLARE @Error int, @RowCount int
SELECT price/0 FROM dbo.titles
SELECT @RowCount = @@ROWCOUNT, @Error = @@ERROR
SELECT @Error AS Error

但是如果你坚持使用标准ANSI SQL赋值方法，也是有办法的，但是可读性不好，不推荐这样。

DECLARE @ErrorAndRowcount AS varchar(25), @Error int, @RowCount int
SELECT price/0 FROM dbo.titles

/* Capturing @@ERROR and @@ROWCOUNT into a dot separated string */
SET @ErrorAndRowcount = CAST(@@ERROR AS varchar(12)) + '.' + CAST(@@ROWCOUNT AS varchar(12))

/* One way to separate the string into error and rowcount variables */
SET @Error = CAST(PARSENAME(@ErrorAndRowcount, 2) AS int)
SET @RowCount = CAST(PARSENAME(@ErrorAndRowcount, 1) AS int)
SELECT @Error AS Error, @RowCount AS Row_Count

/* Another way of splitting the string into error and rowcount variables */
SET @Error = CAST(LEFT(@ErrorAndRowcount, CHARINDEX('.', @ErrorAndRowcount)-1) AS int)
SET @RowCount = CAST(RIGHT(@ErrorAndRowcount, CHARINDEX('.', REVERSE(@ErrorAndRowcount))-1) AS int)
SELECT @Error AS Error, @RowCount AS Row_Count
GO

3.SET和SELECT还有一个区别是，当使用查询出来的值赋值给变量时，SET和SELECT都可以实现，但当查询出的值为多
个是，SET会提示错误，但SELECT不会，只会接受最后一个值。这点很重要，也是很多程序Bug容易被忽略的地方。
如下是例子：

/* 创建两条记录的测试表 */
SET NOCOUNT ON
CREATE TABLE #Test (i int, j varchar(10))
INSERT INTO #Test (i, j) VALUES (1, 'First Row')
INSERT INTO #Test (i, j) VALUES (1, 'Second Row')
GO

/* 以下查询出来两条记录，不会报错。
假如你不知道 WHERE i = 1 有两条记录，这样很容易出错*/
DECLARE @j varchar(10)
SELECT @j = j FROM #Test WHERE i = 1
SELECT @j
GO

但你用SET重写上面的SQL语句。

DECLARE @j varchar(10)
SET @j = (SELECT j FROM #Test WHERE i = 1)
SELECT @j
将会报错：
Server: Msg 512, Level 16, State 1, Line -1074284106
Subquery returned more than 1 value. This is not permitted when the subquery 
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

所以我们在查询赋值时推荐使用SET，如果你还想用SELECT，那么请这样使用：
DECLARE @j varchar(10)
SELECT @j = (SELECT j FROM #Test WHERE i = 1)
SELECT @j

但是如果查询不返回任何记录时，请小心。
/* 以下记录返回NULL */
DECLARE @Title varchar(80)
SET @Title = 'Not Found'

SET @Title = 
(
SELECT title
FROM dbo.titles
WHERE title_id = 'InvalitTitleID'
)

SELECT @Title
GO

/* 返回'Not Found' */
DECLARE @Title varchar(80)
SET @Title = 'Not Found'

SELECT @Title = title
FROM dbo.titles
WHERE title_id = 'InvalitTitleID'

SELECT @Title
GO

最后一点，SET和SELECT在性能上有没有区别呢，这也是很多开发者不太清楚的地方。
经过测试我们发现，SET和SELECT在赋值方面，性能没有很大的区别。
但是SELECT语句可以实现一句给多个变量赋值，所以性能略高于SET。
以下为测试代码：

DECLARE @Test1 int, @Test2 int, @Test3 int, @TestVar1 int, @TestVar2 int
DECLARE @Loop int, @Start datetime, @CTR int, @TimesToLoop1 int, @TimesToLoop2 int

SET @Test1 = 0
SET @Test2 = 0
SET @Test3 = 0
SET @Loop = 0
SET @TestVar2 = 0
SET @TimesToLoop1 = 10
SET @TimesToLoop2 = 50000
WHILE @Loop < @TimesToLoop1
BEGIN
SET @Start = CURRENT_TIMESTAMP
SET @CTR = 0

/* 测试SET */
WHILE @CTR < @TimesToLoop2
BEGIN
   SET @TestVar1 = 1
   SET @TestVar2 = @TestVar2 - @TestVar1
   SET @CTR = @CTR + 1
END

SET @Loop = @Loop + 1
SET @Test1 = @Test1 + DATEDIFF(ms, @Start, CURRENT_TIMESTAMP)
END

SET @Loop = 0
SET @TestVar2 = 0
WHILE @Loop < @TimesToLoop1
BEGIN
SELECT @Start = CURRENT_TIMESTAMP
SELECT @CTR = 0

/* 测试SELECT */
WHILE @CTR < @TimesToLoop2
BEGIN
   SELECT @TestVar1 = 1
   SELECT @TestVar2 = @TestVar2 - @TestVar1
   SELECT @CTR = @CTR + 1
END

SELECT @Loop = @Loop + 1
SELECT @Test2 = @Test2 + DATEDIFF(ms, @Start, CURRENT_TIMESTAMP)
END

SET @Loop = 0
SET @TestVar2 = 0
WHILE @Loop < @TimesToLoop1
BEGIN
SELECT @Start = CURRENT_TIMESTAMP, @CTR = 0

/* 测试SELECT给多个变量赋值 */
WHILE @CTR < @TimesToLoop2
BEGIN
   SELECT @TestVar1 = 1, @TestVar2 = @TestVar2 - @TestVar1, @CTR = @CTR + 1
END

SELECT @Loop = @Loop + 1, @Test3 = @Test3 + DATEDIFF(ms, @Start, CURRENT_TIMESTAMP)
END

SELECT (@Test1/CAST(@TimesToLoop1 AS decimal(7,2)))/1000.00 AS [SET], 
(@Test2/CAST(@TimesToLoop1 AS decimal(7,2)))/1000.00 AS [SELECT], 
(@Test3/CAST(@TimesToLoop1 AS decimal(7,2)))/1000.00 AS [SELECT with Multiple Assignments]

 SQLServer中对已经定义的变量赋值的方式用两种，分别是 SET 和 SELECT。对于这两种方式的区别，SQL Server 联机丛书中已经有详细的说明，但很多时候我们并没有注意，其实这两种方式还是有很多差别的

SQL Server推荐使用 SET 而不是 SELECT 对变量进行赋值。
当表达式返回一个值并对一个变量进行赋值时，推荐使用 SET 方法。
下表列出 SET 与 SELECT 的区别。请特别注意红色部分。

 set select 
同时对多个变量同时赋值 不支持 支持 
表达式返回多个值时 出错 将返回的最后一个值赋给变量 
表达式未返回值 变量被赋null值 变量保持原值

下面以具体示例来说明问题：
create table chinadba1(
userid int ,
addr varchar(128) 
)
go
insert into chinadba1(userid,addr) values(1,'addr1')
insert into chinadba1(userid,addr) values(2,'addr2')
insert into chinadba1(userid,addr) values(3,'addr3')
go

表达式返回多个值时，使用 SET 赋值 
declare @addr varchar(128)
set @addr = (select addr from chinadba1)
/*
--出错信息为
服务器: 消息 512，级别 16，状态 1，行 2
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
*/
go
表达式返回多个值时，使用 SELECT 赋值 declare @addr varchar(128)
select @addr = addr from chinadba1
print @addr --结果集中最后一个 addr 列的值
--结果: addr3 
go

表达式未返回值时，使用 SET 赋值 declare @addr varchar(128)
set @addr = '初始值'
set @addr = (select addr from chinadba1 where userid = 4 )
print @addr --null值 
go

表达式未返回值时，使用 SELECT 赋值 declare @addr varchar(128)
set @addr = '初始值'
select @addr = addr from chinadba1 where userid = 4
print @addr --保持原值
go

需要注意的是，SELECT 也可以将标量子查询的值赋给变量，如果标量子查询不返回值，则变量被置为 null 值。
此时与 使用 SET 赋值是完全相同的
对标量子查询的概念大家应该都觉得陌生，举个例子就能说明
declare @addr varchar(128)
set @addr = '初始值'
--select addr from chinadba1 where userid = 4 为标量子查询语句
select @addr = (select addr from chinadba1 where userid = 4) 
print @addr --null值
go