SET Statements (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

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.

For information about setting local variables with the SET statement, see SET @local_variable (Transact-SQL).

Category	Statements
Date and time statements	SET DATEFIRST SET DATEFORMAT
Locking statements	SET DEADLOCK_PRIORITY SET LOCK_TIMEOUT
Miscellaneous statements	SET CONCAT_NULL_YIELDS_NULL SET CURSOR_CLOSE_ON_COMMIT SET FIPS_FLAGGER SET IDENTITY_INSERT SET LANGUAGE SET OFFSETS SET QUOTED_IDENTIFIER
Query Execution Statements	SET ARITHABORT SET ARITHIGNORE SET FMTONLY Note: This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. SET NOCOUNT SET NOEXEC SET NUMERIC_ROUNDABORT SET PARSEONLY SET QUERY_GOVERNOR_COST_LIMIT SET RESULT SET CACHING (Preview) Note: This feature applies to Azure Synapse Analytics only. SET ROWCOUNT SET TEXTSIZE
ISO Settings statements	SET ANSI_DEFAULTS SET ANSI_NULL_DFLT_OFF SET ANSI_NULL_DFLT_ON SET ANSI_NULLS SET ANSI_PADDING SET ANSI_WARNINGS
Statistics statements	SET FORCEPLAN SET SHOWPLAN_ALL SET SHOWPLAN_TEXT SET SHOWPLAN_XML SET STATISTICS IO SET STATISTICS XML SET STATISTICS PROFILE SET STATISTICS TIME
Transactions statements	SET IMPLICIT_TRANSACTIONS SET REMOTE_PROC_TRANSACTIONS SET TRANSACTION ISOLATION LEVEL SET XACT_ABORT

Considerations When You Use the SET Statements

• All SET statements run at execute or run time, except these statements, which run at parse time:

  • SET FIPS_FLAGGER
  • SET OFFSETS
  • SET PARSEONLY
  • and SET QUOTED_IDENTIFIER

• If a SET statement runs in a stored procedure or trigger, the value of the SET option gets restored after the stored procedure or trigger returns control. Also, if you specify a SET statement in a dynamic SQL string that runs by using either sp_executesql or EXECUTE, the value of the SET option gets restored after control returns from the batch that you specified in the dynamic SQL string.

• Stored procedures execute with the SET settings specified at execute time except for SET ANSI_NULLS and SET QUOTED_IDENTIFIER. Stored procedures specifying SET ANSI_NULLS or SET QUOTED_IDENTIFIER use the setting specified at stored procedure creation time. If used inside a stored procedure, any SET setting is ignored.

• The user options setting of sp_configure allows for server-wide settings and works across multiple databases. This setting also behaves like an explicit SET statement, except that it occurs at login time.

• Database settings set by using ALTER DATABASE are valid only at the database level and take effect only if explicitly set. Database settings override instance option settings that are set by using sp_configure.

• If a SET statement uses ON and OFF, you can specify either one for multiple SET options.

  Note

  This doesn't apply to the statistics related SET options.

  For example, SET QUOTED_IDENTIFIER, ANSI_NULLS ON sets both QUOTED_IDENTIFIER and ANSI_NULLS to ON.

• SET statement settings override identical database option settings that are set by using ALTER DATABASE. For example, the value specified in a SET ANSI_NULLS statement will override the database setting for ANSI_NULLs. Additionally, some connection settings get automatically set ON when a user connects to a database based on the values that go into effect by the previous use of the sp_configure user options setting, or the values that apply to all ODBC and OLE/DB connections.

• ALTER, CREATE and DROP DATABASE statements don't honor the SET LOCK_TIMEOUT setting.

• When a global or shortcut SET statement sets several settings, issuing the shortcut SET statement resets the previous settings for all those options that the shortcut SET statement affects. If a SET option that gets affected by a shortcut SET statement gets set after the shortcut SET statement gets issued, the individual SET statement overrides the comparable shortcut settings. An example of a shortcut SET statement would be SET ANSI_DEFAULTS.

• When batches are used, the database context is determined by the batch that is established by using the USE statement. Unplanned queries and all other statements that run outside the stored procedure and that are in batches inherit the option settings of the database and connection established by the USE statement.

• Multiple Active Result Set (MARS) requests share a global state that contains the most recent session SET option settings. When each request executes, it can modify the SET options. The changes are specific to the request context in which they're set, and don't affect other concurrent MARS requests. However, after the request execution is completed, the new SET options are copied to the global session state. New requests that execute under the same session after this change will use these new SET option settings.

• When a stored procedure runs from a batch or from another stored procedure, it's run under the option values set up in the database that has the stored procedure. For example, when stored procedure db1.dbo.sp1 calls stored procedure db2.dbo.sp2, stored procedure sp1 executes under the current compatibility level setting of database db1, and stored procedure sp2 executes under the current compatibility level setting of database db2.

• When a Transact-SQL statement concerns objects that are in multiple databases, the current database context and the current connection context applies to that statement. In this case, if Transact-SQL statement is in a batch, the current connection context is the database defined by the USE statement; if the Transact-SQL statement is in a stored procedure, the connection context is the database that contains the stored procedure.

• When you're creating and manipulating indexes on computed columns or indexed views, you must set these SET options to ON: ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS. Set the option NUMERIC_ROUNDABORT to OFF.

  If you don't set any one of these options to the required values, INSERT, UPDATE, DELETE, DBCC CHECKDB, and DBCC CHECKTABLE actions on indexed views or tables with indexes on computed columns will fail. SQL Server will raise an error listing all the options that are incorrectly set. Also, SQL Server will process SELECT statements on these tables or indexed views as if the indexes on computed columns or on the views don't exist.

• When SET RESULT_SET_CACHING is ON, it enables the result caching feature for the current client session. Result_set_caching cannot be turned ON for a session if it is turned OFF at the database level. When SET RESULT_SET_CACHING is OFF, the result set caching feature is disabled for the current client session. Changing this setting requires membership in the public role. Applies to: Azure Synapse Analytics Gen2