6

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.

  1. 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:

  1. 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
  2. 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.

  • 2
    Code golf? – Martin Smith May 17 '16 at 21:06
  • 1
    Additional fodder for for Rob Volk? sqlbits.com/Sessions/Event12/Revenge_The_SQL – Kenneth Fisher May 17 '16 at 21:37
  • 1
    MSDN seems consistent. Must be 1-128 and the @ is part of the name. So @ plus 127 chars is ok but @ plus 128 chars isn't. Got "The identifier that starts with '@aaa...aaa' is too long. Maximum length is 128." in 2008 R2 when I did @ with 128 "a"s. – Riley Major May 18 '16 at 21:06
1

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:

object_name                      counter_name    instance_name
-----------------------------    ------------    -------------------------------
SQLServer:Deprecated Features    Usage           '@' and names that start with '@@' as
                                                 Transact-SQL identifiers

SQLServer:Deprecated Features    Usage           '#' and '##' as the name of temporary
                                                 tables and stored procedures

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":

Deprecated feature                     Replacement                               Feature ID
-----------------------------------    --------------------------------------    ----------
Use of #, ## as temporary table and    Use at least one additional character.    185
temporary stored procedure names.

Use of @, @@, or @@ as                 Do not use @ or @@ or names that begin    186
Transact-SQL identifiers.              with @@ as identifiers.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.