Difference Between SET and SELECT Statement In SQL Server..

Although there are plenty of blog's where you will get the differences between SET and SELECT. But there are only few where you will get logical and very important difference which should be taken care at the time of code writing.

Basically, SET is SQL ANSI standard for settings variables, SELECT is not. 

SET works only for single assignments, SELECT can do multiple assignments. 

The important point on which I'm going to focus is:

"A SELECT that does not fetch anything also does not set anything But its not the case in SET."

Let see the same in SSMS.

Use Master
Go
Create database  TestVK
Go
Use TestVK
Go
Create table Test1 (id int)
Go
Insert into Test1 values(1),(3)
Go
Select * from Test1

Now open a run the below queries and see the differences in output:

Declare @id int =-1
select @id = id from Test1 where id =7;
select @id

Output: -1

Which is incorrect. From here its clear that A SELECT that does not select or fetch anything also does not set anything But its not the case in SET 

Execute the below and check the result:

Declare @id int =-1
Set @id = (select id from Test1 where id =7) 
select @id

Output: NULL

I hope now its clear from above example. Please take care of same while writing codes.

Thanks For Reading This Blog!!!