Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Parameter, Variable, and Temporary Table & Procedure names / identifiers can be empty

For some strange reason you are allowed to create Parameters, Local Variables, Table Variables, Local & Global Temporary Tables, and Local & Global Temporary Stored Procedures with empty / missing names / identifiers. Yet the MSDN page for Database Identifiers ( https://msdn.microsoft.com/en-us/library/ms175874.aspx ) states:

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.

This "defect" doesn't cause any errors, though does allow for writing some seriously unmaintainable code. It will also likely break most custom code that people have written to parse parameter info using RegEx, assuming the pattern is based on "@" plus "1 or more word characters".

I have tested this on SQL Server 2012, SP2 and SQL Server 2014, SP1.

0 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 2

    <=-=May 23 2016 2:11PM=-=>

    I just discovered that this behavior is both known and deprecated; it is listed on the “Deprecated Database Engine Features in SQL Server 2016” MSDN page, in the “Features Not Supported in a Future Version of SQL Server” section ( https://msdn.microsoft.com/en-us/library/ms143729.aspx#Anchor_1 ):

    FeatureID = 185: Use of #, ## as temporary table and temporary stored procedure names.
    FeatureID = 186: Use of , @, or @@ as Transact-SQL identifiers.

    The earliest reference I could find for the deprecation notice was in the 2008 documentation:

    https://msdn.microsoft.com/en-us/library/ms143729(v=sql.100).aspx#Anchor_1

    <=-=May 24 2016 9:01AM=-=>

    I forgot to mention in the other comment that this item should probably be closed as “By Design”.

    2 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base