What is the precedence of the SET commands, database options, session options etc. in SQL Server?

Except for SET FIPS_FLAGGER, SET OFFSETS, SET PARSEONLY, and SETQUOTED_IDENTIFIER, all other SET statements are set at execute or run time. SETFIPS_FLAGGER, SET OFFSETS, SET PARSEONLY, and SET QUOTED_IDENTIFIER are set atparse time.

If a SET statement is set in a stored procedure, the value of the SEToption is restored after control is returned from the stored procedure.Therefore, a SET statement specified in dynamic SQL does not affect thestatements that follow the dynamic SQL statement.

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

The user options setting of sp_configure allows server-wide settings andworks across multiple databases. This setting also behaves like an explicit SETstatement, except that it occurs at login time.

Database settings (set by using sp_dboption) are valid only at the databaselevel and only take effect if not explicitly set. Database settings overrideserver option settings (set using a SET statement) when an option appears atmore than one level.

With any of the SET statements with ON and OFF settings, it is possible tospecify either an ON or OFF setting for multiple SET options. For example, SETQUOTED_IDENTIFIER, ANSI_NULLS ON sets both QUOTED_IDENTIFIER and ANSI_NULLS toON.

SET statement settings override database option settings (set by usingsp_dboption). In addition, some connection settings are set ON automaticallywhen a user connects to a database based on the values put into effect by theprior use of the sp_configure user options setting or the values that apply toall 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 priorsettings for all those options affected by the shortcut SET statement. If anindividual SET option (affected by a shortcut SET statement) is explicitly setafter the shortcut SET statement is issued, the individual SET statementoverrides the corresponding shortcut settings.

When batches are used, the database context is determined by the batchestablished with the USE statement. Ad hoc queries and all other statementsthat are executed outside of the stored procedure and that are in batchesinherit the option settings of the database and connection established with theUSE statement.

When a stored procedure is executed either from a batch or from anotherstored procedure, it is executed under the option values that are currently setin the database that contains the stored procedure. For example, when storedprocedure db1.dbo.sp1 calls stored procedure db2.dbo.sp2, stored procedure sp1is executed under the current compatibility level setting of database db1, andstored procedure sp2 is executed under the current compatibility level settingof database db2.

When a Transact-SQL statement refers to objects that reside in multipledatabases, the current database context and the current connection context (thedatabase defined by the USE statement if it is in a batch, or the database thatcontains the stored procedure if it is in a stored procedure) applies to thatstatement.

With the exception of the SET QUOTED_IDENTIFIER and the SET ANSI_NULLSoptions, all SET options apply immediately after being set. Therefore, mostTransact-SQL SET statements are interpreted during execution.