Keywords

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mohammed Moinudheen... » Keywords

17 posts, Page 1 of 2

1 2 »»

Keywords

Rate Topic

Display Mode

Topic Options

Author

Message

mohammed moinudheen

Posted Monday, February 14, 2011 10:02 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:43 PM
Points: 2,174, Visits: 3,586

Comments posted to this topic are about the item Keywords

Mohammed Moinudheen

Post #1063994

Koen Verbeeck

Posted Monday, February 14, 2011 11:34 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:11 AM
Points: 9,378, Visits: 6,473

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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1064033

sharath.chalamgari

Posted Tuesday, February 15, 2011 12:35 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, September 17, 2012 7:30 AM
Points: 1,038, Visits: 679

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.

Post #1064050

mohammed moinudheen

Posted Tuesday, February 15, 2011 1:30 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:43 PM
Points: 2,174, Visits: 3,586

Yes, I agree with Sharath. But many of them got it right though Smile

Mohammed Moinudheen

Post #1064068

Hardy21

Posted Tuesday, February 15, 2011 2:41 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 7:11 AM
Points: 877, Visits: 1,159

Nice easy one.. Thanks

Thanks

Post #1064111

michael.kaufmann

Posted Tuesday, February 15, 2011 3:37 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 11:51 PM
Points: 1,263, Visits: 1,079

Thanks for the question--as already stated nice and easy Smile

.

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

Post #1064129

Koen Verbeeck

Posted Tuesday, February 15, 2011 3:44 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:11 AM
Points: 9,378, Visits: 6,473

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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1064131

michael.kaufmann

Posted Tuesday, February 15, 2011 4:01 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 11:51 PM
Points: 1,263, Visits: 1,079

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

Post #1064137

Hugo Kornelis

Posted Tuesday, February 15, 2011 4:04 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:03 PM
Points: 5,244, Visits: 7,061

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

Post #1064139

Hugo Kornelis

Posted Tuesday, February 15, 2011 4:15 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:03 PM
Points: 5,244, Visits: 7,061

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

Post #1064144

« Prev Topic | Next Topic »

17 posts, Page 1 of 2

1 2 »»

Permissions