SET 陳述式 (Transact-SQL)SET Statements (Transact-SQL)

適用於: 否SQL Server否Azure SQL Database是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_configureuser 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 ANSI_DEFAULTS 是捷徑 SET 陳述式的範例。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.

  • Multiple Active Result Set (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 預存程序時,會使用 db1 資料庫目前的相容性層級設定來執行 sp1 預存程序,並使用 db2 資料庫目前的相容性層級設定來執行 sp2 預存程序。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. 若在資料庫層級關閉 (OFF) Result_set_caching,便無法為工作階段開啟 (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. 變更此設定需要 public 角色中的成員資格。Changing this setting requires membership in the public role. 適用於:Azure SQL 資料倉儲 Gen2Applies to: Azure SQL Data Warehouse Gen2