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