Freigeben über

SYSK 30: Differences between SET and SELECT in SQL Server

  • Artikel

I came across a great post with same title by Narayana Vyas Kondreddi at http://vyaskn.tripod.com/differences_between_set_and_select.htm.  Great info, very well presented.

Below are some key points from the article:

1.  SET is the ANSI standard way of assigning values to variables, and SELECT is not.

2.  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
 -- 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. The system variables @@ERROR and @@ROWCOUNT must be captured in one statement, immediately after a data manipulation (DML) statement like INSERT, UPDATE, DELETE, or else, these system variables get reset to 0.
So, to get the right values and avoid complex workarounds, use SELECT in this case, instead of SET

4. When using a query to populate a variable, SET will fail with an error, if the query returns more than one value, but SELECT will assign one of the returned rows and mask the fact that the query returned more than one row. As a result, bugs in your code could go unnoticed with SELECT.

5.  Based on Narayana's performance testing, there is no performance difference between SET and SELECT when one variable is used. However, a single SELECT statement assigning values to 3 different variables is at least twice as fast compared to SET.

Comments

  • Anonymous
    December 16, 2005
    This is good info too!

    I have a question about how to select the last of a group of items. A sample would be a client table that is related to orders where the client may have more than one order. how would I obtain a match on the last order only? I could do it in MS Access by using the LAST function in the grouping context. But LAST is not an option in SQL Server.

  • Anonymous
    December 22, 2005
    Andy,

    If you have an order id that's an identity field, you could use something like this: SELECT TOP 1 * FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderID DESC

  • Anonymous
    November 11, 2006
    IF select itself can be used to assign values to variables then, why "SET" Keyword was added unneccassarily to SQL 7.0 ? moreover set is used to enable modes such as set nocount on/off so on ..... and it also assign values to variables ,why this functionality added to 'SET' when there is already 'select' statement for assigning values.

  • Anonymous
    November 12, 2006
    Unnecessary 'SET' keyword?  I beg the difference... Here is one of many valuable example of using the SET keyword (the SQL statement below is an example of modifying an Inventory (I) table from the INSERT trigger on the Transactions (T) table: Update I Set I.Quantity = (I.Quantity - T.Quantity) From Join INSERTED T on I.ItemID = T.ItemID

  • Anonymous
    November 16, 2006
    Set is included in SQL Server because "1.  SET is the ANSI standard way of assigning values to variables, and SELECT is not." If MS didn't include this then they wouldn't be able to say they supported ANSI SQL.