.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles


Posted By:Prasanti Prusty       Posted Date: December 22, 2009    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com  

SET and SELECT both key words are used to Assign Variables in SQL Server.

SET and SELECT both specifies the columns to be changed and the new values for the columns.
The values in the specified columns are updated with the values specified in the SET and SELECT in all rows that match the WHERE clause search condition.
If no WHERE clause is specified, all rows are updated.

There are some difference based on the Performance, Process like Follows :

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

2. SELECT can be used to assign values to more than one variable at a time, Whereas SET allows to assign data to only one variable at a time.

Example :

/* Declaring variables */
DECLARE @Var1 AS int, @Var2 AS int
/* The same can be done using SET, but two SET statements are needed */
SET @Var1 = 1
SET @Var2 = 2
/* Initializing two variables at once */
SELECT @Var1 = 1, @Var2 = 2
use SET instead SELECT, for variable initialization, It will throw the following error
SET @Var1 = 1, @Var2 = 2
, Level 15, State 1, Line 10

3. 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 .so the variable will not be changed from it's previous value.  

Example :

Run it in master Database in SQL Server.

/* Returns NULL */
DECLARE @Title varchar(80)
--SET @Title = 'Not Found'
SET @Title =
SELECT error
FROM SysMessages
WHERE Description = 'Invalid Description'
/* Returns the string literal 'Not Found' */
DECLARE @Title varchar(80)
SET @Title = 'Not Found'
SELECT @Title = error
FROM SysMessages
WHERE Description = 'Invalid Description'

4. Let using a query needs to populate a variable and the Query returns more than one value.
SET will fail with an error in this scenario.
But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row.

As a result, bugs in your the could go unnoticed with SELECT, and this type of bugs is hard to track down too.

Example :
/* Consider the following table with two rows */
TABLE #Table (i int, j varchar(10))
INSERT INTO #Table (i, j) VALUES (1, 'Sunday')
INSERT INTO #Table (i, j) VALUES (1, 'Monday')
/* Following SELECT will return two rows, but the variable gets its value from one of those rows, without an error.
you will never know that two rows existed for the condition, WHERE i = 1 */
DECLARE @j varchar(10)
SELECT @j = j FROM #Table WHERE i = 1
/* If you rewrite the same query, but use SET instead, for variable initialization, you will see the following error */
DECLARE @j varchar(10)
SET @j = (SELECT j FROM #Table WHERE i = 1)

Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Based on the above results, when using a query to populate variables, we should always use SET.
If you want to be sure that only one row is returned then only use SELECT, as shown below:

DECLARE @j varchar(10)
SELECT @j = (SELECT j FROM #Table WHERE i = 1)

5. This very feature of SELECT makes it a winner over SET, when assigning values to multiple variables. A single SELECT statement assigning values to 3 different variables, is much faster than 3 different SET statements assigning values to 3 different variables.
In this scenario, using a SELECT is at least twice as fast, compared to SET.

So, the conclusion is, if there is a loop in th stored procedure that manipulates the values of several variables, and if you want to squeeze as much performance as possible out of this loop, then do all variable manipulations in one single SELECT statement or group the related variables into few SELECT statements as show below:

SELECT @Var1 = @Var1 + 1, @Var2 = @Var2 - 1, @CNT = @CNT + 1

 Subscribe to Articles


Further Readings:

Author: Santosh         Company URL: http://www.dotnetspark.com
Posted Date: December 23, 2009

so nice code chikkul.p

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend