Difference between statement for declaring variable MSSQL

Question

DECLARE @Variable AnyType

SELECT @Variable = something

OR

DECLARE @Variable AnyType = something

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).