SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF

What are the differences between SET QUOTED_IDENTIFIER ON and SET QUOTED_IDENTIFIER OFF [SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF]?

	SET QUOTED_IDENTIFIER ON	SET QUOTED_IDENTIFIER OFF
Use	It is treated as IDENTIFIER Allows characters enclosed with double quotes[“”] for Object names	It is treated as Literal OR Value  Returns the string value enclosed with double quotes[“”] as value
	SET QUOTED_IDENTIFIER ON CREATE TABLE "Child"(RecordId int IDENTITY(100,1)) GO RESULT: Command(s) completed successfully.	SET QUOTED_IDENTIFIER OFF CREATE TABLE "Child"(RecordId int IDENTITY(100,1)) GO RESULT: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'Child'.
	CREATE TABLE Child(RecordId int IDENTITY(100,1)) GO INSERT Child DEFAULT VALUES GO SET QUOTED_IDENTIFIER ON SELECT "RecordId" FROM Child RESULT: RecordId 100	CREATE TABLE Child(RecordId int IDENTITY(100,1)) GO INSERT Child DEFAULT VALUES GO SET QUOTED_IDENTIFIER OFF SELECT "RecordId" FROM Child RESULT: RecordId
	SET QUOTED_IDENTIFIER ON SELECT "RecordId" RESULT: Msg 207, Level 16, State 1, Line 2 Invalid column name 'RecordId'.	SET QUOTED_IDENTIFIER OFF SELECT "RecordId" RESULT: RecordId
	Best practice is to use the SET QUOTED_IDENTIFIER ON setting.	Avoid using SET QUOTED_IDENTIFIER OFF setting. As more and more new features require this setting value to be ON. For example while working with Filtered Indexes SET QUOTED_IDENTIFIER setting should be ON otherwise we will get into an exception.
Objects which are created with SET QUTOED IDENTIFIER ON/OFF	SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE uses_quoted_identifier = 1	SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE uses_quoted_identifier = 0

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF

@@IDENTITY vs SCOPE_IDENTITY()

What are the differences among/ between @@IDENTITY and SCOPE_IDENTITY() and 

IDENT_CURRENT(‘<<tablename>>’) [@@IDENTITY vs SCOPE_IDENTITY()]?

@@IDENTITY	SCOPE_IDENTITY()	IDENT_CURRENT(‘table’)
Returns the last identity value generated for any table in the current session, across all scopes (i.e. current session and global scope).	Returns the last identity value generated for any table in the current session and the current scope (i.e. current session and local scope).	Returns the last identity value generated for a specific table in any session and any scope (i.e. any session and global scope).
CREATE TABLE Parent(RecordId int IDENTITY(1,1)) GO CREATE TABLE Child(RecordId int IDENTITY(100,1)) GO /* -- Trigger definition CREATE TRIGGER TR_Parent_insert ON Parent FOR INSERT AS BEGIN    INSERT Child DEFAULT VALUES END GO -- End of trigger definition */ -- Child Procedure definition CREATE PROCEDURE USP_Child_insert AS BEGIN    INSERT Child DEFAULT VALUES END GO -- End Child Procedure definition -- Parent Procedure definition CREATE PROCEDURE USP_Parent_insert AS BEGIN    INSERT Parent DEFAULT VALUES;    EXEC USP_Child_insert -- (This SP will excute in other scope)    SELECT @@IDENTITY;    SELECT SCOPE_IDENTITY();    SELECT IDENT_CURRENT('Parent');    SELECT IDENT_CURRENT('Child');    END GO -- End Parent Procedure definition -- Execute the Procedure EXEC USP_Parent_insert GO EXAMPLE: 1 RESULT: SELECT @@IDENTITY; -- Returns 100 i.e. Child.RecordId from Child table. SELECT SCOPE_IDENTITY(); -- Returns 1 i.e. Parent.RecordId from Parent table. SELECT IDENT_CURRENT('Parent'); -- Returns 1 i.e. Parent.RecordId from Parent SELECT IDENT_CURRENT('Child'); -- Returns 100 i.e. Child.RecordId from Child table. EXAMPLE: 2 Execute the following code in same session; you get the results as follows. SELECT @@IDENTITY; -- Returns 100 i.e. Child.RecordId from Child table. SELECT SCOPE_IDENTITY(); -- Returns NULL i.e. Parent.RecordId from Parent table. SELECT IDENT_CURRENT('Parent'); -- Returns 1 i.e. Parent.RecordId from Parent SELECT IDENT_CURRENT('Child'); -- Returns 100 i.e. Child.RecordId from Child table. EXAMPLE: 3 Execute the following code in another session; you get the results as follows. SELECT @@IDENTITY; -- Returns NULL i.e. Child.RecordId from Child table. SELECT SCOPE_IDENTITY(); -- Returns NULL i.e. Parent.RecordId from Parent table. SELECT IDENT_CURRENT('Parent'); -- Returns 1 i.e. Parent.RecordId from Parent SELECT IDENT_CURRENT('Child'); -- Returns 100 i.e. Child.RecordId from Child table.

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF