SET vs SELECT !

Friday, 22 May 2009

SQL programmers  generally use SET/SELECT to assign the values to the variables.The following are the notable differences between SET and SELECT.

1.In SELECT,more than one variable can be used

declare @name varchar(36),@grade varchar(3)

select @name='john',@grade = '10'

on the other hand in SET ,only one variable can be used at a time

declare @name varchar(36),@grade varchar(3)

set @name='john'

set @grade='10'

2. SELECT can perform better than SET statement,

here's the test code to check the performance of the SET and SELECT.Its better test this in your local machine instead of the production server.

declare @startime datetime ,@i varchar(1000),@j bigint 

set @startime = current_timestamp 

set @j = 50000 

while @j > 0 

begin 

set @i='myteststring' 

set @j = @j -1 

end 

SELECT datediff(ms, @Startime, CURRENT_TIMESTAMP) AS 'Time taken by SET in ms'

select @startime = current_timestamp

select @j = 50000 

while @j > 0 

begin 

select @i='myteststring' 

select @j = @j -1

end 

select datediff(ms, @Startime, CURRENT_TIMESTAMP) AS 'Time taken by SELECT in ms'

3.When assigning from a query if there's no value returned SET will assign a NULL ,whereas SELELCT will ignore the statement, the variable will return the previous value.

declare @myvar varchar(10)

set @myvar = 'TEST'

set @myvar = (select name from sysobjects where 1<>1)

select @myvar

this block of code will return NULL,but the below code will return 'TEST'.

declare @myvar varchar(10)

select @myvar = 'TEST'

select @myvar= name from sysobjects where 1<>1

select @myvar

Conclusion : SET can be used when you want follow the coding standard and performance is not an issue.SELECT can be used when performance is a concern.