Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Keywords


Keywords

Author
Message
M&M
M&M
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2785 Visits: 3900
Comments posted to this topic are about the item Keywords

M&M
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18453 Visits: 13247
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?)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
sharath.chalamgari
sharath.chalamgari
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1304 Visits: 798
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.
M&M
M&M
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2785 Visits: 3900
Yes, I agree with Sharath. But many of them got it right though :-)

M&M
Hardy21
Hardy21
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1284 Visits: 1399
Nice easy one.. Thanks

Thanks
michael.kaufmann
michael.kaufmann
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1297 Visits: 1082
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
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18453 Visits: 13247
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
michael.kaufmann
michael.kaufmann
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1297 Visits: 1082
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
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8732 Visits: 11716
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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8732 Visits: 11716
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


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search