I have this table:
CREATE TABLE [dbo].[Accounts] (
[AccountId] UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID(),
-- WHATEVER other columns
);
GO
CREATE UNIQUE CLUSTERED INDEX [AccountsIndex]
ON [dbo].[Accounts]([AccountId] ASC);
GO
This query:
DECLARE @result UNIQUEIDENTIFIER
SELECT @result = AccountId FROM Accounts WHERE AccountId='guid-here'
executes with a query plan consisting of a single Index Seek – as expected:
SELECT <---- Clustered Index Seek
This query does the same:
DECLARE @result UNIQUEIDENTIFIER
SET @result = (SELECT AccountId FROM Accounts WHERE AccountId='guid-here')
but it's executed with a plan where result of Index Seek is Left Outer Joined with result of some Constant Scan and then fed into Compute Scalar:
SELECT <--- Compute Scalar <--- Left Outer Join <--- Constant Scan
^
|------Clustered Index Seek
What's that extra magic? What does that Constant Scan followed by Left Outer Join do?
Best Answer
The semantics of the two statements are different:
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
Using
SET @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):
Notice the scalar aggregate produces a row even though it receives no input.
Documentation:
Further reading: