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.