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.