Post reply

Views whose names begin with a number

  • April 24, 2007 at 1:20 pm

    #175939

    Greetings,

    Why does the Query window require me to bracket a view named 1vw_MyView when selecting from it (i.e. select * from [1vw_MyView]).  If I don't put brackets around it I get the following error:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '.1'.

    If I create the same view and name it without the initial "1" (i.e. vw_MyView), it returns just fine without the syntax error.  Is there a limitation on naming views beginning with a number?  Thanks.

  • April 24, 2007 at 1:31 pm

    #702246

    Mostly, because it's just not allowed... read the following from Books Online...

    Rules for Regular Identifiers

    The rules for the format of regular identifiers are dependent on the database compatibility level, which can be set with sp_dbcmptlevel. For more information, see sp_dbcmptlevel. When the compatibility level is 80, the rules are:

    1. The first character must be one of the following:

      • A letter as defined by the Unicode Standard 2.0. The Unicode definition of letters includes Latin characters from a through z and from A through Z, in addition to 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. An identifier beginning with the "at" sign denotes a local variable or parameter. An identifier beginning with a number sign denotes a temporary table or procedure. An identifier beginning with double number signs (##) denotes a global temporary object.

        Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, it is recommended that you do not use names that start with @@.

    2. Subsequent characters can be:

      • Letters as defined in the Unicode Standard 2.0.
      • Decimal numbers from either Basic Latin or other national scripts.
      • The "at" sign, dollar sign ($), number sign, or underscore.

    3. The identifier must not be a Transact-SQL reserved word. SQL Server reserves both the uppercase and lowercase versions of reserved words.
    4. Embedded spaces or special characters are not allowed.

    When used in Transact-SQL statements, identifiers that fail to comply with these rules must be delimited by double quotation marks or brackets.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 24, 2007 at 3:23 pm

    #702276

    Thanks Jeff.  I figured that it was a rule in the RDBMS, but couldn't find documentation on why.  BOL explains it.  Thanks for the quick reply.

  • April 28, 2007 at 9:59 pm

    #703136

    why on earth would you want to name your objects like that?  that's very non-standard.

    ---------------------------------------
    elsasoft.org

  • May 2, 2007 at 8:20 am

    #703818

    Wouldn't necessarily name objects this way.  I was just wondering why this limitation exists in SQL Server.  Oracle doesn't have this problem.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply