ANSI_NULLS and QUOTED_IDENTIFIERS mismatch
so how does SQL determine whether to define on or off for the ansi nulls by default?


BOL quotes - For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed.

You can use the sys.sql_modules to check  the stored procedure or function uses uses_ansi_nulls and uses_quoted_identifier settings 1 (ON) or 0 (OFF)

SELECT OBJECT_NAME([object_id]) as ObjectName, 
       uses_ansi_nulls, 
       uses_quoted_identifier
FROM sys.sql_modules
--WHERE OBJECT_NAME([object_id]) = 'any_sp/function_name_your_Want_to_Check'
ORDER BY object_id


Both ANSI_NULLS and QUOTED_IDENTIFIERS should be ON so that you dont see any unexpected surprises. Also, in newer versions of SQL Server, this two settings are defaulted to ON.