Comments on Microsoft Sql Server Tutorials: SET VS SELECT - When to use what?

SET @CID =(Select CID from Customers) SELECT @CID ...

2010-06-23T23:06:09.845-07:00

SET @CID =(Select CID from Customers)
SELECT @CID = (Select CID from Customers)
Both of these statements will fail if there are multiple rows in the result.

Following is the actual syntax if query returns multiple results

SELECT @CID = CID from Customers

Actually, SET @CID =(Select CID from Customers) SE...

2010-04-12T07:35:39.822-07:00

Actually,
SET @CID =(Select CID from Customers)
SELECT @CID = (Select CID from Customers)
will behave identically - both will fail for multiple rows.

SELECT will behave as you described when you don't use a subquery (i.e. SELECT @CID = CID from Customers)

Thanks Boyan and Bart for the informative comments...

2008-04-27T11:02:00.000-07:00

Thanks Boyan and Bart for the informative comments..

Multiple assignments is by far the biggest reason ...

2008-04-27T08:58:00.000-07:00

Multiple assignments is by far the biggest reason to use select over set. It gives you a huge performance gain if you are setting a lot of variables for a big sproc/script

declare @test int, @test2 int;
set @test = 1, @test2 = 2; --doesn't work
select @test = 1, @test2 = 2; -- works

A shorter variation of:
SELECT @CID = (Select C...

2008-04-25T05:49:00.000-07:00