SET 语句 (Transact-SQL)SET Statements (Transact-SQL)

适用对象: 是SQL Server 否Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Transact-SQLTransact-SQL 编程语言提供了一些 SET 语句,这些语句可以更改特定信息的当前会话处理。The Transact-SQLTransact-SQL programming language provides several SET statements that change the current session handling of specific information. SET 语句可分为下表中列出的几个类别。The SET statements are grouped into the categories shown in the following table.

有关使用 SET 语句设置局部变量的信息,请参阅 SET @local_variable (Transact-SQL)For information about setting local variables with the SET statement, see SET @local_variable (Transact-SQL).

类别Category 语句Statements
日期和时间语句Date and time statements SET DATEFIRSTSET DATEFIRST

SET DATEFORMATSET DATEFORMAT
锁定语句Locking statements SET DEADLOCK_PRIORITYSET DEADLOCK_PRIORITY

SET LOCK_TIMEOUTSET LOCK_TIMEOUT
杂项语句Miscellaneous statements SET CONCAT_NULL_YIELDS_NULLSET CONCAT_NULL_YIELDS_NULL

SET CURSOR_CLOSE_ON_COMMITSET CURSOR_CLOSE_ON_COMMIT

SET FIPS_FLAGGERSET FIPS_FLAGGER

SET IDENTITY_INSERTSET IDENTITY_INSERT

SET LANGUAGESET LANGUAGE

SET OFFSETSSET OFFSETS

SET QUOTED_IDENTIFIERSET QUOTED_IDENTIFIER
查询执行语句Query Execution Statements SET ARITHABORTSET ARITHABORT

SET ARITHIGNORESET ARITHIGNORE

SET FMTONLYSET FMTONLY
请注意:此功能处于维护模式并且可能会在 Microsoft SQL Server 将来的版本中被删除。This feature is in maintenance mode and may 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.Note: 此功能处于维护模式并且可能会在 Microsoft SQL Server 将来的版本中被删除。This feature is in maintenance mode and may 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 NOCOUNTSET NOCOUNT

SET NOEXECSET NOEXEC

SET NUMERIC_ROUNDABORTSET NUMERIC_ROUNDABORT

SET PARSEONLYSET PARSEONLY

SET QUERY_GOVERNOR_COST_LIMITSET QUERY_GOVERNOR_COST_LIMIT

SET RESULT_SET_CACHING(预览版)SET RESULT SET CACHING (Preview)
注意:此功能仅适用于 Azure SQL 数据仓库。Note: This feature applies to Azure SQL Data Warehouse only.

SET ROWCOUNTSET ROWCOUNT

SET TEXTSIZESET TEXTSIZE
ISO 设置语句ISO Settings statements SET ANSI_DEFAULTSSET ANSI_DEFAULTS

SET ANSI_NULL_DFLT_OFFSET ANSI_NULL_DFLT_OFF

SET ANSI_NULL_DFLT_ONSET ANSI_NULL_DFLT_ON

SET ANSI_NULLSSET ANSI_NULLS

SET ANSI_PADDINGSET ANSI_PADDING

SET ANSI_WARNINGSSET ANSI_WARNINGS
统计语句Statistics statements SET FORCEPLANSET FORCEPLAN

SET SHOWPLAN_ALLSET SHOWPLAN_ALL

SET SHOWPLAN_TEXTSET SHOWPLAN_TEXT

SET SHOWPLAN_XMLSET SHOWPLAN_XML

SET STATISTICS IOSET STATISTICS IO

SET STATISTICS XMLSET STATISTICS XML

SET STATISTICS PROFILESET STATISTICS PROFILE

SET STATISTICS TIMESET STATISTICS TIME
事务语句Transactions statements SET IMPLICIT_TRANSACTIONSSET IMPLICIT_TRANSACTIONS

SET REMOTE_PROC_TRANSACTIONSSET REMOTE_PROC_TRANSACTIONS

SET TRANSACTION ISOLATION LEVELSET TRANSACTION ISOLATION LEVEL

SET XACT_ABORTSET XACT_ABORT

使用 SET 语句时的注意事项Considerations When You Use the SET Statements

  • 在执行过程中或运行时运行的所有 SET 语句,除了在分析时运行的这些语句之外:All SET statements run at execute or run time, except these statements, which run at parse time:

    • SET FIPS_FLAGGERSET FIPS_FLAGGER
    • SET OFFSETSSET OFFSETS
    • SET PARSEONLYSET PARSEONLY
    • 和 SET QUOTED_IDENTIFIERand SET QUOTED_IDENTIFIER
  • 如果是在存储过程或触发器中运行 SET 语句,则存储过程或触发器返回控制后,将恢复 SET 选项的值。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. 同样,如果是在使用 sp_executesql 或 EXECUTE 运行的动态 SQL 字符串中指定 SET 语句,则从在动态 SQL 字符串中指定的批处理返回控制后,将恢复 SET 选项的值 。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.

  • 存储过程使用执行时指定的 SET 设置执行,但 SET ANSI_NULLS 和 SET QUOTED_IDENTIFIER 除外。Stored procedures execute with the SET settings specified at execute time except for SET ANSI_NULLS and SET QUOTED_IDENTIFIER. 指定 SET ANSI_NULLS 或 SET QUOTED_IDENTIFIER 的存储过程使用创建存储过程时指定的设置。Stored procedures specifying SET ANSI_NULLS or SET QUOTED_IDENTIFIER use the setting specified at stored procedure creation time. 如果在存储过程内使用任何 SET 设置,则该设置将被忽略。If used inside a stored procedure, any SET setting is ignored.

  • sp_configure 的 user options 设置允许服务器范围的设置,并可以跨多个数据库运行 。The user options setting of sp_configure allows for server-wide settings and works across multiple databases. 此设置的行为还类似于显式 SET 语句,只是后者发生在登录时。This setting also behaves like an explicit SET statement, except that it occurs at login time.

  • 使用 ALTER DATABASE 设置的数据库设置仅在数据库级有效,并且仅在显式设置时有效。Database settings set by using ALTER DATABASE are valid only at the database level and take effect only if explicitly set. 数据库设置优先于使用 sp_configure 设置的实例选项设置 。Database settings override instance option settings that are set by using sp_configure.

  • 如果 SET 语句使用 ON 和 OFF 时,则可为一个语句指定多个 SET 选项。If a SET statement uses ON and OFF, you can specify either one for multiple SET options.

    备注

    这不适用与统计相关的 SET 选项。This doesn't apply to the statistics related SET options.

    例如,SET QUOTED_IDENTIFIER, ANSI_NULLS ON 可将 QUOTED_IDENTIFIER 和 ANSI_NULLS 设置为 ON。For example, SET QUOTED_IDENTIFIER, ANSI_NULLS ON sets both QUOTED_IDENTIFIER and ANSI_NULLS to ON.

  • SET 语句设置优先于使用 ALTER DATABASE 设置的等价数据库选项设置。SET statement settings override identical database option settings that are set by using ALTER DATABASE. 例如,SET ANSI_NULLS 语句中指定的值将覆盖 ANSI_NULL 的数据库设置。For example, the value specified in a SET ANSI_NULLS statement will override the database setting for ANSI_NULLs. 此外,如果用户在连接到数据库时依据的值是因为先前使用 sp_configure user options 设置而生效的,或者所依据的值适用于所有 ODBC 和 OLE/DB 连接,则一些连接设置将自动设置为 ON 。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 和 DROP DATABASE 语句不提供 SET LOCK_TIMEOUT 设置。ALTER, CREATE and DROP DATABASE statements don't honor the SET LOCK_TIMEOUT setting.

  • 当全局或快捷 SET 语句设置多个设置时,发出快捷 SET 语句后,将重设所有受该快捷 SET 语句影响的选项的先前设置。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. 如果在发出快捷 SET 语句后设置了受快捷 SET 语句影响的 SET 选项,则该单个 SET 语句将覆盖相应的快捷设置。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. 快捷 SET 语句的示例将为 SET ANSI_DEFAULTS。An example of a shortcut SET statement would be SET ANSI_DEFAULTS.

  • 使用批处理时,数据库上下文由使用 USE 语句建立的批处理决定。When batches are used, the database context is determined by the batch that is established by using the USE statement. 在存储过程的外部运行的以及批处理中的计划外查询和所有其他语句,将继承通过 USE 语句建立的数据库和连接的选项设置。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.

  • 多个活动的结果集 (MARS) 请求共享一个全局状态,该状态包含最新会话 SET 选项设置。Multiple Active Result Set (MARS) requests share a global state that contains the most recent session SET option settings. 每个请求执行时都可以修改 SET 选项。When each request executes, it can modify the SET options. 更改特定于设置这些更改所在的请求上下文,不影响其他并发 MARS 请求。The changes are specific to the request context in which they're set, and don't affect other concurrent MARS requests. 但是,请求执行完成后,新的 SET 选项会被复制到全局会话状态。However, after the request execution is completed, the new SET options are copied to the global session state. 在此更改之后,同一会话中执行的新请求将使用这些新的 SET 选项设置。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. 例如,存储过程 db1.dbo.sp1 调用存储过程 db2.dbo.sp2 时,执行存储过程 sp1 时将使用数据库 db1 的当前兼容级别设置,执行存储过程 sp2 时将使用数据库 db2 的当前兼容级别设置 。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.

  • 如果 Transact-SQLTransact-SQL 语句关注的对象驻留在多个数据库中,则将对该语句应用当前数据库上下文和当前连接上下文。When a Transact-SQLTransact-SQL statement concerns objects that are in multiple databases, the current database context and the current connection context applies to that statement. 在这种情况下,如果 Transact-SQLTransact-SQL 语句在批处理中,则当前连接上下文是 USE 语句定义的数据库;如果 Transact-SQLTransact-SQL 语句在存储过程中,则连接上下文是包含该存储过程的数据库。In this case, if Transact-SQLTransact-SQL statement is in a batch, the current connection context is the database defined by the USE statement; if the Transact-SQLTransact-SQL statement is in a stored procedure, the connection context is the database that contains the stored procedure.

  • 针对计算列或索引视图创建并操作索引时,必须将这些 SET 选项设置为 ON:ARITHABORT、CONCAT_NULL_YIELDS_NULL、QUOTED_IDENTIFIER、ANSI_NULLS、ANSI_PADDING 和 ANSI_WARNINGS。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. 将选项 NUMERIC_ROUNDABORT 设置为 OFF。Set the option NUMERIC_ROUNDABORT to OFF.

    如果未将以上任一选项设置为要求的值,则对索引视图或带计算列索引的表进行 INSERT、UPDATE、DELETE、DBCC CHECKDB 和 DBCC CHECKTABLE 操作时将失败。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 ServerSQL Server 将发出一个错误,并列出所有设置不正确的选项。will raise an error listing all the options that are incorrectly set. 同时,SQL ServerSQL Server 将对这些表或索引视图运行 SELECT 语句,就好像计算列或视图中不存在索引。Also, SQL ServerSQL 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.

  • 如果 SET RESULT_SET_CACHING 设置为 ON,它为当前客户端会话启用结果集缓存功能。When SET RESULT_SET_CACHING is ON, it enables the result caching feature for the current client session. 如果已在数据库级别将 Result_set_caching 设置为 OFF,就无法为会话将它设置为 ON。Result_set_caching cannot be turned ON for a session if it is turned OFF at the database level. 如果 SET RESULT_SET_CACHING 设置为 OFF,它为当前客户端会话禁用结果集缓存功能。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. 适用范围:Azure SQL 数据仓库 Gen2Applies to: Azure SQL Data Warehouse Gen2