SET Statements (Part 2)

Category	Statements	Descirption	Syntax	Ex	Permission	IMP To Remember
ISO Settings statements	SET ANSI_DEFAULTS	Controls a group of SQL Server settings that collectively specify some ISO standard behavior.	SET ANSI_DEFAULTS { ON | OFF }	SET ANSI_DEFAULTS ON	Public	ANSI_DEFAULTS is a server-side setting which can enable the behavior for all client connections. When enabled (ON), this option enables the following ISO settings:SET ANSI_NULLS SET CURSOR_CLOSE_ON_COMMIT SET ANSI_NULL_DFLT_ON SET IMPLICIT_TRANSACTIONS SET ANSI_PADDING SET QUOTED_IDENTIFIER SET ANSI_WARNINGS
	SET ANSI_NULL_DFLT_OFF	Alters the behavior of the session to override default nullability of new columns when the ANSI null default option for the database is true.	SET ANSI_NULL_DFLT_OFF { ON | OFF }	SET ANSI_NULL_DFLT_OFF OFF	Public
	SET ANSI_NULL_DFLT_ON	Modifies the behavior of the session to override default nullability of new columns when the ANSI null default option for the database is false.	SET ANSI_NULL_DFLT_ON {ON | OFF}	SET ANSI_NULL_DFLT_ON ON	Public
	SET ANSI_NULLS	Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values in SQL Server 2019	SET ANSI_NULLS { ON | OFF }	SET ANSI_NULLS ON	Public
	SET ANSI_PADDING	Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.	SET ANSI_PADDING { ON | OFF }	SET ANSI_PADDING ON	Public	The default for SET ANSI_PADDING is ON. When ANSI_DEFAULTS is ON, ANSI_PADDING is enabled. ANSI_PADDING must be ON when you are creating or changing indexes on computed columns or indexed views.
	SET ANSI_WARNINGS	Specifies ISO standard behavior for several error conditions.	SET ANSI_WARNINGS { ON | OFF }	SET ANSI_WARNINGS ON	Public	When set to ON, if null values appear in aggregate functions, such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT, a warning message is generated. When set to OFF, no warning is issued. When set to ON, the divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. ANSI_WARNINGS is not honored when passing parameters in a stored procedure, user-defined function, or when declaring and setting variables in a batch statement. For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds. When ANSI_DEFAULTS is ON, ANSI_WARNINGS is enabled.
Statistics statements	SET FORCEPLAN	When FORCEPLAN is set to ON, the SQL Server query optimizer processes a join in the same order as the tables appear in the FROM clause of a query. In addition, setting FORCEPLAN to ON forces the use of a nested loop join unless other types of joins are required to construct a plan for the query, or they are requested with join hints or query hints.	SET FORCEPLAN { ON | OFF }	SET FORCEPLAN ON	SET FORCEPLAN permissions default to all users.
	SET SHOWPLAN_ALL	Causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements.	SET SHOWPLAN_ALL { ON | OFF }	SET SHOWPLAN_ALL ON	SHOWPLAN permission for all databases containing referenced objects
	SET SHOWPLAN_TEXT	Causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed.	SET SHOWPLAN_TEXT { ON | OFF }	SET SHOWPLAN_TEXT ON	SHOWPLAN permission for all databases containing referenced objects
	SET SHOWPLAN_XML	Causes SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document.	SET SHOWPLAN_XML { ON | OFF }	SET SHOWPLAN_XML ON	SHOWPLAN permission for all databases containing referenced objects
	SET STATISTICS IO	Causes SQL Server to display information about the amount of disk activity generated by Transact-SQL statements.	SET STATISTICS IO { ON | OFF }	SET STATISTICS IO ON	To use SET STATISTICS IO, users must have the appropriate permissions to execute the Transact-SQL statement.	When STATISTICS IO is ON, statistical information is displayed, and when OFF, the information isn't displayed.
	SET STATISTICS XML	Causes Microsoft SQL Server to execute Transact-SQL statements and generate detailed information about how the statements were executed in the form of a well-defined XML document.	SET STATISTICS XML { ON | OFF }	SET STATISTICS XML ON	Appropriate permissions to execute the Transact-SQL statements. SHOWPLAN permission on all databases containing objects that are referenced by the Transact-SQL statements.
	SET STATISTICS PROFILE	Displays the profile information for a statement. STATISTICS PROFILE works for ad hoc queries, views, and stored procedures.	SET STATISTICS PROFILE { ON | OFF }	SET STATISTICS PROFILE ON	Appropriate permissions to execute the Transact-SQL statements. SHOWPLAN permission on all databases containing objects that are referenced by the Transact-SQL statements.	When STATISTICS PROFILE is ON, each executed query returns its regular result set, followed by an additional result set that shows a profile of the query execution.
	SET STATISTICS TIME	Displays the number of milliseconds required to parse, compile, and execute each statement.	SET STATISTICS TIME { ON | OFF }	SET STATISTICS TIME ON	Appropriate permissions to execute the Transact-SQL statements.	When SET STATISTICS TIME is ON, the time statistics for a statement are displayed. When OFF, the time statistics are not displayed.
Transactions statements	SET IMPLICIT_TRANSACTIONS	Sets the BEGIN TRANSACTION mode to implicit, for the connection.	SET IMPLICIT_TRANSACTIONS { ON | OFF }	SET IMPLICIT_TRANSACTIONS ON
	SET TRANSACTION ISOLATION LEVEL	Controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server.	SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }	SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }
	SET XACT_ABORT	Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.	SET XACT_ABORT { ON | OFF }	SET XACT_ABORT ON		The THROW statement honors SET XACT_ABORT. RAISERROR does not. New applications should use THROW instead of RAISERROR.
Miscellaneous statements	SET CONCAT_NULL_YIELDS_NULL	Controls whether concatenation results are treated as null or empty string values.	SET CONCAT_NULL_YIELDS_NULL { ON | OFF }	SET CONCAT_NULL_YIELDS_NULL ON		In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
	SET IDENTITY_INSERT	Allows explicit values to be inserted into the identity column of a table.	SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table_name { ON | OFF }	SET IDENTITY_INSERT dbo.Tool ON;	ALTER permission on the table.
	SET LANGUAGE	Specifies the language environment for the session. The session language determines the datetime formats and system messages.	SET LANGUAGE { [ N ] 'language' | @language_var }	SET LANGUAGE Italian;		Use syslanguages DMV or sp_helplanguage to check available languages.
	SET QUOTED_IDENTIFIER	Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers.	SET QUOTED_IDENTIFIER { ON | OFF }	SET QUOTED_IDENTIFIER ON	public