Difference between statement for declaring variable MSSQL

Question

DECLARE @Variable AnyType

SELECT @Variable = something

OR

DECLARE @Variable AnyType = something

• Erik Reasonable Rates Darling: There is some difference between using set and select for variable assignment, though.
• Dominique Boucher: I'm not sure to get what you really want to know... Why do you care about that ? Are you trying to fix an issue ?

Answer 1

There isn't much difference at all. For ages you could only declare a variable and assign a value in two separate steps, like this:

DECLARE @LongSelfDescriptiveVariableName int;
SET @LongAndSelfDescriptiveVariableName = 5;

Or like this:

DECLARE @LongSelfDescriptiveVariableName int;
SELECT @LongSelfDescriptiveVariableName = 5;

(And as Erik pointed out, there are differences between those two methods. The main difference for me is that I always use SELECT because it allows me to assign multiple variables instead of one at a time.)

In SQL Server 2008, they added inline variable assignment, so now there is a third way, and it saves typing:

DECLARE @LongAndLegibleSelfDescriptiveVariableName int = 5;

There is no performance or behavior difference, this is just a syntax shortcut. It is much more helpful if you use long and descriptive variable names; if your code is filled with @x and @y then reducing the number of times you have to specify those names makes the impact negligible.

One thing to be aware of is the scope of the statement when you are declaring and assigning multiple variables that have dependencies between them. For example:

DECLARE @now datetime;
DECLARE @today date;
DECLARE @tomorrow date;
SET @now = GETDATE();
SET @today = CONVERT(date, @now);
SET @tomorrow = DATEADD(DAY, 1, @today);

If you try to shorten this code and fit it into the newer method, you'll hit problems:

DECLARE @now datetime = GETDATE(),
 @today date = CONVERT(date, @now),
 @tomorrow date = DATEADD(DAY, 1, @today);

Msg 137 Level 15 State 2 Line 2
Must declare the scalar variable "@now".

However it works if you try to assign them in the same SELECT

DECLARE @now datetime;
DECLARE @today date;
DECLARE @tomorrow date;
SELECT @now = GETDATE(),
 @today = CONVERT(date, @now),
 @tomorrow = DATEADD(DAY, 1, @today);

Another way to work around this is to put each variable that depends on an earlier declared variable into a new DECLARE statement.

DECLARE @now datetime = GETDATE();
DECLARE @today date = CONVERT(date, @now);
DECLARE @tomorrow date = DATEADD(DAY, 1, @today);

Still shorter than the old way.

Answer 2

Nothing really, the second syntax is simply a shortcut.

To be picky, DECLARE @Variable AnyType = something is a shortcut for DECLARE @Variable AnyType; SET @Variable = something, not SELECT which imposes some limits, but if you are only assigning literal values or from deterministic functions it makes no actual difference. If you want to use SELECT in this shortcut you can do something like DECLARE @Variable Anytype = (SELECT something FROM some_table_or_view_or_more_complex_sql).