Difference
between SET and SELECT in SQL Server
-
S.No SET SELECT 1 Is it ANSI Standard ?SET is ANSI Standard for value assignment to variables.Is it ANSI Standard ?SELECT is Non-ANSI Standard for value assignment to variables.2 Variable Assignment:SET can be used to assign value to one variable at a time.DECLARE @i INT,@j INT,@k INTSET @i = 10,@j = 20,@k = 30It gives error:Msg 102, Level 15, State 1, Line 5Incorrect syntax near ‘,’.Variable Assignment:SELECT can be used to assign values to multiple variables in a single SELECT statement.The below query using SELECT is valid:12345DECLARE @i INT,@j INT,@k INTSELECT @i = 10,@j = 20,@k = 30Output:Command(s) completed successfully.The below query using SET is not valid:123453 Behaviour of SET when query returns more then one value:When assigning from a query that returns more than one value, SET will fail with an error.The below query using set will fail:123DECLARE @i INTSET @i = (SELECT n FROM (VALUES (10),(20),(30)) AS List(n))Error:Msg 512, Level 16, State 1, Line 5Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Behaviour of SELECT when query returns more then one value:When assigning from a query that returns more than one value, SELECT will assign the last value returned by the query and hide the fact that the query returnedmore than one row.The below query using select will execute successfully:12345DECLARE @i INTSELECT @i = n FROM (VALUES (10),(20),(30)) AS List(n)SELECT @iOutput:304 Behaviour of SET when query does not return any rows:If the variable is initially assigned a value following is the behavior of variable assignment for SET,Assigns null if the query does not return any rows.The output of the below statement will be NULL1234567DECLARE @i INTSET @i = 1SET @i = (SELECT n FROM (VALUES (10),(20),(30)) AS List(n) WHERE 1=2)SELECT @iOutput:NULLBehaviour of SELECT when query does not return any rows:If the variable is initially assigned a value following is the behavior of variable assignment for SELECT,Retains the initially assigned value and does not assign null if the query does not return any rows.The output of the below statement will be 11234567DECLARE @i INTSET @i = 1SELECT @i = n FROM (VALUES (10),(20),(30)) AS List(n) WHERE 1=2SELECT @iOutput:15 Performance:Set does not provide better performance over select when used for assigning values to multiple variables at the same time.Assigning values to multiple variables using SET:1234567DECLARE @i INT,@j INT,@k INTSET @i = 10SET @j = 20SET @k = 30Performance:Select has better performance over set when used for assigning values to multiple variables at the same time.Assigning values to multiple variables using Select:12345DECLARE @i INT,@j INT,@k INTSELECT @i = 10,@j = 20,@k = 306 When to use ?Following are few scenarios for using SET1. If we are required to assign a single value directly to variable and no query is involved to fetch value.2. NULL assignments are expected (NULL returned in result set)3. Standards are meant to be follow for any planned migration4. Non scalar results are expected and are required to be handledWhen to use ?Using SELECT is efficient and flexible in the following few cases.1. Multiple variables are being populated by assigning values directly2. Multiple variables are being populated by single source (table , view)3. Less coding for assigning multiple variables4. Use this if we need to get @@ROWCOUNT and @ERROR for last statement executed
No comments:
Post a Comment