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 |
Always uses Sunday as the first day of the week. |
|
Sets the order of the month, day, and year date parts for interpreting dates. |
||
Locking statements |
Supported only by parser and does not change the default settings. |
|
Supported only by parser and does not change the default settings. |
||
Miscellaneous statements |
Supported only by parser and does not change the default settings. |
|
Supported only by parser and does not change the default settings. |
||
SET FIPS_FLAGGER |
Not supported. |
|
SET IDENTITY_INSERT |
Not supported. |
|
Only us_english is supported. |
||
SET OFFSETS |
Not supported. |
|
Supported only by parser. Always ‘ON’. |
||
Query Execution Statements |
Supported only by parser. Always ‘ON’. |
|
Supported only by parser. Always ‘ON’. |
||
Supported only by parser. Always ‘ON’. |
||
Supported only by parser. Always ‘ON’. |
||
Supported only by parser. Always ‘OFF’. |
||
Supported only by parser. Always ‘ON’. |
||
Supported only by parser. Always ‘OFF’. |
||
Supported only by parser. Value ignored. |
||
Supported only by parser. Value ignored. |
||
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. |
|
Supported only by parser. Value ignored. |
||
Supported only by parser. Value ignored. |
||
Statistics statements |
SET FORCEPLAN |
Not supported. |
Supported only by parser. Always ‘OFF’. |
||
Supported only by parser. Always ‘OFF’. |
||
Supported only by parser. Always ‘OFF’. |
||
Supported only by parser. Always ‘OFF’. |
||
Supported only by parser. Always ‘OFF’. |
||
Supported only by parser. Always ‘OFF’. |
||
Supported only by parser. Always ‘OFF’. |
||
Transactions statements |
Supported only by parser. Always ‘OFF’. |
|
SET REMOTE_PROC_TRANSACTIONS |
Not supported. |
|
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. |