SET and SELECT in SQL Server 2005 (Difference and Comparison):

As per my observation, many developers are used to with SELECT rather than SET. Is it good practice? Let us see some facts about that.

When question comes to assigning the value to single variable, I prefer SET over SELECT as It is more readable and as per ANSI standard.

As you may know, SET and SELECT both are used to assign the value to variable. But SELECT could used to assign multiple variables at a time and SET can assign value to one variable at a time.

--select command is assigning multiple value

DECLARE @A int

DECLARE @B int

SELECT @A=1,@B=2

PRINT @A

PRINT @B

--set command is assigning only one value per set command

DECLARE @A int

DECLARE @B int

SET @A=1

SET @B=2

PRINT @A

PRINT @B

SET can’t be assigned from SELECT query like we can assign variable in SELECT statement.

--SELECT assigning value to variable @A

USE AdventureWorks

GO

DECLARE @A VARCHAR(25)

SELECT @A=NAME FROM HumanResources.Department

PRINT @A

Note: Don’t assign variable in SELECT when you are not sure whether only one row return or else you will get only last value as @A is variable, not an array

As long as performance concern, I don’t see much difference between these two but yes, you will get only benefit that you could assign more than one variable in SELECT statement.

You can draw your own conclusion based on these facts.

Reference: Ritesh Shah