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.