This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing the major difference between SET and SELECT, which we are using for assigning a value to the Variable of SQL Server.
What I have found is, the people are always confused to use SET or SELECT for assigning a value to the Variable and which one is faster ?
SET is the ANSI standard, and SELECT is not the ANSI standard.
Using SET, we can only assign a scalar value, and if the query is returning multiple values, SET will raise an error.
In contrast, SELECT never raises an error if the query is returning multiple values.
Using SET, we can assign one variable at a time, using SELECT we can assign multiple variables at a time.
There are not major differences in speed and performance, but SELECT is slightly faster than SET because it can perform multiple assignments at a time.
As per the ANSI standard, If there is no value returned, SET will assign NULL and SELECT will not assign any value and variable will not be changed from its previous value.
For this Difference, I prepared a small demonstration here.
Create table with Sample Data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE [dbo].[tbl_Employee] ( EmpID INTEGER IDENTITY(1,1) ,EmpName VARCHAR(255) ,Gender CHAR(1) ,DOB DATETIME ,CONSTRAINT pk_tbl_Employee_EmpID PRIMARY KEY(EmpID) ) GO INSERT INTO [dbo].[tbl_Employee] (EmpName,Gender,DOB) VALUES ('Anvesh','M','19880126') ,('Roy','M','19930214') ,('Jenny','F','19920320') ,('Martin','M','19850616') GO |
Assigning variable using SET:
|
1 2 3 4 5 |
DECLARE @EmpName VARCHAR(255) SET @EmpName = 'Anu' SET @EmpName = (SELECT EmpName FROM [dbo].[tbl_Employee] WHERE DOB ='19880127') SELECT @EmpName /* @EmpName is NULL */ GO |
Assigning variable using SELECT:
|
1 2 3 4 |
DECLARE @EmpName VARCHAR(255) SET @EmpName = 'Anu' SELECT @EmpName = EmpName FROM [dbo].[tbl_Employee] WHERE DOB ='19880127' SELECT @EmpName /* @EmpName still having a value 'Anu' */ |
