SQL赋值SET和SELECT有什么区别？

事实上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]

 

sql十万个为什么 http://www.sqlwhy.com

文章来源：http://www.sqlwhy.com/t_sql/t_sqltips/2009-05-22/151.html