SQL Server中SET与SELECT的区别：二者作为参数赋值关键字的差异解析

Great question! Let’s dive into the key distinctions between SET and SELECT when assigning values to variables in Microsoft SQL Server—these two methods might look interchangeable at first glance, but their behavior varies in critical ways that can impact your queries.

Core Differences #

1. Handling Multiple Returned Rows #

• SET: Strictly requires a single value. If your subquery returns more than one row, SET will throw an error (Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression).
  Example that fails:

  DECLARE @ProductName VARCHAR(50)
  SET @ProductName = (SELECT Name FROM Production.Product) -- Fails if multiple rows exist
  

• SELECT: Silently takes the last row's value from the result set without throwing an error. This can be useful if you intentionally want the latest record, but risky if you're expecting a single value.
  Example that works (but uses last row):

  DECLARE @ProductName VARCHAR(50)
  SELECT @ProductName = Name FROM Production.Product -- Assigns last row's Name
  

2. Behavior When No Rows Are Returned #

• SET: Sets the variable to NULL, regardless of its previous value.
  Example:

  DECLARE @Count INT = 10
  SET @Count = (SELECT COUNT(*) FROM Production.Product WHERE ProductID = 99999) -- No rows match, @Count becomes NULL
  

• SELECT: Leaves the variable with its original value if no rows are returned. This is handy if you want to preserve existing values when a query doesn't find a match.
  Example:

  DECLARE @Count INT = 10
  SELECT @Count = COUNT(*) FROM Production.Product WHERE ProductID = 99999 -- No rows match, @Count remains 10
  

3. Assigning Multiple Variables Simultaneously #

• SET: Can only assign one variable per statement. You need separate SET commands for each variable.
  Example:

  DECLARE @ID INT, @Name VARCHAR(50)
  SET @ID = (SELECT ProductID FROM Production.Product WHERE ProductNumber = 'BK-M18B-42')
  SET @Name = (SELECT Name FROM Production.Product WHERE ProductNumber = 'BK-M18B-42')
  

• SELECT: Lets you assign multiple variables in a single statement, which is more efficient and cleaner.
  Example:

  DECLARE @ID INT, @Name VARCHAR(50)
  SELECT 
      @ID = ProductID,
      @Name = Name
  FROM Production.Product 
  WHERE ProductNumber = 'BK-M18B-42'
  

4. ANSI Standard Compliance #

• SET: Follows ANSI SQL standards, making your code more portable across different database systems (like PostgreSQL, Oracle).
• SELECT: Variable assignment with SELECT is a SQL Server-specific extension. If you need your code to work with other databases, stick to SET.

Best Practices #

• Use SET when:
  • You need to enforce that only a single value is assigned (to avoid silent failures from multiple rows).
  • You want to follow ANSI standards for portability.
  • You explicitly want the variable to become NULL if no rows are found.
• Use SELECT when:
  • You need to assign multiple variables in one go for efficiency.
  • You want to preserve the variable's existing value if no rows match.
  • You intentionally want the last value from a multi-row result set.

内容的提问来源于stack exchange，提问作者Dhivya Manivannan