SET vs. SELECT - What's the difference?

SET is the ANSI standard way of assigning values to variables, and SELECT is not. But you can use SELECT to assign values to more than one variable at a time. SET allows you to assign data to only one variable at a time. So that in performance is where SELECT will be a winner.

For more detail and examples refer to: Difference between SET and SELECT when assigning values to variables

SQL Server: one situation where you have to use SELECT is when assigning @@ERROR and @@ROWCOUNT as these have to be set in the same statement (otherwise they get reset):

SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

(SET only works with one value at a time)

Set is ANSI standard for assigning values to variables.

Select can be used when assigning values to multiple vairables.

For more details please read this detailed post by Narayana Vyas

set and select both assign values to variables. Using select you can assign values to more than one variable

some thing like

select @var1=1,@var2=2

where as using set you have to use separate set statements (its an ANSI way of assigning values) i.e.

set @var1=1

set @var2=2

I hope this helps

cheers