December 3, 2012 at 8:11 pm
Comments posted to this topic are about the item SET Options - 2
December 3, 2012 at 10:15 pm
December 3, 2012 at 11:25 pm
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..
December 4, 2012 at 12:06 am
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.
December 4, 2012 at 12:55 am
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..
December 4, 2012 at 1:00 am
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..
December 4, 2012 at 1:57 am
Got it wrong, because I thought it is a trick question since "GO;" will throw an error and the table will not be created.
December 4, 2012 at 2:02 am
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.
December 4, 2012 at 2:07 am
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:
December 4, 2012 at 2:24 am
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
December 4, 2012 at 2:50 am
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
December 4, 2012 at 3:20 am
Nice question on what quoted identifiers can let you get away with, shame about the extra ';' which means the table isn't created. 🙂
December 4, 2012 at 3:43 am
Thanks man. Reall Great
--
Dineshbabu
Desire to learn new things..
December 4, 2012 at 4:22 am
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!
December 4, 2012 at 4:39 am
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 40 total)
You must be logged in to reply to this topic. Login to reply