DECLARE @Variable AnyType
SELECT @Variable = something
DECLARE @Variable AnyType = something
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
@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
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 @now datetime = GETDATE(); DECLARE @today date = CONVERT(date, @now); DECLARE @tomorrow date = DATEADD(DAY, 1, @today);
Still shorter than the old way.
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).