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

LEFT OUTER JOIN vs RIGHT OUTER JOIN

What are the differences between LEFT OUTER JOIN and RIGHT OUTER JOIN

[LEFT OUTER JOIN vs RIGHT OUTER JOIN] OR [LEFT JOIN vs RIGHT JOIN]?

	LEFT JOIN	RIGHT JOIN
Use	Select all records from the LEFT table and matched records from RIGHT table. NOTE: LEFT JOIN and LEFT OUTER JOIN are the same; both are producing the same plan, performance and result.	Select all records from the RIGHT table and matched records from LEFT table. NOTE: RIGHT JOIN and RIGHT OUTER JOIN are the same; both are producing the same plan, performance and result.
An Example for LEFT JOIN and RIGHT JOIN
Table: Department SELECT * FROM Department WITH (NOLOCK) DepartmentID DepartmentName IsActive 1 Computer Science 1 2 Maths 1 3 Chemistry 1 4 Physics 1 Table: Employee SELECT * FROM Employee  WITH (NOLOCK) EmployeeID DepartmentID EmployeeName Gender BirthDate Salary Detail 1 1 Krishnan M 4/4/1974 30000 Krishnan Details 2 2 Raman M 12/31/1974 29000 Raman Details 3 3 Durga F 10/2/1980 25000 Durga Details 4 3 Saratha F 9/2/1978 26000 Saratha Details 5 2 Shivan M 11/6/1970 45000 Shivan Details 6 3 Parvathi F 9/18/1975 35000 Parvathi Details 7 0 Easwari F 10/12/1990 10000 Parvathi Details 8 0 Donald M 11/11/1990 12000 Donald Details By looking the above sample table data, we can say The EmployeeID 7 and 8 do not have the DepartmentID (not associated with department). The DepartmentID 4 does not have Employees. _______________________________________________ LEFT JOIN: Select all records from the LEFT table and matched records from RIGHT table. -- EXAMPLE QUERY FOR LEFT JOIN SELECT e.EmployeeID,        e.EmployeeName,        d.DepartmentID,        d.DepartmentName,         CASE WHEN e.Gender = 'F' THEN 'Female'             WHEN e.Gender = 'M' THEN 'Male' ELSE NULL        END AS Gender,        e.Salary      FROM Employee e WITH (NOLOCK) LEFT JOIN Department d WITH (NOLOCK) ON d.DepartmentID = e.DepartmentID RESULT: Returns all the Employee(LEFT) table records, including not matched with department(RIGHT) table. EmployeeID EmployeeName DepartmentID DepartmentName Gender Salary 1 Krishnan 1 Computer Science Male 30000 2 Raman 2 Maths Male 29000 3 Durga 3 Chemistry Female 25000 4 Saratha 3 Chemistry Female 26000 5 Shivan 2 Maths Male 45000 6 Parvathi 3 Chemistry Female 35000 7 Easwari NULL NULL Female 10000 8 Donald NULL NULL Male 12000 _______________________________________________ RIGHT JOIN: Select all records from the RIGHT table and matched records from LEFT table. -- EXAMPLE QUERY FOR RIGHT JOIN SELECT e.EmployeeID,        e.EmployeeName,        d.DepartmentID,        d.DepartmentName,           CASE WHEN e.Gender = 'F' THEN 'Female'             WHEN e.Gender = 'M' THEN 'Male' ELSE NULL        END AS Gender,        e.Salary      FROM Employee e WITH (NOLOCK) RIGHT JOIN Department d WITH (NOLOCK) ON d.DepartmentID = e.DepartmentID RESULT: Returns all the Department(RIGHT) table records, including not matched with Employee(LEFT) table. EmployeeID EmployeeName DepartmentID DepartmentName Gender Salary 1 Krishnan 1 Computer Science Male 30000 2 Raman 2 Maths Male 29000 5 Shivan 2 Maths Male 45000 3 Durga 3 Chemistry Female 25000 4 Saratha 3 Chemistry Female 26000 6 Parvathi 3 Chemistry Female 35000 NULL NULL 4 Physics NULL NULL

[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