Here is The Combined answer that i have collected from various articles and experience.
Here is the Difference.
1. SET is the ANSI standard for variable assignment, SELECT is not. 2.You can use SELECT to assign values to more than one variable at a time. SET allows you to assign data to only one variable at a time. Here's how:
/* Declaring variables */
DECLARE @Variable1 AS int, @Variable2 AS int
/* Initializing two variables at once */
SELECT @Variable1 = 1, @Variable2 = 2
/* The same can be done using SET, but two SET statements are needed */
SET @Variable1 = 1
SET @Variable2 = 2
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).
4. When using a query to populate a variable, SET will fail with an error, if the query returns more than one value. 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 code could go unnoticed with SELECT, and this type of bugs is hard to track down too. Here is an example:
/* Consider the following table with two rows */
SET NOCOUNT ON
CREATE TABLE #Test (i int, j varchar(10))
INSERT INTO #Test (i, j) VALUES (1, 'First Row')
INSERT INTO #Test (i, j) VALUES (1, 'Second Row')
GO
/* Following SELECT will return two rows, but the variable gets its value from one of those rows, without an error.
This may not be what you were expecting. Since no error is returned,
you will never know that two rows existed for the condition, WHERE i = 1 */
DECLARE @j varchar(10)
SELECT @j = j FROM #Test WHERE i = 1
SELECT @j
GO
/* 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 #Test WHERE i = 1)
SELECT @j
Server: Msg 512, Level 16, State 1, Line -1074284106
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, I suggest you always use SET, if you want to be sure that only one row is returned. If you hate SET for some reason, you could get the same behavior of SET, using SELECT, as shown below:
DECLARE @j varchar(10)
SELECT @j = (SELECT j FROM #Test WHERE i = 1)
SELECT @j
Here is another difference with respect to assigning values based on a query, especially when the query doesn't return any rows. Run the following example in the pubs database, and you will see what I mean:
/* Returns NULL */
DECLARE @Title varchar(80)
SET @Title = 'Not Found'
SET @Title =
(
SELECT title
FROM dbo.titles
WHERE title_id = 'InvalitTitleID'
)
SELECT @Title
GO
/* Returns the string literal 'Not Found' */
DECLARE @Title varchar(80)
SET @Title = 'Not Found'
SELECT @Title = title
FROM dbo.titles
WHERE title_id = 'InvalitTitleID'
SELECT @Title
GO
Performance Wise 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 you have a loop in your 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 @TestVar1 = @TestVar1 + 1, @TestVar2 = @TestVar2 - 1, @CTR = @CTR + 1
Thanks..
Here is the Difference.
1. SET is the ANSI standard for variable assignment, SELECT is not. 2.You can use SELECT to assign values to more than one variable at a time. SET allows you to assign data to only one variable at a time. Here's how:
/* Declaring variables */
DECLARE @Variable1 AS int, @Variable2 AS int
/* Initializing two variables at once */
SELECT @Variable1 = 1, @Variable2 = 2
/* The same can be done using SET, but two SET statements are needed */
SET @Variable1 = 1
SET @Variable2 = 2
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).
4. When using a query to populate a variable, SET will fail with an error, if the query returns more than one value. 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 code could go unnoticed with SELECT, and this type of bugs is hard to track down too. Here is an example:
/* Consider the following table with two rows */
SET NOCOUNT ON
CREATE TABLE #Test (i int, j varchar(10))
INSERT INTO #Test (i, j) VALUES (1, 'First Row')
INSERT INTO #Test (i, j) VALUES (1, 'Second Row')
GO
/* Following SELECT will return two rows, but the variable gets its value from one of those rows, without an error.
This may not be what you were expecting. Since no error is returned,
you will never know that two rows existed for the condition, WHERE i = 1 */
DECLARE @j varchar(10)
SELECT @j = j FROM #Test WHERE i = 1
SELECT @j
GO
/* 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 #Test WHERE i = 1)
SELECT @j
Server: Msg 512, Level 16, State 1, Line -1074284106
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, I suggest you always use SET, if you want to be sure that only one row is returned. If you hate SET for some reason, you could get the same behavior of SET, using SELECT, as shown below:
DECLARE @j varchar(10)
SELECT @j = (SELECT j FROM #Test WHERE i = 1)
SELECT @j
Here is another difference with respect to assigning values based on a query, especially when the query doesn't return any rows. Run the following example in the pubs database, and you will see what I mean:
/* Returns NULL */
DECLARE @Title varchar(80)
SET @Title = 'Not Found'
SET @Title =
(
SELECT title
FROM dbo.titles
WHERE title_id = 'InvalitTitleID'
)
SELECT @Title
GO
/* Returns the string literal 'Not Found' */
DECLARE @Title varchar(80)
SET @Title = 'Not Found'
SELECT @Title = title
FROM dbo.titles
WHERE title_id = 'InvalitTitleID'
SELECT @Title
GO
Performance Wise 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 you have a loop in your 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 @TestVar1 = @TestVar1 + 1, @TestVar2 = @TestVar2 - 1, @CTR = @CTR + 1
Comments