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.
