Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
SET Statements (Transact-SQL)
Collapse the table of content
Expand the table of content

SET Statements (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

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

System_CAPS_noteNote

This feature will be removed in a future version of Microsoft 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 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  

  • All SET statements are implemented at execute or run time, except for SET FIPS_FLAGGER, SET OFFSETS, SET PARSEONLY, and SET QUOTED_IDENTIFIER. These statements are implemented at parse time.

  • If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger. Also, if a SET statement is specified in a dynamic SQL string that is run by using either sp_executesql or EXECUTE, the value of the SET option is restored after control is returned from the batch 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.

  • For any one of the SET statements with ON and OFF settings, you can specify either an ON or OFF setting for multiple SET options.

    System_CAPS_noteNote

    This does not 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 equivalent 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 are automatically set ON when a user connects to a database based on the values put 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 do not honor the SET LOCK_TIMEOUT setting.

  • When a global or shortcut SET statement, such as SET ANSI_DEFAULTS, sets several settings, issuing the shortcut SET statement resets the previous settings for all those options affected by the shortcut SET statement. If an individual SET option that is affected by a shortcut SET statement is explicitly set after the shortcut SET statement is issued, the individual SET statement overrides the corresponding shortcut settings.

  • When batches are used, the database context is determined by the batch established by using the USE statement. Ad hoc queries and all other statements that are executed 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 are set, and do not 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 is executed, either from a batch or from another stored procedure, it is executed under the option values that are currently set in the database that contains the stored procedure. For example, when stored procedure db1.dbo.sp1 calls stored procedure db2.dbo.sp2, stored procedure sp1 is executed under the current compatibility level setting of database db1, and stored procedure sp2 is executed under the current compatibility level setting of database db2.

  • When a Transact-SQL statement refers to objects that reside 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 are creating and manipulating indexes on computed columns or indexed views, the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option NUMERIC_ROUNDABORT must be set to OFF.

    If any one of these options is not set 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 do not exist.

Community Additions

ADD
Show:
© 2015 Microsoft