Difference Between SET and SELECT commands in SQL Server

SET	SELECT
Can assign the value of an expression to the variable	Can assign the value of an expression to the variable
Cannot retrieve data from data source. It can only retrieve data from the expressions.	Can retrieve data from data source and also uses other SELECT clauses (WHERE, FROM etc)
Use SET only when you want to assign value of a function result or constant to a variable	Can use for both i.e. retrieve value of function result

Example

–Variable declaration
DECLARE
    @vName VARCHAR(10),
    @vobject_id VARCHAR(30),
    @vName1 VARCHAR(30);

–assigning a single value
SET @vName1=’Test Value’;

–Selecting and retrieving multiple values in a single select statement
SELECT
    @vName=name,
    @vobject_id=object_id
FROM
    sys.objects
WHERE
    name =’sysrowsetcolumns’

–printing the retrieved values
SELECT  @vName1,@vName, @vobject_id

–By Harsh Shah