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


Advertisements
Comments
  1. ashish says:

    This artcle is very nice. Thanks for given valuable information.

    Like

  2. Bill H says:

    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).

    Like

  3. Muy buen articulo, preciso y conciso.

    Like

  4. Martin Soles says:

    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

    Like

  5. Johnc28 says:

    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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s