SET Options - 2

  • Comments posted to this topic are about the item SET Options - 2

    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]

  • Wrong answer marked as correct. SQL Server will return the following error message:

    Msg 102, Level 15, State 1, Line 5

    Incorrect syntax near ';'.

    Semicolon is not allowed after go.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • By Default "Quoted Identifier" Will be ON. Eventhoug if we didn't set any options. the syntax will work fine.

    Please correct me if i'm wrong.

    --
    Dineshbabu
    Desire to learn new things..

  • dineshbabus (12/3/2012)


    By Default "Quoted Identifier" Will be ON. Eventhoug if we didn't set any options. the syntax will work fine. Please correct me if i'm wrong.

    The default is OFF (see http://msdn.microsoft.com/en-us/library/ms190707(v=sql.105).aspx and http://msdn.microsoft.com/en-us/library/ms191203(v=sql.105).aspx).

    However, most client applications SET QUOTED_IDENTIIFIER ON when they connect.

  • I Opened a new Sql server management studio and tried the create statement without any set options. Query works fine.

    can you xplain in detail?

    --
    Dineshbabu
    Desire to learn new things..

  • Please refer this URL. It says by default IT will be ON.

    http://msdn.microsoft.com/en-us/library/ms174393(SQL.105).aspx

    --
    Dineshbabu
    Desire to learn new things..

  • Got it wrong, because I thought it is a trick question since "GO;" will throw an error and the table will not be created.

  • I had to make a guess as to the author's intention and preparation.

    Did he actually try the code himself? In that case, it is a nasty trick question, because everything in the question distracts from the syntax error (semicolon after GO) that causes the batch to fail and the table to not be created.

    Of did he forget to execute the code? In that case, it is an unintended trick question, because I can assume that the semicolon after the GO was an oversight and the author wanted us to focus on the ability to create a table name using reserved words (not a recommended good practice, by the way).

    I picked the wrong option. If I had checked the submitter of the question, I probably would have picked the right option - I don't think I ever saw an intentional trick question from Ron.


    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/

  • dineshbabus (12/4/2012)


    I Opened a new SQL Server Management Studio and tried the create statement without any set options. Query works fine. Can you explain in detail?

    SQL Server Management Studio is a client application which (by default) issues SET QUOTED_IDENTIFIER ON when connecting. It depends on how you have your SSMS configured, of course:

  • dineshbabus (12/4/2012)


    Please refer this URL. It says by default IT will be ON.

    http://msdn.microsoft.com/en-us/library/ms174393(SQL.105).aspx

    -- Default for user options is 0

    -- See http://msdn.microsoft.com/en-us/library/ms189631.aspx

    -- Flag values are at http://msdn.microsoft.com/en-us/library/ms176031.aspx

    -- e.g. QUOTED_IDENTIFER is 256

    SELECT

    c.value_in_use

    FROM sys.configurations AS c

    WHERE

    c.name = N'user options';

    -- Show database-level defaults

    SELECT

    name, is_quoted_identifier_on

    FROM sys.databases AS d;

    -- Is QUOTED_IDENTIFIER on for the current connection?

    -- A value of 256 means yes

    -- See http://msdn.microsoft.com/en-us/library/ms177525.aspx

    SELECT @@OPTIONS & 256;

    Try connecting via sqlcmd instead of using SSMS. It does not SET QUOTED_IDENTIFIER ON unless you specify the -I switch, see http://msdn.microsoft.com/en-us/library/ms162773.aspx

  • Good question and Hugo raises some good points too.

    My mistake in not getting a point today for being too literal with my interpretation of the question

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • Nice question on what quoted identifiers can let you get away with, shame about the extra ';' which means the table isn't created. 🙂

  • Thanks man. Reall Great

    --
    Dineshbabu
    Desire to learn new things..

  • Dont' know what everyone else got but on SS 2008 I got:

    Msg 102, Level 15, State 1, Line 5

    Incorrect syntax near ';'.

    Looks like another fine QotD!

  • Nice question apart from teh semi-colon.

    Some interesting discussion about the default. BoL contradicts itself firmly here - the SET QUOTED_IDENTIFIER page has said the default is ON at least since SQL 2005, while the SET OPTIONS page says it is OFF. I believe that the fact is that it's one of those options like ANSI_WARNINGS where the default in the engine is OFF but the default applied by most connection methods is ON, but unlike ANSI_WARNINGS MS hasn't documented this in BoL (you will only finnd ANSI_WARNINGS off if you connect using DBLIBRARY, it's on by default for the native ODBC and OLE-DB drivers.

    It's rather surprising this error has managed to remain on the SET QUOTED_IDENTIFIER page in BoL for so many years.

    It's quite surprising too that more of these SET options which can be set off to get non-standard behavious have not been deprecated. There are features which just don't work when they are OFF - for example if QUOTED_IDENTIFIER is OFF tables with indexes on computed columns behave badly.

    Tom

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

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