Sunday, 25 August 2013

SQL SERVER - SET VS SELECT


SET - It is designed to assign the values.
SELECT - It is designed to Select the result set.

Example 


DECLARE @S INT ,@D INT

SET @S = 2
SET @D = 4


SELECT @S = 1,@d = 3

Error on declare like follow.

SET @S = 2,@D = 5


SET 
  1. It is ANSI Standard.
  2. More than one variable can't be assign using one set statement.
  3. It will assign only scalar value, if multiple row are return from query than raise error.
  4. If the return query set doesn't have values for the column then SET will default the value as NULL.
  5. It will execute on each and every statement of assign.so time taken for execution.
  6. Performance wise slow when assign a value to variable when compare to SELECT.
SELECT
  1. More than one variable can be assigned at a time.
  2. If more than one value is returned from query then , it will assign one value from that result.
  3. It will not default assign the value NULL. when the query returns no records.
  4. Performance wise faster than SET, Because it will execute to assign any variables at a time.


From this article I hope you can understand the difference between the SET and SELECT.

No comments:

Post a Comment