Tuesday, August 2, 2016

SQL Server Variable Assignment - SET or SELECT?

Declaration variables and assigning values in different ways is something we see as a very common code in modules like stored procedures and functions. It is possible to assign a value at the declaration or a value can be assigned after the declaration either using SET or SELECT. A question raised on it, what would be the best and which gives better performance, when assigning the value using SET or SELECT. This is what I explained;

Let's see the differences one by one;
1. Only one variable can be set with a value with a single SET statement but with the SELECT statement, multiple variables can be set with values using a single SELECT statement.

-- Declaring variables without assigning values
DECLARE @Variable01 int;
DECLARE @Variable02 int;

-- New two SET statements for assigning values
SET @Variable01 = 100; SET @Variable02 = 200; 
SELECT @Variable01, @Variable02;

-- Assigning values using a single SELECT
SELECT @Variable01 = 300, @Variable02 = 300;
SELECT @Variable01, @Variable02;
GO


2. Both SET and SELECT support assigning values using a query. Just like the number (1), SELECT can be used for setting multiple variables.

USE WideWorldImporters;
GO

-- Declaring variables without assigning values
DECLARE @Variable01 int;
DECLARE @Variable02 int;

-- Assigning a value using a query, this works as expected
SET @Variable01 = (SELECT COUNT(*) FROM Sales.Customers); 
SELECT @Variable01;

-- Assigning values using a query, this works as expected
SELECT @Variable01 = COUNT(*), @Variable02 = AVG(CustomerID) FROM Sales.Customers

SELECT @Variable01, @Variable02;
GO


3. When assigning values using a query, if the query returns more than one record, SET returns an error whereas SELECT takes the first record and assign the value to the variable.

USE WideWorldImporters;
GO

-- Declaring variables without assigning values
DECLARE @Variable01 int;
DECLARE @Variable02 int;

-- Assigning a value using a query, this query returns more than one value
-- , hence SET throws an error
SET @Variable01 = (SELECT CustomerID FROM Sales.Customers); 
SELECT @Variable01;

-- Assigning a value using a query, this query returns more than one value
-- , but SELECT takes the first value without throwing an error
SELECT @Variable01 = CustomerID FROM Sales.Customers

SELECT @Variable01, @Variable02;
GO


4. When assigning values using a query, if the query returns no record, NULL will be set to the variable with SET statement but SELECT statement will keep the old value without changing.

USE WideWorldImporters;
GO

-- Declaring variables, assigning values
DECLARE @Variable01 int = 0;
DECLARE @Variable02 int = 0;

-- Assigning a value using a query
-- This query does not return any record, hence variable becomes NULL
SET @Variable01 = (SELECT CustomerID FROM Sales.Customers WHERE BuyingGroupID = 100 ); 

-- Assigning a value using a query
-- This query does not return any record, but initial value will not be replaced with a NULL
SELECT @Variable02 = CustomerID FROM Sales.Customers WHERE BuyingGroupID = 100;

SELECT @Variable01, @Variable02;

5. One more point, remember that SET is ANSI standard for assigning variables but SELECT is not.

No comments:

Post a Comment