OK, from an application developer’s point of view, here’s what these settings do:
QUOTED_IDENTIFIER
This setting controls how quotation marks ".." are interpreted by the SQL compiler. When QUOTED_IDENTIFIER is ON then quotes are treated like brackets ([...]) and can be used to quote SQL object names like table names, column names, etc. When it is OFF (not recommended), then quotes are treated like apostrophes ('..') and can be used to quote text strings in SQL commands.
ANSI_NULLS
This setting controls what happens when you try to use any comparison operator other than IS on NULL. When it is ON, these comparisons follow the standard which says that comparing to NULL always fails (because it isn’t a value, it’s a Flag) and returns FALSE. When this setting is OFF (really not recommended) you can sucessfully treat it like a value and use =, <>, etc. on it and get back TRUE as appropiate.
The proper way to handle this is to instead use the IS (ColumnValue IS NULL ..).
CONCAT_NULL_YIELDS_NULL
This setting controls whether NULLs “Propogate” whn used in string expressions. When this setting is ON, it follows the standard and an expression like 'some string' + NULL .. always returns NULL. Thus, in a series of string concatenations, one NULL can cause the whole expression to return NULL. Turning this OFF (also, not recommended) will cause the NULLs to be treated like empty strings instead, so 'some string' + NULL just evaluates to 'some string'.
The proper way to handle this is with the COALESCE (or ISNULL) function: 'some string' + COALESCE(NULL, '') ...