Fun with qualifiers

  • sestell1

    SSChampion

    Points: 10230

    Comments posted to this topic are about the item Fun with qualifiers

  • This was removed by the editor as SPAM

  • helenlu7 65408

    SSC-Addicted

    Points: 425

    Only when the 'QUOTED_IDENTIFIER' is set as 'ON', can this query run successfully, otherwise, it will fail

    MCSE Data Platform; BI
    MCITP Database Developer; BI

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    I missed the most important correct answer: you'll get fired. 😀

    Luckily Shane does indicate in the explanation that using this type of table names is not a good idea.

    A bit of additional background for those interested.

    In the ANSI standard for SQL, the 'single quote' character is defined as the string delimiter, and the "double quote" is the delimiter for object names. Using [square brackets] is not supported at all.

    In Access, the 'single quote' is used for VB-style comments (if I remember correctly), and the "double quote" delimits strings, so Microsoft had to come up with something else for object names - and the [square bracket] was born.

    In SQL Server, Microsoft tried a halfhearted attempt at being compatible with both the ANSI standards (and hence the major competitors), and with Access (to facilitate upgrade from Access to SQL Server):

    - [square brackets] are always seen as delimiters for object names (Access style - ANSI SQL would throw an error)

    - 'single quotes' are always seen as string delimiters (ANSI style - Access would consider the rest of the line as a comment)

    - And "double quotes" can be interpreted either as delimiters for strings (Access style), or as delimiters for object names (ANSI SQL style), depending on the setting of the SET QUOTED_IDENTIFIER option and/or the QUOTED_IDENTIFIER database setting. The ON setting, which is default, and also a requirement for a lot of features, implies that "double quotes" are interpreted ANSI style, as object name delimiter.

    The effect of this is that, in theory, code ported from Access should work with SET QUOTED_IDENTIFIER OFF, and code ported from ANSI-compliant competitors should work with SET QUOTED_IDENTIFIER ON.

    Another effect of this setting is that 'single quote' for strings and [brackets] for object names always work, and are "safe". But "double quotes" are less safe, because code that works can stop working when someone tampers with the setting. This is probably the reason why all Microsoft's documentation, and all Microsoft's code generating tools will always use [square brackets] - and that in turn has resulted in [square brackets] being the de facto standard for all code I ever see on SQL Server.

    Which is a bummer if your boss ever decides to port the system to Oracle so that it can be sold to other companies. Now, apart from having to deal with all the "real" inconsistencies between the products, you will also have to replace all [object names] with "object names". And a simple search and replace won't work because [brackets] also have a special meaning in LIKE patterns (and that meaning really requires brackets, in all ANSI-compliant products).

    </rant>


    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/

  • Ed Wagner

    SSC Guru

    Points: 286957

    Even though it looked horrible, I'm not surprised by the answer. I must admit I liked Hugo's answer: Someone would get fired. Well, perhaps at least a strong talking-to. 😀 Nice question.

  • DrKiller

    Right there with Babe

    Points: 780

    Ed Wagner (12/1/2015)


    Even though it looked horrible, I'm not surprised by the answer. I must admit I liked Hugo's answer: Someone would get fired. Well, perhaps at least a strong talking-to. 😀 Nice question.

    He will investigate other possible careers 😉

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Hugo Kornelis (12/1/2015)


    I missed the most important correct answer: you'll get fired. 😀

    So true

  • sestell1

    SSChampion

    Points: 10230

    Thank you for the very interesting background behind the square bracket delimiter in SQL Server, Hugo. I had always assumed it was a more arbitrary decision on Microsoft's part. It's interesting to know the history behind it.

  • Rich Mechaber

    SSChampion

    Points: 10935

    Hugo Kornelis (12/1/2015)


    I missed the most important correct answer: you'll get fired. 😀

    Luckily Shane does indicate in the explanation that using this type of table names is not a good idea.

    A bit of additional background for those interested.

    In the ANSI standard for SQL, the 'single quote' character is defined as the string delimiter, and the "double quote" is the delimiter for object names. Using [square brackets] is not supported at all.

    In Access, the 'single quote' is used for VB-style comments (if I remember correctly), and the "double quote" delimits strings, so Microsoft had to come up with something else for object names - and the [square bracket] was born.

    In SQL Server, Microsoft tried a halfhearted attempt at being compatible with both the ANSI standards (and hence the major competitors), and with Access (to facilitate upgrade from Access to SQL Server):

    - [square brackets] are always seen as delimiters for object names (Access style - ANSI SQL would throw an error)

    - 'single quotes' are always seen as string delimiters (ANSI style - Access would consider the rest of the line as a comment)

    - And "double quotes" can be interpreted either as delimiters for strings (Access style), or as delimiters for object names (ANSI SQL style), depending on the setting of the SET QUOTED_IDENTIFIER option and/or the QUOTED_IDENTIFIER database setting. The ON setting, which is default, and also a requirement for a lot of features, implies that "double quotes" are interpreted ANSI style, as object name delimiter.

    The effect of this is that, in theory, code ported from Access should work with SET QUOTED_IDENTIFIER OFF, and code ported from ANSI-compliant competitors should work with SET QUOTED_IDENTIFIER ON.

    Another effect of this setting is that 'single quote' for strings and [brackets] for object names always work, and are "safe". But "double quotes" are less safe, because code that works can stop working when someone tampers with the setting. This is probably the reason why all Microsoft's documentation, and all Microsoft's code generating tools will always use [square brackets] - and that in turn has resulted in [square brackets] being the de facto standard for all code I ever see on SQL Server.

    Which is a bummer if your boss ever decides to port the system to Oracle so that it can be sold to other companies. Now, apart from having to deal with all the "real" inconsistencies between the products, you will also have to replace all [object names] with "object names". And a simple search and replace won't work because [brackets] also have a special meaning in LIKE patterns (and that meaning really requires brackets, in all ANSI-compliant products).

    </rant>

    Interesting history with Access, Hugo. Interconnected platforms, apparently, like it or not. Thanks for the perspective!

    As today's question was specifically about nitpicky delimiters for strings and objects, should we deduct points from your reply for not including an opening "<rant>" tag?? :hehe:

    Rich

  • Andy Warren

    SSC Guru

    Points: 119675

    Shane, that question made my eyes hurt!

    Hugo, that answer is why I love reading the QOTD, the follow on discussion is always worth reading. But I only see a closing rant tag - rookie mistake?:-)

  • Dain Bramage

    Valued Member

    Points: 70

    THANKS for an EXCELLENT QOD. This is one of the best I have seen on this site for 3 reasons.

    1. The question was asked in such a way that most people will give the correct answers which is encouraging.

    2. I quickly learned something I did not know. All too often the QOD becomes a research project. Most of us working people do not have that kind of time for the QOD.

    3. Giving us code to run can be fun too. It's always cool to see it in action even though it isn't always possible depending on subject matter. Also pointing back to reason 2 - the people that have more time to spend can choose not to run the code and answer/guess from knowledge ...others can run the provided code, see results quickly, answer the question, and still learn stuff. win! win! win!

  • sestell1

    SSChampion

    Points: 10230

    Dain Bramage (12/1/2015)


    THANKS for an EXCELLENT QOD. This is one of the best I have seen on this site for 3 reasons.

    1. The question was asked in such a way that most people will give the correct answers which is encouraging.

    2. I quickly learned something I did not know. All too often the QOD becomes a research project. Most of us working people do not have that kind of time for the QOD.

    3. Giving us code to run can be fun too. It's always cool to see it in action even though it isn't always possible depending on subject matter. Also pointing back to reason 2 - the people that have more time to spend can choose not to run the code and answer/guess from knowledge ...others can run the provided code, see results quickly, answer the question, and still learn stuff. win! win! win!

    Thanks Drain Bramage, I'm glad you liked it. I've learned a great deal from attempting to answer the QotD each day, so I'm glad I was able to contribute in a positive way. Oddly, I didn't think it was a very good question because it is convoluted to read and requires three answers to get it right. :laugh:

  • sestell1

    SSChampion

    Points: 10230

    Andy Warren (12/1/2015)


    Shane, that question made my eyes hurt!

    Hugo, that answer is why I love reading the QOTD, the follow on discussion is always worth reading. But I only see a closing rant tag - rookie mistake?:-)

    Hey Andy! Feast your eyes on THIS! Muh-hahahahahaaaaaa!! :laugh:

    CREATE SCHEMA [FROM];

    CREATE TABLE [FROM].[FROM]

    (

    [SELECT] VARCHAR(10),

    [WHERE] VarChar(10),

    [AND] VarChar(10),

    );

    INSERT INTO [FROM].[FROM] ([SELECT],[WHERE], [AND]) VALUES ('SELECT','WHERE', 'AND');

    SELECT [SELECT] [SELECT] FROM [FROM].[FROM] [WHERE] WHERE [WHERE].[WHERE] = 'WHERE' AND [AND] = 'AND'

  • Andy Warren

    SSC Guru

    Points: 119675

    That's close to evil!

  • SQL_Hunt

    SSC-Dedicated

    Points: 33261

    Very Nice Question!

    Thanks.

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

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