Traditionally, SQL Server database developers are accustomed to using SELECT for assigning values to variables. This was fine and a perfectly valid practice right until SQL Server 6.5. Microsoft released SQL Server 7.0 in 1999. SQL Server 7.0 introduced the new SET statement for initializing and assigning values to variables. SQL Server 7.0 Books Online also stated: "It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable."

This caused some confusion in the database developer community, as Microsoft never mentioned, why SET is recommended over SELECT for assigning values to variables. In this article, I will highlight all the major differences between SET and SELECT, and things you should be aware of, when using either SET or SELECT.

If you are completely new to T-SQL, then the following examples give you an idea of what I am talking about:

/* Declaring variables */
DECLARE @Variable1 AS int, @Variable2 AS int

/* Setting @Variable1 to a value of 1 using SELECT */
SELECT @Variable1 = 1

/* Setting @Variable2 to a value of 2 using SET */
SET @Variable2 = 2

Now coming to the differences between SET and SELECT! Are standards important to you? If your answer is 'yes', then you should be using SET. This is because, SET is the ANSI standard way of assigning values to variables, and SELECT is not.

Another fundamental difference between SET and SELECT is that, 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