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.
  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 Diagnostics setting that when set to ON, constant folding will occur during typing phase.
  SET EXECUTE_SELECT_EXPRESSION_IN_MEMORY If ON, queries with no FROM clause will be executed on the current database. When OFF 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.