SET Statements

The Transact-SQL programming language provides several SET statements that change the current session handling of specific information. The SET statements are grouped into the categories shown in the following table.

Category	Statements	Compatibility and support
Date and time statements	SET DATEFIRST	Always uses Sunday as the first day of the week.
	SET DATEFORMAT	Sets the order of the month, day, and year date parts for interpreting dates.
Locking statements	SET DEADLOCK_PRIORITY	Supported only by parser and does not change the default settings.
	SET LOCK_TIMEOUT	Supported only by parser and does not change the default settings.
Miscellaneous statements	SET CONCAT_NULL_YIELDS_NULL	Supported only by parser and does not change the default settings.
	SET CURSOR_CLOSE_ON_COMMIT	Supported only by parser and does not change the default settings.
	SET FIPS_FLAGGER	Not supported.
	SET IDENTITY_INSERT	Not supported.
	SET LANGUAGE	Only us_english is supported.
	SET OFFSETS	Not supported.
	SET QUOTED_IDENTIFIER	Supported only by parser. Always ‘ON’.
Query Execution Statements	SET ARITHABORT	Supported only by parser. Always ‘ON’.
	SET ARITHIGNORE	Supported only by parser. Always ‘ON’.
	SET FMTONLY	Supported only by parser. Always ‘ON’.
	SET NOCOUNT	Supported only by parser. Always ‘ON’.
	SET NOEXEC	Supported only by parser. Always ‘OFF’.
	SET NUMERIC_ROUNDABORT	Supported only by parser. Always ‘ON’.
	SET PARSEONLY	Supported only by parser. Always ‘OFF’.
	SET QUERY_GOVERNOR_COST_LIMIT	Supported only by parser. Value ignored.
	SET ROWCOUNT	Supported only by parser. Value ignored.
	SET TEXTSIZE	Supported only by parser. Value ignored.
ISO Settings statements	SET ANSI_DEFAULTS	Not supported.
	SET ANSI_NULL_DFLT_OFF	Not supported.
	SET ANSI_NULL_DFLT_ON	Not supported.
	SET ANSI_NULLS	Supported only by parser. Value ignored.
	SET ANSI_PADDING	Supported only by parser. Value ignored.
Statistics statements	SET FORCEPLAN	Not supported.
	SET SHOWPLAN_ALL	Supported only by parser. Always ‘OFF’.
	SET SHOWPLAN_TEXT	Supported only by parser. Always ‘OFF’.
	SET SHOWPLAN_XML	Supported only by parser. Always ‘OFF’.
	SET STATISTICS IO	Supported only by parser. Always ‘OFF’.
	SET STATISTICS XML	Supported only by parser. Always ‘OFF’.
	SET STATISTICS PROFILE	Supported only by parser. Always ‘OFF’.
	SET STATISTICS TIME	Supported only by parser. Always ‘OFF’.
Transactions statements	SET IMPLICIT_TRANSACTIONS	Supported only by parser. Always ‘OFF’.
	SET REMOTE_PROC_TRANSACTIONS	Not supported.
	SET TRANSACTION ISOLATION LEVEL	Supported only by parser. Value ignored.
	SET XACT_ABORT	Not supported.
System-specific	SET CONSTANT_FOLDING	Diagnostics setting that when set to ON, constant folding will occur during typing phase.
	SET EXECUTE_SELECT_EXPRESSION_IN_MEMORY	If OFF, queries with no FROM clause will be executed on the current database. When ON the in-memory engine will be used.
	SET EXTERNAL_FEDERATION	Controls advanced preprocessing feature.
	SET EXTRACT_VARIABLE	Controls advanced preprocessing feature.
	FORCE_SPARK_ITERATIVE_COLLECTION	Controls the way of retrieving results from Apache Spark. ON - results as an iterator of rows (.toLocalIterator() OFF - results as a single job result (.collect())
	SET IN_MEMORY_FEDERATION	Controls advanced preprocessing feature.
	SET NO_BROWSETABLE	Controls advanced preprocessing feature.
	SET PREDICATE_PUSH_DOWN	When OFF disables predicate pushdown.
	SET SINGLE_SOURCE_FEDERATION	Controls advanced preprocessing feature.