It’s many times observed, a SQL Server Developer commonly use SELECT for assigning values to variables. This was fine and a perfectly valid practice till SQL Server 6.5 But SQL Server 7.0 introduced the new SET statement for initializing and assigning values to variables and SQL Server 7.0 Books Online also stated: “It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable.”
There are many Differences between SET and SELECT as
1) SET is the ANSI standard way of assigning values to variables, and SELECT is not.
2) SET can only assign one variable at a time, SELECT can make multiple assignments at once.
Example :
— Declaring variables
DECLARE @Variable1 AS int, @Variable2 AS int
— Initializing two variables at once
SELECT @Variable1 = 1, @Variable2 = 2
— The same can be done using SET, but two SET statements are needed */
SET @Variable1 = 1
SET @Variable2 = 2
3) If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned
4) When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from its previous value)
Please Comments on same J
WikiDBA 