Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Keywords Expand / Collapse
Author
Message
Posted Monday, February 14, 2011 10:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:12 PM
Points: 2,278, Visits: 3,798
Comments posted to this topic are about the item Keywords

Mohammed Moinudheen
Post #1063994
Posted Monday, February 14, 2011 11:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 13,261, Visits: 11,051
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
Posted Tuesday, February 15, 2011 12:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 10:39 AM
Points: 1,194, Visits: 787
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
Posted Tuesday, February 15, 2011 1:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:12 PM
Points: 2,278, Visits: 3,798
Yes, I agree with Sharath. But many of them got it right though

Mohammed Moinudheen
Post #1064068
Posted Tuesday, February 15, 2011 2:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 4:43 AM
Points: 1,130, Visits: 1,391
Nice easy one.. Thanks

Thanks
Post #1064111
Posted Tuesday, February 15, 2011 3:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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
Post #1064129
Posted Tuesday, February 15, 2011 3:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 13,261, Visits: 11,051
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
Posted Tuesday, February 15, 2011 4:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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
Posted Tuesday, February 15, 2011 4:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:05 PM
Points: 6,043, Visits: 8,323
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
Posted Tuesday, February 15, 2011 4:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:05 PM
Points: 6,043, Visits: 8,323
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1064144
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse