Keywords

  • Comments posted to this topic are about the item Keywords

    M&M

  • Easy question.

    But what's the deal with QUOTED_IDENTIFIER, because the statements works whatever it's setting is.

    (was it a distraction from the real question?)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (2/14/2011)


    Easy question.

    But what's the deal with QUOTED_IDENTIFIER, because the statements works whatever it's setting is.

    (was it a distraction from the real question?)

    True,it's a distraction

    mohammed want to say that irrespect of the QUOTED_IDENTIFIER The square brackets are always leagal.

  • Yes, I agree with Sharath. But many of them got it right though 🙂

    M&M

  • Nice easy one.. Thanks

    Thanks

  • Thanks for the question--as already stated nice and easy :-).

    One word on the distraction, though.

    Apparently BOL is not quite consistent in this matter.

    While the explanation on QUOTED_IDENTIFIER (http://msdn.microsoft.com/en-us/library/ms174393.aspx) states:

    SET QUOTED_IDENTIFIER must be ON when reserved keywords are used for object names in the database.

    the page on IDENTIFIERS (http://msdn.microsoft.com/en-us/library/ms175874.aspx) does not make a mention of this option at all and simply states:

    Identifiers that do not comply with all the rules for identifiers must be delimited in a Transact-SQL statement.

    Is this a simplification due to the fact that by default quoted identifiers are ON?

    Anyway, I guess the best thing to do is to not use reserved keywords as identifiers for any database object. But then, who would actually use those as table or column names, etc.?

    Regards,

    Michael

  • michael.kaufmann (2/15/2011)


    Anyway, I guess the best thing to do is to not use reserved keywords as identifiers for any database object. But then, who would actually use those as table or column names, etc.?

    There are a lot of identifiers and some of them could make actually logic table/column names (I don't say they make good table or column names), such as:

    key, source, date et cetera.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (2/15/2011)

    There are a lot of identifiers and some of them could make actually logic table/column names (I don't say they make good table or column names), such as:

    key, source, date et cetera.

    True--that's why I attempt (I'm not saying there's always success to the attempt) to avoid these and use tblkey, source_data, date_start, date_end, date_submit, etc. (as often as possible).

    One reason may well be that a German keyboard doesn't make it that easy to use brackets [], as you have to press CTRL + ALT + 8 or 9 (or AltGr + 8 or 9, if available).

    Regards,

    Michael

  • I don't know if the explanations in BOL are correct or not, but here is the deal.

    The ANSI standard says that double quotes should be used to delimit identifiers, and single quotes for string constants. Brackets are not defined as special character. So the first query below would be invalid in ANSI, but is valid in SQL Server; the second query is the ANSI equivalent.

    -- First query

    SELECT 'Constant string' AS [Column name];

    -- Second query

    SELECT 'Constant string' AS "Column name";

    SQL Server uses the brackets as delimtiers, probably in an effort to create some sort of compatibility with Access. Or maybe because of legacy reasons - I can't check, but I would not be surprised if very early versions of SQL Server accept double quotes as string delimiters. So these very old SQL Server versions would create the same output as the queries above by running:

    -- Third query

    SELECT "Constant string" AS [Column name];

    I think the QUOTED_IDENTIFIER setting is to preserve backwards compatibility. With QUOTED_IDENTIFIER set to OFF, SQL Server reverts to its legacy behaviour where both single and double quotes delimit strings, and only brackets delimit idenitifeers. With QUOTED_IDENTIFIER set to ON, we get the ANSI standard behaviour where single quotes delimit strings and double quotes delimit identifiers, with the support for brackets as identifier delimiters kept in as a bonus. So you could get the third quuery to run without error message if you first SET QUOTED_IDENTIFIER OFF.


    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/

  • michael.kaufmann (2/15/2011)


    One reason may well be that a German keyboard doesn't make it that easy to use brackets [], as you have to press CTRL + ALT + 8 or 9 (or AltGr + 8 or 9, if available).

    The obvious solution would be to start using ANSI-compliant double quotes to delimit identifiers that need to be delimited. :Whistling:


    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/

  • Hugo Kornelis (2/15/2011)

    The obvious solution would be to start using ANSI-compliant double quotes to delimit identifiers that need to be delimited. :Whistling:

    Hugo,

    first of all thanks for your explanation in your previous post and the sample code clearly describing the different outcome based on the quoted identifier setting.

    And I wasn't aware of the ANSI compliant variant using double quotes, as the examples in BOL as well as any other code I've seen so far exclusively made use of brackets.

    So thank you very much for making this point, and I'll definitely start using double quotes :-P.

    Regards,

    Michael

  • Hugo Kornelis (2/15/2011)


    I think the QUOTED_IDENTIFIER setting is to preserve backwards compatibility. With QUOTED_IDENTIFIER set to OFF, SQL Server reverts to its legacy behavior where both single and double quotes delimit strings, and only brackets delimit idenitifeers. With QUOTED_IDENTIFIER set to ON, we get the ANSI standard behavior where single quotes delimit strings and double quotes delimit identifiers, with the support for brackets as identifier delimiters kept in as a bonus. So you could get the third query to run without error message if you first SET QUOTED_IDENTIFIER OFF.

    As per me, you are correct. We had few stored procedures which were developed during SQL 2000 and due to critical procedures, they were available with latest schema though we upgraded SQL version to SQL 2008. We were created the procedures with SET QUOTED_IDENTIFIER OFF.

    Just before few days, I had converted the procedures & now they are compatible with SET QUOTED_IDENTIFIER ON.

    I also heard that Microsoft should stop the usage of SET QUOTED_IDENTIFIER OFF while creating any objects.

    Thanks

  • also heard that Microsoft should stop the usage of SET QUOTED_IDENTIFIER OFF while creating any objects.

    Regards,

    Hardik Doshi

    Out of curiosity in 2008 I checked for depreciated items with the following:

    SELECT * FROM sys.dm_os_performance_counters WHERE object_name LIKE ('%deprecated%')

    AND instance_name LIKE('%SET%')

    Although eleven SET statements were displayed, SET QUOTED_IDENTIFIER was not one of those.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Thanks for the question!

  • Thanks for the question, and to all who contributed to the ensuing discussion - very informative.

Viewing 15 posts - 1 through 15 (of 16 total)

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