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


Keywords


Keywords

Author
Message
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: 1331 Visits: 1082
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. Whistling


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 :-P.

Regards,
Michael
Hardy21
Hardy21
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: 1584 Visits: 1399
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
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7845 Visits: 25280
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
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2694 Visits: 2204
Thanks for the question!
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3582 Visits: 1552
Thanks for the question, and to all who contributed to the ensuing discussion - very informative.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32503 Visits: 18556
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

Iulian -207023
Iulian -207023
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1495 Visits: 1245
Nice question.

Thanks,
Iulian
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