SET

The Transact-SQL programming language provides several SET statements that alter the current session handling of specific information.

The SET statements are grouped into these categories.

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 DISABLE_DEF_CNST_CHK

SET FIPS_FLAGGER

SET IDENTITY_INSERT

SET LANGUAGE

SET OFFSETS

SET QUOTED_IDENTIFIER

Query Execution Statements

SET ARITHABORT

SET ARITHIGNORE

SET FMTONLY

SET NOCOUNT

SET NOEXEC

SET NUMERIC_ROUNDABORT

SET PARSEONLY

SET QUERY_GOVERNOR_COST_LIMIT

SET ROWCOUNT

SET TEXTSIZE

SQL-92 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 STATISTICS IO

SET STATISTICS PROFILE

SET STATISTICS TIME

Transactions Statements

SET IMPLICIT_TRANSACTIONS

SET REMOTE_PROC_TRANSACTIONS

SET TRANSACTION ISOLATION LEVEL

SET XACT_ABORT

Considerations When Using the SET Statements

• Except for SET FIPS_FLAGGER, SET OFFSETS, SET PARSEONLY, and SET QUOTED_IDENTIFIER, all other SET statements are set at execute or run time. SET FIPS_FLAGGER, SET OFFSETS, SET PARSEONLY, and SET QUOTED_IDENTIFIER are set at parse time.
  
  
• If a SET statement is set in a stored procedure, the value of the SET option is restored after control is returned from the stored procedure. Therefore, a SET statement specified in dynamic SQL does not affect the statements that follow the dynamic SQL statement.
  
  
• 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 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 sp_dboption) are valid only at the database level and only take effect if explicitly set. Database settings override server option settings (set using sp_configure).
  
  
• With any of the SET statements with ON and OFF settings, it is possible to specify either an ON or OFF setting for multiple SET options. For example,

  SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
  

  sets both QUOTED_IDENTIFIER and ANSI_NULLS to ON.

• SET statement settings override database option settings (set by using sp_dboption). In addition, some connection settings are set ON automatically when a user connects to a database based on the values put into effect by the prior use of the sp_configure user options setting, or the values that apply to all ODBC and OLE/DB connections.
  
  
• When a global or shortcut SET statement (for example, SET ANSI_DEFAULTS) sets a number of settings, issuing the shortcut SET statement resets the prior settings for all those options affected by the shortcut SET statement. If an individual SET option (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 with the USE statement. Ad hoc queries and all other statements that are executed outside of the stored procedure and that are in batches inherit the option settings of the database and connection established with the USE statement.
  
  
• 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 (the database defined by the USE statement if it is in a batch, or the database that contains the stored procedure if it is in a stored procedure) applies to that statement.
  
  
• When 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 of these options are not set to the required values, INSERT, UPDATE, and DELETE 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 though the indexes on computed columns or on the views do not exist.