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. Returns result set metadata only; no source scan.
	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.
Querona-specific	SET CONSTANT_FOLDING	Folds constant expressions during query typing.
	SET EXECUTE_SELECT_EXPRESSION_IN_MEMORY	Runs no-FROM SELECTs on the in-memory engine.
	SET EXTERNAL_FEDERATION	Runs the default (external) federation push-down step.
	SET EXTRACT_VARIABLE	Extracts in-memory expressions into session variables.
	SET FORCE_SPARK_ITERATIVE_COLLECTION	Streams Apache Spark results instead of collecting them.
	SET IN_MEMORY_FEDERATION	Uses the in-memory engine to federate across sources.
	SET NO_BROWSETABLE	Treats every SELECT as if FOR BROWSE were appended.
	SET PREDICATE_PUSH_DOWN	Pushes filter predicates down into the source queries.
	SET SINGLE_SOURCE_FEDERATION	Pushes a federated query to the source holding most data.