SQL Server中使用set和select赋值时的差别

为说明其差别，建立一张数据表，插入几条数据

create table tblStudent
(
id nvarchar(10) primary key,
name nvarchar(21)not null,
)

insert into tblStudent(id,name) values('2008110101','张三');
insert into tblStudent(id,name) values('2008110102','李四');
insert into tblStudent(id,name) values('2008110103','李四');

1.select可以同时给多个变量赋值，set只能给一个变量赋值

情况1：

declare @variable1 int,@variable2 int
select @variable2=1,@variable2=2
set @variable1=1,@variable2=2

报错

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ','.

情况2：

declare @variable1 int,@variable2 int
set @variable2=1
set @variable2=2
select @variable1=1,@variable2=2  

正常通过

Command(s) completed successfully.

2.当返回值为多行时，select不会出错，set要报错

declare @id nvarchar(10)
print 'set赋多返回值:'
set @id=(select id from tblStudent1 where name='李四')
print 'set后的值为:'+@id --由于姓名为李四的人有两个，所以用set赋值会报错

print 'set赋多返回值:'
select @id=id from tblStudent1 where name='李四'
print 'set后的值为:'+@id --用select赋值则不会报错

结果为:

错误码中Subquery returned more than 1 value.(即子查询返回超过一个值)

3.当无返回值时,set不为变量赋值,select赋值为NULL

--使用set赋空返回值
DECLARE @id varchar(10)
SET @id = '无值'

SET @id = 
(
SELECT id
FROM dbo.tblStudent
WHERE id = '**********'
)
--使用set赋值时，当查询无返回值时就为变量赋值为NULL
SELECT @id as setResult
GO

--使用select赋空返回值
DECLARE @id varchar(10)
SET @id = '无值'

SELECT @id = name
FROM dbo.tblStudent
WHERE id = '**********'

--使用select赋值时，当查询无返回值时就不为变量赋值，变量保持以前的值即'无值'
SELECT @id as selectResult
GO

结果为:

 

4.相同条件下为多个变量赋值时，用select为多个变量赋值效率高，用多个set为变量赋值的效率较之更低

参考:http://vyaskn.tripod.com/differences_between_set_and_select.htm