SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Keywords


Keywords

Author
Message
M&M
M&M
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

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

M&M
Koen Verbeeck
Koen Verbeeck
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34991 Visits: 13270
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1646 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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

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

M&M
Hardy21
Hardy21
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

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

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

Group: General Forum Members
Points: 1411 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
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34991 Visits: 13270
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.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1411 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
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13307 Visits: 12182
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
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13307 Visits: 12182
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