I just discovered, through sheer brilliance accidentally, that SQL Server allows you to create variables, parameters, table variables, temporary tables (local and global), and temporary stored procedures (local and global) without any name! Well, at least not in what I would consider to be a name. Meaning, you can specify DECLARE @ INT = 5;
and that is valid T-SQL: it executes without error and is not even flagged with a squiggly red underline in SSMS (code with full list of examples shown at the end of this Question).
Given that the MSDN page for Database Identifiers states (emphasis mine):
There are two classes of identifiers:
Regular identifiers
…
Delimited identifiers
…
Both regular and delimited identifiers must contain from 1 through 128 characters. For local temporary tables, the identifier can have a maximum of 116 characters.
and this certainly does not seem like intended behavior, I initially considered this to be a defect (doesn't cause any errors, but doesn't seem to be "correct" behavior) and filed a Connect bug: Parameter, Variable, and Temporary Table & Procedure names / identifiers can be empty.
HOWEVER, that same MSDN page also states:
Rules for Regular Identifiers
The names of variables, functions, and stored procedures must comply with the following rules for Transact-SQL identifiers.
The first character must be one of the following:
- A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages.
The underscore (_), at sign (@), or number sign (#).
Certain symbols at the beginning of an identifier have special meaning in SQL Server. A regular identifier that starts with the at sign always denotes a local variable or parameter and cannot be used as the name of any other type of object. An identifier that starts with a number sign denotes a temporary table or procedure. An identifier that starts with double number signs (##) denotes a global temporary object. Although the number sign or double number sign characters can be used to begin the names of other types of objects, we do not recommend this practice.
So, it could be interpreted that the minimum requirement of 1 character for the name (from the top quoted piece showing "1 through 128 characters") would be satisfied by simply having either @
or #
since:
- it states that those would be the "first" character instead of some type of external indication of the type of thing that it is, and
- there is nothing stating that when either
@
or#
are the first character, that 2 through 128 characters are required.
SO, if this behavior is neither bug nor defect, then is there any benefit to making use of this ability? Is there a legitimate use-case that is benefited by being able to do this (i.e. some functional benefit to a project and not merely reducing character count of the source code)?
I cannot think of one, outside of being unprofessional and leaving horribly unmanageable code for everyone as you move to a new employer.
But I can also see a related functional issue in that there are numerous blocks of code in many projects that parse T-SQL objects. If someone uses a RegEx pattern looking for @
followed by something like (\w+)
, then it would skip these entries.
-- Local variables:
DECLARE @ INT = 99;
DECLARE @@ VARCHAR(10);
SELECT CONVERT(VARCHAR(20), @), STR(@);
SET @@ = STR(@);
SELECT @@;
GO
-- Table Variable:
DECLARE @ TABLE (Col1 INT);
INSERT INTO @ (Col1) VALUES (86);
SELECT * FROM @;
GO
-- Local Temporary Table:
CREATE TABLE # (Col2 DATETIME);
INSERT INTO # (Col2) VALUES (GETDATE());
SELECT * FROM #;
SELECT OBJECT_ID(N'tempdb..#');
GO
-- Global Temporary Stored Procedure
CREATE PROCEDURE ## ( @ INT = 999 )
AS
SELECT @ AS [Huh?];
GO
EXEC ##;
EXEC ## 204;
DECLARE @ INT = 12345;
EXEC ## @ = @;
-- The above also work in another session on the same instance, in a different Database
SELECT * FROM tempdb.sys.parameters tsp WHERE tsp.[object_id] = OBJECT_ID(N'tempdb..##');
-- returns 1 row showing a name of just "@"
P.S. I had searched on here, the Googles, and Microsoft Connect and couldn't find any references to "missing" or "empty" variable names or "identifiers". However, @MartinSmith did point out this "ability" being used in the following StackOverflow answer to reduce the character count of the code: Build an ASCII chart of the most commonly used words in a given text.
Best Answer
It seems that this behavior / ability is both known and deprecated. I was looking through the sys.dm_os_performance_counters DMV the other day and noticed the following two entries:
I then checked the MSDN documentation and found both of these noted on the Deprecated Database Engine Features in SQL Server 2016 page, in the Features Not Supported in a Future Version of SQL Server section, under the Category of "Transact-SQL":
The earliest reference I could find for this deprecation notice was in the SQL Server 2008 documentation. And while the version drop-down on that "Deprecated Database Engine Features" page does not have an entry for SQL Server 2005, you can still get to that documentation via, https://msdn.microsoft.com/en-us/library/ms143729(v=sql.90).aspx, and see that neither of these items are listed.
That information leads me to the following conclusions:
Question Part 1 (is this a bug or intended behavior):
It is intended, albeit undesirable, behavior.
Question Part 2 (is there a benefit to this behavior):
Not only does there not seem to be any beneficial use-case here, even if there was, it wouldn't matter too much due to this behavior / ability being deprecated and hence not something that should be used going forward, especially in new code / projects.