Which is Better for Performance – SELECT or SET? – Interview Question of the Week #166

Question: Which is faster for SQL Server variable assignment, SELECT or SET?

Answer: Some interview questions never get old, and this is one of them. I have heard “SELECT is faster” repeated as a rule because a single SELECT can assign several variables while SET needs one statement per variable. That is a narrow comparison, not a useful general performance promise. The more important question is what happens when the query returns zero rows or several rows.

For constants, the two forms below are straightforward. Use whichever makes the intent clearest; benchmark a real workload before arguing about the tiny cost of these assignments.

DECLARE @foo1 int, @foo2 int, @foo3 int;

SET @foo1 = 1;
SET @foo2 = 2;
SET @foo3 = 3;

SELECT @foo1 = 1,
       @foo2 = 2,
       @foo3 = 3;

Here is the behavior that changes application results. With a row-assignment SELECT, no matching row leaves the existing variable value alone. A scalar subquery used by SET returns NULL when it finds no row. The script is safe to run as a small demonstration:

DECLARE @Chosen int = 99;

SELECT @Chosen = v.Value
FROM (VALUES (1)) AS v(Value)
WHERE v.Value = 2;
SELECT @Chosen AS AfterSelect; -- 99: no row, no assignment.

SET @Chosen =
    (SELECT v.Value
     FROM (VALUES (1)) AS v(Value)
     WHERE v.Value = 2);
SELECT @Chosen AS AfterSet;   -- NULL: scalar subquery has no row.

Multiple matching rows are more dangerous. A row-assignment SELECT can assign repeatedly and leave the last value it happens to process; without a meaningful order, that is not a dependable choice. A scalar subquery used with SET raises an error when it returns multiple rows, exposing the data assumption instead of hiding it. If exactly one row is required, enforce that rule in the query or data model.

My interview answer today is: choose the assignment semantics you need first, then test performance in context.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.