locked
IsQuotedIdentifierOn RRS feed

  • Question

  • I made a comparison between two schemas, in many stored procedures (about 100) the results say that they're differents, but the only difference is that in one of the squemas they have IsQuotedIdentifierOn=true and IsQuotedIdentifierOn=false.

    I need the two schemas but i want to normalize they. My question is what is the difference in the procedures, to change to true or false this property. What is the mos convenient?

    Thursday, August 26, 2010 8:37 PM

Answers

  • If you or anyone else developing stored procedures did not intentionally set that property, you most likely do not have an issue.  I would recommend that you update your procedures to have the setting = ON.  This is the ANSI standard and could be considered a best practice.  Usually the only case where this may be an issue is when someone uses double quotes instead of single quotes to identify a string in their procedure. 

    Next I would check all of the development tools such as Management Studio and Visual Studio.  Make sure they are set to turn on the quoted_identifier.  We had a case where developers did not have them all set the same.  I would recommend that you set a standard for your team so you can do compares without this issue.

    Regards,

    Steve

    • Marked as answer by eduar284 Wednesday, September 1, 2010 1:46 PM
    Wednesday, September 1, 2010 4:57 AM

All replies

  • Hi,

    Data from msdn ...

             When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

    SET QUOTED_IDENTIFIER OFF
    GO
    -- An attempt to create a table with a reserved keyword as a name
    -- should fail.
    CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL)
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- Will succeed.
    CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL)
    GO
    
    SELECT "identity","order" 
    FROM "select"
    ORDER BY "order"
    GO
    
    DROP TABLE "SELECT"
    GO
    
    SET QUOTED_IDENTIFIER OFF
    GO
    

    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Friday, August 27, 2010 4:42 AM
  • Well I understand that but it doesn't  answer my question.  What is the difference in my Stored Procedure if i have this property set in on or set in off.
    Friday, August 27, 2010 2:32 PM
  • SQL Server stores the setting when the procedure is created and this setting might affect the behavior of the stored procedure in certain cases. Like lets say

     1. If the procedure updates a table which has an indexed view defined on it then this setting must be ON otherwise update will fail

     2. If the procedure has any statements which used "" to represent an identifier, and for this procedure if you set this option to OFF then it will start giving syntax errors from that point as it treats the value in double quotes as literal insted of an identifier.

    Please read on http://ranjithk.com/2010/01/10/understanding-set-quoted_identifier-onof/


    Ranjith | My Blog
    Sunday, August 29, 2010 1:03 PM
  • If you or anyone else developing stored procedures did not intentionally set that property, you most likely do not have an issue.  I would recommend that you update your procedures to have the setting = ON.  This is the ANSI standard and could be considered a best practice.  Usually the only case where this may be an issue is when someone uses double quotes instead of single quotes to identify a string in their procedure. 

    Next I would check all of the development tools such as Management Studio and Visual Studio.  Make sure they are set to turn on the quoted_identifier.  We had a case where developers did not have them all set the same.  I would recommend that you set a standard for your team so you can do compares without this issue.

    Regards,

    Steve

    • Marked as answer by eduar284 Wednesday, September 1, 2010 1:46 PM
    Wednesday, September 1, 2010 4:57 AM