Quoted Identifier

  • pprashanth

    SSC Enthusiast

    Points: 174

    Comments posted to this topic are about the item Quoted Identifier

  • Oleg Netchaev

    SSCertifiable

    Points: 5272

    Good question, thank you. I really hope that nobody actually thinks that it is OK to abuse the database just because the engine allows it.

    Oleg

  • ziangij

    SSCertifiable

    Points: 7261

    agreed, absolutely !

    Oleg Netchaev (6/2/2010)


    Good question, thank you. I really hope that nobody actually thinks that it is OK to abuse the database just because the engine allows it.

    Oleg

  • UMG Developer

    SSChampion

    Points: 13482

    Nice question, thanks!

    Again, I probably wouldn't hire anyone who admitted to doing this. 🙂

  • ralm

    Hall of Fame

    Points: 3156

    Good.

    but when tried with SET QUOTED_IDENTIFIER OFF; also it is working fine. (in SQL Server 2008).

    [font="Verdana"]Regards,
    Rals
    [/font].
  • Øystein Fallo

    SSCommitted

    Points: 1835

    rajesh.subramanian (6/3/2010)


    Good.

    but when tried with SET QUOTED_IDENTIFIER OFF; also it is working fine. (in SQL Server 2008).

    That would be because Manangement Studio default options is setting QUOTED_IDENTIFIER ON.

    The session-option overrides database setting.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Good question. (Although I hate the ALL CAPS in the code).

    The QUOTED_IDENTIFIER option is a rather odd one. Frankly, I am surprised that it's not deprecated, as its only use is for backwartd compatibility.

    Ancient versions of SQL Server allowed you to enclose string constans in "double quotes" instead of 'single quotes'. That bit Microsoft when the ANSI standard for SQL decided that 'single quotes' are for delimiting string constants, and "double quotes" are for delimiting identifier names. So in order to adhere to the standard, Microsoft had to change the meaning of the "double quote"; in order to not break existing code, SET QUOTED_IDENTIFIER was introduced.

    All major non-Microsoft relational databases use 'single quotes' te delimit strings, "double quotes" to delimit identifiers, and [square brackets] to force syntax errors. MS-Access uses 'single quotes' to start inline comments, "double quotes" to delimit strings, and [square brackets] to delimit identifiers. And SQL Server? Well, under the recommended option (QUOTED_IDENTIFIER ON), it uses 'single quotes' for strings, "double quotes" for identifiers (as per ANSI standard), and [square brackets] for identifiers as well, but this can be changed to use both 'single' and "double" quotes for strings and [square brackets] only for identifiers.

    @UMG Developer - I hope the "not hiring" comment refers to the use of "identity" and "string" as identifier names, not to the use of double quotes. Using such identifiers is indeed bad practice, using double quotes to delimit them is not - if might be an indication that the developer has a thousand years of experience in other databases, or that (s)he tries to keep code portable. To name a random example of a developer who prefers double quotes over square brackets when delimiting identifiers: me.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    Right answer, wrong reason. So I got the point and I learned something, cool!

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    As usual, some comments in the forum about the question are as educational as the question and explanation. Thanks.

  • UMG Developer

    SSChampion

    Points: 13482

    Hugo, yes, my comment is in reference to the naming choices not the use of quotes.

    I understand why people might like to use quotes instead of []s, but I personally prefer the []s when necessary.

  • SQLRNNR

    SSC Guru

    Points: 281252

    Good question - Thanks

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 11 posts - 1 through 11 (of 11 total)

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