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 
This artcle is very nice. Thanks for given valuable information.
LikeLike
Thanks a lot dear!
LikeLike
Some developers use SELECT to append multiple values to one variable. For example, SELECT @Str = @Str + C1 FROM T1 ORDER BY C1.
SET cannot do what a SELECT appears to do, without writing additional code. But, the problem with the above SELECT method is that its behavior is not defined (the value of the result varies) – see “PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location” (http://support.microsoft.com/kb/287515). Less obvious is that a plan (for an aggregate concatenation query) which uses the HASH and PARALLELISM operators is able to produce extremely random results (values).
To ensure accurate and consistent results for an aggregate concatenation query, additional code needs to be written, at which point using SET (as opposed to SELECT) is almost an equally viable approach. Harnessing XML within a SELECT is another approach (see http://blog.programmingsolution.net/sql-server-2008/comma-separated-list-csv-of-rows-of-a-column-using-for-xml-path/). I have not tested point 4 with the XML approach (I try to avoid storing NULL values), but point 2 is moot, and point 3 is addressed by the XML approach. Since systems as all SQL Server systems, I am not concerned about whether the XML approach adheres to an ANSI/ISO standard, or not (point 1).
LikeLike
Hi Bill H,
Thanks a lot for your comment!
LikeLike
Muy buen articulo, preciso y conciso.
LikeLike
gracias hermano !
LikeLike
I tend to use the SELECT variant, especially for assigning multiple variables instead of multiple passes through the data.
An example:
SELECT @a = max(field1), @b = min(field1), @c = sum(field2) FROM table1
LikeLike
Reblogged this on Dinesh Ram Kali..
LikeLike
Nice read, I just passed this onto a friend who was doing some research on that. And he actually bought me lunch as I found it for him smile Thus let me rephrase that Thanks for lunch! eadgbfcbkebd
LikeLike
Have a healthy lunch!!!
LikeLike