SET vs SELECT

Pinal Dave, Blogger and Speaker, posted about  Interview Question of the Week #044 – What is the difference of performance between SELECT and SET?

Here are some more examples on the difference

1 SET can assign a single value to a variable

DECLARE @I INT
SET @I=10
SELECT @I

SELECT can assign a value to a variable and will SELECT and display it too

DECLARE @I INT
SELECT @I=10
SELECT @I as result

2 If SET uses a query to assign a value to a variable and there is no result set returned from a query, variable will be assigned a NULL value

DECLARE @I INT
SET @I=10
SELECT @I as result1
SET @I=(SELECT 1 WHERE 1=0)
SELECT @I as result2

If you execute the above code, it returns two result sets. Result1 is 10 and Result2 is NULL. As SELECT 1 WHERE 1=0 does not return any value

If you use SELECT in the above example result2 is still 10. It is because when there is no result set returned, SELECT retains the old value of variable.

DECLARE @I INT
SET @I=10
SELECT @I as result1
SELECT @I = 1 WHERE 1=0
SELECT @I as result2

Both SELECTs return 10 as result.

If you want to assign a value to a variable, always use SET

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s