February 14, 2011 at 11:34 pm
Easy question.
But what's the deal with QUOTED_IDENTIFIER, because the statements works whatever it's setting is.
(was it a distraction from the real question?)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 15, 2011 at 12:35 am
Koen Verbeeck (2/14/2011)
Easy question.But what's the deal with QUOTED_IDENTIFIER, because the statements works whatever it's setting is.
(was it a distraction from the real question?)
True,it's a distraction
mohammed want to say that irrespect of the QUOTED_IDENTIFIER The square brackets are always leagal.
February 15, 2011 at 1:30 am
Yes, I agree with Sharath. But many of them got it right though 🙂
M&M
February 15, 2011 at 2:41 am
Nice easy one.. Thanks
Thanks
February 15, 2011 at 3:37 am
Thanks for the question--as already stated nice and easy :-).
One word on the distraction, though.
Apparently BOL is not quite consistent in this matter.
While the explanation on QUOTED_IDENTIFIER (http://msdn.microsoft.com/en-us/library/ms174393.aspx) states:
SET QUOTED_IDENTIFIER must be ON when reserved keywords are used for object names in the database.
the page on IDENTIFIERS (http://msdn.microsoft.com/en-us/library/ms175874.aspx) does not make a mention of this option at all and simply states:
Identifiers that do not comply with all the rules for identifiers must be delimited in a Transact-SQL statement.
Is this a simplification due to the fact that by default quoted identifiers are ON?
Anyway, I guess the best thing to do is to not use reserved keywords as identifiers for any database object. But then, who would actually use those as table or column names, etc.?
Regards,
Michael
February 15, 2011 at 3:44 am
michael.kaufmann (2/15/2011)
Anyway, I guess the best thing to do is to not use reserved keywords as identifiers for any database object. But then, who would actually use those as table or column names, etc.?
There are a lot of identifiers and some of them could make actually logic table/column names (I don't say they make good table or column names), such as:
key, source, date et cetera.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 15, 2011 at 4:01 am
Koen Verbeeck (2/15/2011)
There are a lot of identifiers and some of them could make actually logic table/column names (I don't say they make good table or column names), such as:
key, source, date et cetera.
True--that's why I attempt (I'm not saying there's always success to the attempt) to avoid these and use tblkey, source_data, date_start, date_end, date_submit, etc. (as often as possible).
One reason may well be that a German keyboard doesn't make it that easy to use brackets [], as you have to press CTRL + ALT + 8 or 9 (or AltGr + 8 or 9, if available).
Regards,
Michael
February 15, 2011 at 4:04 am
I don't know if the explanations in BOL are correct or not, but here is the deal.
The ANSI standard says that double quotes should be used to delimit identifiers, and single quotes for string constants. Brackets are not defined as special character. So the first query below would be invalid in ANSI, but is valid in SQL Server; the second query is the ANSI equivalent.
-- First query
SELECT 'Constant string' AS [Column name];
-- Second query
SELECT 'Constant string' AS "Column name";
SQL Server uses the brackets as delimtiers, probably in an effort to create some sort of compatibility with Access. Or maybe because of legacy reasons - I can't check, but I would not be surprised if very early versions of SQL Server accept double quotes as string delimiters. So these very old SQL Server versions would create the same output as the queries above by running:
-- Third query
SELECT "Constant string" AS [Column name];
I think the QUOTED_IDENTIFIER setting is to preserve backwards compatibility. With QUOTED_IDENTIFIER set to OFF, SQL Server reverts to its legacy behaviour where both single and double quotes delimit strings, and only brackets delimit idenitifeers. With QUOTED_IDENTIFIER set to ON, we get the ANSI standard behaviour where single quotes delimit strings and double quotes delimit identifiers, with the support for brackets as identifier delimiters kept in as a bonus. So you could get the third quuery to run without error message if you first SET QUOTED_IDENTIFIER OFF.
February 15, 2011 at 4:15 am
michael.kaufmann (2/15/2011)
One reason may well be that a German keyboard doesn't make it that easy to use brackets [], as you have to press CTRL + ALT + 8 or 9 (or AltGr + 8 or 9, if available).
The obvious solution would be to start using ANSI-compliant double quotes to delimit identifiers that need to be delimited. :Whistling:
February 15, 2011 at 4:28 am
Hugo Kornelis (2/15/2011)
The obvious solution would be to start using ANSI-compliant double quotes to delimit identifiers that need to be delimited. :Whistling:
Hugo,
first of all thanks for your explanation in your previous post and the sample code clearly describing the different outcome based on the quoted identifier setting.
And I wasn't aware of the ANSI compliant variant using double quotes, as the examples in BOL as well as any other code I've seen so far exclusively made use of brackets.
So thank you very much for making this point, and I'll definitely start using double quotes :-P.
Regards,
Michael
February 15, 2011 at 5:31 am
Hugo Kornelis (2/15/2011)
I think the QUOTED_IDENTIFIER setting is to preserve backwards compatibility. With QUOTED_IDENTIFIER set to OFF, SQL Server reverts to its legacy behavior where both single and double quotes delimit strings, and only brackets delimit idenitifeers. With QUOTED_IDENTIFIER set to ON, we get the ANSI standard behavior where single quotes delimit strings and double quotes delimit identifiers, with the support for brackets as identifier delimiters kept in as a bonus. So you could get the third query to run without error message if you first SET QUOTED_IDENTIFIER OFF.
As per me, you are correct. We had few stored procedures which were developed during SQL 2000 and due to critical procedures, they were available with latest schema though we upgraded SQL version to SQL 2008. We were created the procedures with SET QUOTED_IDENTIFIER OFF.
Just before few days, I had converted the procedures & now they are compatible with SET QUOTED_IDENTIFIER ON.
I also heard that Microsoft should stop the usage of SET QUOTED_IDENTIFIER OFF while creating any objects.
Thanks
February 15, 2011 at 8:02 am
also heard that Microsoft should stop the usage of SET QUOTED_IDENTIFIER OFF while creating any objects.
Regards,
Hardik Doshi
Out of curiosity in 2008 I checked for depreciated items with the following:
SELECT * FROM sys.dm_os_performance_counters WHERE object_name LIKE ('%deprecated%')
AND instance_name LIKE('%SET%')
Although eleven SET statements were displayed, SET QUOTED_IDENTIFIER was not one of those.
February 15, 2011 at 11:30 am
Thanks for the question!
February 16, 2011 at 1:23 am
Thanks for the question, and to all who contributed to the ensuing discussion - very informative.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply