SET Options - 2

Question

Post reply

SET Options - 2

Ron McCullough

SSC Guru

Points: 63877
More actions
December 3, 2012 at 8:11 pm

#285765

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]

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

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

okbangas SSChampion Points: 11773 More actions · Answer 1

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]

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 2

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..

Paul White SSC Guru Points: 150467 More actions · Answer 3

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 4

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..

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 5

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..

ma-516002 Hall of Fame Points: 3359 More actions · Answer 6

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 7

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/

Paul White SSC Guru Points: 150467 More actions · Answer 8

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:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 9

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Stuart Davies SSCoach Points: 19505 More actions · Answer 10

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 11

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

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 12

Thanks man. Reall Great

--
Dineshbabu
Desire to learn new things..

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 13

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!

TomThomson SSC Guru Points: 104773 More actions · Answer 14

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