Home » SQL Server » SQL SERVER – SELECT vs. SET Performance Comparison

SQL SERVER – SELECT vs. SET Performance Comparison


While testing the performance of the following two scripts in query analyzer, interesting results are discovered.

SET @var1 = 1;
SET @var2 = 2;
SET @var3 = 3;
SELECT
@var1 = 1,
@var2 = 2,
@var3 = 3;

While comparing their performance in loop SELECT statement gives better performance then SET. In other words, SET is slower than SELECT. The reason is that each SET statement runs individually and updates on values per execution, whereas the entire SELECT statement runs once and update all three values in one execution.

SET is the ANSI standard for variable assignment, SELECT is not.

SET can only assign one variable at a time, SELECT can make multiple assignments at once – that gives SELECT slight speed advantage over SET.

If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned.

When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all keeping the variable unchanged.

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

%d bloggers like this: