Return to Answer

Further reading:

• Fun with Scalar and Vector Aggregates

See also my article, Fun with Scalar and Vector Aggregates

Multiple Definite Assignment

On occasion, you might need to definitely assign multiple variables at once. This means you have to use SELECT, but this doesn't assign NULL if no rows are produced by the query:

DECLARE @T AS table ( a integer NOT NULL, b integer NOT NULL, c integer NOT NULL ); -- Original values DECLARE @a integer = 1, @b integer = 2, @c integer = 3; -- Multiple assignment SELECT @a = T.a, @b = T.b, @c = T.c FROM @T AS T; -- Values unchanged because no row found SELECT a = @a, b = @b, c = @c; 

Output:

The workaround is to use an OUTER APPLY:

-- Definite assignment workaround DECLARE @T AS table ( a integer NOT NULL, b integer NOT NULL, c integer NOT NULL ); -- Original values DECLARE @a integer = 1, @b integer = 2, @c integer = 3; SELECT @a = T.a, @b = T.b, @c = T.c FROM (VALUES(1/0)) AS D (dummy) OUTER APPLY ( SELECT TT.a, TT.b, TT.c FROM @T AS TT ) AS T; -- Values changed to NULL SELECT a = @a, b = @b, c = @c; 

Output:

The dummy table ensures a row always exists to cause variable assignment. The outer apply means the dummy row is unaffected when the target query produces no rows.

The intentional division by zero in the dummy table expression ensures you don't accidentally select that value. That column isn't projected in the example, so the optimizer removes it from the execution plan and no error occurs.

The semantics of the two statements are different:

• The first does not set the value of the variable if no row is found.
• The second always sets the variable, including to null if no row is found.

The Constant Scan produces an empty row (with no columns!) that will result in the variable being updated in case nothing matches from the base table. The left join ensures the empty row survives the join. Variable assignment can be thought of as happening at the root node of the execution plan.

###Using SELECT @result

-- Set initial value DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'}; -- @result does not change SELECT @result = AccountId FROM Accounts WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'}; SELECT @result; 

###Using SET @result

-- Set initial value DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'}; -- @result set to null SET @result = ( SELECT AccountId FROM Accounts WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'} ); SELECT @result; 

###Execution plans

_{No row arrives at the root node, so no assignment occurs.}

_{A row always arrives at the root node, so variable assignment occurs.}

The extra Constant Scan and Nested Loops Left Outer Join are nothing to be concerned about. The join in particular is cheap since it is guaranteed to encounter one row on its outer input, and at most one row (in your example) on the inner input.

There are other ways to ensure a row is generated from the subquery to ensure a variable assignment occurs. One is to use a redundant scalar aggregate (no group by clause):

-- Set initial value DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'}; -- @result set to null SET @result = ( SELECT MAX(AccountId) FROM Accounts WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'} ); SELECT @result; 

Notice the scalar aggregate produces a row even though it receives no input.

Documentation:

• SET @local_variable (Transact-SQL)
• SELECT @local_variable (Transact-SQL)

If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.

 

For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable.

Further reading:

• Fun with Scalar and Vector Aggregates

The semantics of the two statements are different:

• The first does not set the value of the variable if no row is found.
• The second always sets the variable, including to null if no row is found.

The Constant Scan produces an empty row (with no columns!) that will result in the variable being updated in case nothing matches from the base table. The left join ensures the empty row survives the join. Variable assignment can be thought of as happening at the root node of the execution plan.

Using SELECT @result

-- Set initial value DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'}; -- @result does not change SELECT @result = AccountId FROM Accounts WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'}; SELECT @result; 

Using SET @result

-- Set initial value DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'}; -- @result set to null SET @result = ( SELECT AccountId FROM Accounts WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'} ); SELECT @result; 

Execution plans

_{No row arrives at the root node, so no assignment occurs.}

_{A row always arrives at the root node, so variable assignment occurs.}

The extra Constant Scan and Nested Loops Left Outer Join are nothing to be concerned about. The join in particular is cheap since it is guaranteed to encounter one row on its outer input, and at most one row (in your example) on the inner input.

There are other ways to ensure a row is generated from the subquery to ensure a variable assignment occurs. One is to use a redundant scalar aggregate (no group by clause):

-- Set initial value DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'}; -- @result set to null SET @result = ( SELECT MAX(AccountId) FROM Accounts WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'} ); SELECT @result; 

Notice the scalar aggregate produces a row even though it receives no input.

Documentation:

• SET @local_variable (Transact-SQL)
• SELECT @local_variable (Transact-SQL)

If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.

For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable.

Further reading:

• Fun with Scalar and Vector Aggregates

The semantics of the two statements are different:

• The first does not set the value of the variable if no row is found.
• The second always sets the variable, including to null if no row is found.

The Constant Scan produces an empty row (with no columns!) that will result in the variable being updated in case nothing matches from the base table. The left join ensures the empty row survives the join. Variable assignment can be thought of as happening at the root node of the execution plan.

###Using SELECT @result

-- Set initial value DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'}; -- @result does not change SELECT @result = AccountId FROM Accounts WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'}; SELECT @result; 

###Using SET @result

-- Set initial value DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'}; -- @result set to null SET @result = ( SELECT AccountId FROM Accounts WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'} ); SELECT @result; 

###Execution plans

_{No row arrives at the root node, so no assignment occurs.}

_{A row always arrives at the root node, so variable assignment occurs.}

The extra Constant Scan and Nested Loops Left Outer Join are nothing to be concerned about. The join in particular is cheap since it is guaranteed to encounter one row on its outer input, and at most one row (in your example) on the inner input.

There are other ways to ensure a row is generated from the subquery to ensure a variable assignment occurs. One is to use a redundant scalar aggregate (no group by clause):

-- Set initial value DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'}; -- @result set to null SET @result = ( SELECT MAX(AccountId) FROM Accounts WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'} ); SELECT @result; 

Notice the scalar aggregate produces a row even though it receives no input.

Documentation:

• SET @local_variable (Transact-SQL)
• SELECT @local_variable (Transact-SQL)

If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.

For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable.

Further reading:

Fun with Aggregates

The semantics of the two statements are different:

• The first does not set the value of the variable if no row is found.
• The second always sets the variable, including to null if no row is found.

The Constant Scan produces an empty row (with no columns!) that will result in the variable being updated in case nothing matches from the base table. The left join ensures the empty row survives the join. Variable assignment can be thought of as happening at the root node of the execution plan.

###Using SELECT @result

-- Set initial value DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'}; -- @result does not change SELECT @result = AccountId FROM Accounts WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'}; SELECT @result; 

###Using SET @result

-- Set initial value DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'}; -- @result set to null SET @result = ( SELECT AccountId FROM Accounts WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'} ); SELECT @result; 

###Execution plans

_{No row arrives at the root node, so no assignment occurs.}

_{A row always arrives at the root node, so variable assignment occurs.}

The extra Constant Scan and Nested Loops Left Outer Join are nothing to be concerned about. The join in particular is cheap since it is guaranteed to encounter one row on its outer input, and at most one row (in your example) on the inner input.

There are other ways to ensure a row is generated from the subquery to ensure a variable assignment occurs. One is to use a redundant scalar aggregate (no group by clause):

-- Set initial value DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'}; -- @result set to null SET @result = ( SELECT MAX(AccountId) FROM Accounts WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'} ); SELECT @result; 

Notice the scalar aggregate produces a row even though it receives no input.

Documentation:

• SET @local_variable (Transact-SQL)
• SELECT @local_variable (Transact-SQL)

If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.

For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable.

Further reading:

• Fun with Scalar and Vector Aggregates