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 Tuesday, February 15, 2011 4:28 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
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.


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 .

Regards,
Michael
Post #1064151
Posted Tuesday, February 15, 2011 5:31 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
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
Post #1064182
Posted Tuesday, February 15, 2011 8:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
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.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1064311
Posted Tuesday, February 15, 2011 11:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Thanks for the question!
Post #1064469
Posted Wednesday, February 16, 2011 1:23 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:07 AM
Points: 3,352, Visits: 1,485
Thanks for the question, and to all who contributed to the ensuing discussion - very informative.
Post #1064759
Posted Wednesday, February 16, 2011 1:18 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:24 PM
Points: 17,807, Visits: 15,728
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1065261
Posted Monday, February 28, 2011 1:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, October 11, 2014 4:03 AM
Points: 1,005, Visits: 988
Nice question.

Thanks,
Iulian
Post #1070365
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse