ALTER DATABASE SET 選項 (Transact-SQL)ALTER DATABASE SET options (Transact-SQL)

設定 SQL ServerSQL ServerAzure SQL DatabaseAzure SQL DatabaseSQL 資料倉儲SQL Data Warehouse 中的資料庫選項。Sets database options in Microsoft SQL ServerSQL Server, Azure SQL DatabaseAzure SQL Database, and SQL 資料倉儲SQL Data Warehouse. 如需其他 ALTER DATABASE 選項,請參閱 ALTER DATABASEFor other ALTER DATABASE options, see ALTER DATABASE.

選取下列其中一個索引標籤,以查看您所使用特定 SQL 版本的語法、引數、備註、權限和範例。Select one of the following tabs for the syntax, arguments, remarks, permissions, and examples for a particular SQL version that you're working with.

如需語法慣例的詳細資訊,請參閱 Transact-SQL 語法慣例For more information about the syntax conventions, see Transact-SQL syntax conventions.

選取產品Select a product

在下列資料列中,選取您感興趣的產品名稱。In the following row, select whichever product name you're interested in. 視您所選取的產品而定會在本網頁的這裡顯示不同內容。Doing that displays different content here on this webpage, appropriate for whichever product you select.

* SQL Server *  * SQL Server *   SQL Database
單一資料庫/彈性集區
SQL Database
single database/elastic pool
SQL Database
受控執行個體
SQL Database
managed instance
SQL 資料
倉儲
SQL Data
Warehouse

 

SQL ServerSQL Server

資料庫鏡像、AlwaysOn 可用性群組Always On availability groups 和相容性層級為 SET 選項,但是礙於篇幅的因素,將會在個別的文章中描述。Database mirroring, AlwaysOn 可用性群組Always On availability groups, and compatibility levels are SET options but are described in separate articles because of their length. 如需詳細資訊,請參閱 ALTER DATABASE 資料庫鏡像ALTER DATABASE SET HADRALTER DATABASE 相容性層級For more information, see ALTER DATABASE Database Mirroring, ALTER DATABASE SET HADR, and ALTER DATABASE compatibility level.

資料庫範圍設定用來設定數個個別資料庫層級的資料庫設定。Database scoped configurations are used to set several database configurations at the individual database level. 如需詳細資訊,請參閱 ALTER DATABASE SCOPED CONFIGURATIONFor more information, see ALTER DATABASE SCOPED CONFIGURATION.

注意

目前工作階段的許多資料庫 SET 選項都可以使用 SET 陳述式來設定,且通常是在應用程式連線時由其加以設定。Many database set options can be configured for the current session by using SET statements and are often configured by applications when they connect. 工作階段層級 SET 選項會覆寫 ALTER DATABASE SET 值。Session-level set options override the ALTER DATABASE SET values. 下列章節所述的資料庫選項皆為未明確提供其他 SET 選項值,您可針對工作階段進行設定的值。The database options described in the following sections are values that you can set for sessions that don't explicitly provide other set option values.

語法Syntax

ALTER DATABASE { database_name | CURRENT }
SET
{
    <option_spec> [ ,...n ] [ WITH <termination> ]
}

<option_spec> ::=
{
    <accelerated_database_recovery>
  | <auto_option>
  | <automatic_tuning_option>
  | <change_tracking_option>
  | <containment_option>
  | <cursor_option>
  | <database_mirroring_option>
  | <date_correlation_optimization_option>
  | <db_encryption_option>
  | <db_state_option>
  | <db_update_option>
  | <db_user_access_option>
  | <delayed_durability_option>
  | <external_access_option>
  | FILESTREAM ( <FILESTREAM_option> )
  | <HADR_options>
  | <mixed_page_allocation_option>
  | <parameterization_option>
  | <query_store_options>
  | <recovery_option>
  | <remote_data_archive_option>
  | <service_broker_option>
  | <snapshot_option>
  | <sql_option>
  | <target_recovery_time_option>
  | <termination>
  | <temporal_history_retention>
}
;

<accelerated_database_recovery> ::=
{
    ACCELERATED_DATABASE_RECOVERY = { ON | OFF }
     [ ( PERSISTENT_VERSION_STORE_FILEGROUP = { filegroup name } ) ];
}

<auto_option> ::=
{
    AUTO_CLOSE { ON | OFF }
  | AUTO_CREATE_STATISTICS { OFF | ON [ ( INCREMENTAL = { ON | OFF } ) ] }
  | AUTO_SHRINK { ON | OFF }
  | AUTO_UPDATE_STATISTICS { ON | OFF }
  | AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}

<automatic_tuning_option> ::=
{
    AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = { ON | OFF } )
}

<change_tracking_option> ::=
{
    CHANGE_TRACKING
   {
       = OFF
     | = ON [ ( <change_tracking_option_list > [,...n] ) ]
     | ( <change_tracking_option_list> [,...n] )
   }
}

<change_tracking_option_list> ::=
{
   AUTO_CLEANUP = { ON | OFF }
 | CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }
}

<containment_option> ::=
   CONTAINMENT = { NONE | PARTIAL }

<cursor_option> ::=
{
    CURSOR_CLOSE_ON_COMMIT { ON | OFF }
  | CURSOR_DEFAULT { LOCAL | GLOBAL }
}

<database_mirroring_option>
  ALTER DATABASE Database Mirroring

<date_correlation_optimization_option> ::=
    DATE_CORRELATION_OPTIMIZATION { ON | OFF }

<db_encryption_option> ::=
    ENCRYPTION { ON | OFF | SUSPEND | RESUME }

<db_state_option> ::=
    { ONLINE | OFFLINE | EMERGENCY }

<db_update_option> ::=
    { READ_ONLY | READ_WRITE }

<db_user_access_option> ::=
    { SINGLE_USER | RESTRICTED_USER | MULTI_USER }

<delayed_durability_option> ::=
    DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

<external_access_option> ::=
{
    DB_CHAINING { ON | OFF }
  | TRUSTWORTHY { ON | OFF }
  | DEFAULT_FULLTEXT_LANGUAGE = { <lcid> | <language name> | <language alias> }
  | DEFAULT_LANGUAGE = { <lcid> | <language name> | <language alias> }
  | NESTED_TRIGGERS = { OFF | ON }
  | TRANSFORM_NOISE_WORDS = { OFF | ON }
  | TWO_DIGIT_YEAR_CUTOFF = { 1753, ..., 2049, ..., 9999 }
}
<FILESTREAM_option> ::=
{
    NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL
  | DIRECTORY_NAME = <directory_name>
}
<HADR_options> ::=
    ALTER DATABASE SET HADR

<mixed_page_allocation_option> ::=
    MIXED_PAGE_ALLOCATION { OFF | ON }

<parameterization_option> ::=
    PARAMETERIZATION { SIMPLE | FORCED }

<query_store_options> ::=
{
    QUERY_STORE
    {
 = OFF
        | = ON [ ( <query_store_option_list> [,...n] ) ]
        | ( < query_store_option_list> [,...n] )
        | CLEAR [ ALL ]
    }
}

<query_store_option_list> ::=
{
      OPERATION_MODE = { READ_WRITE | READ_ONLY }
    | CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = number )
    | DATA_FLUSH_INTERVAL_SECONDS = number
    | MAX_STORAGE_SIZE_MB = number
    | INTERVAL_LENGTH_MINUTES = number
    | SIZE_BASED_CLEANUP_MODE = { AUTO | OFF }
    | QUERY_CAPTURE_MODE = { ALL | AUTO | CUSTOM | NONE }
    | MAX_PLANS_PER_QUERY = number
    | WAIT_STATS_CAPTURE_MODE = { ON | OFF }
    | QUERY_CAPTURE_POLICY = ( <query_capture_policy_option_list> [,...n] )
}

<query_capture_policy_option_list> :: =
{
    STALE_CAPTURE_POLICY_THRESHOLD = number { DAYS | HOURS }
    | EXECUTION_COUNT = number
    | TOTAL_COMPILE_CPU_TIME_MS = number
    | TOTAL_EXECUTION_CPU_TIME_MS = number
}

<recovery_option> ::=
{
    RECOVERY { FULL | BULK_LOGGED | SIMPLE }
  | TORN_PAGE_DETECTION { ON | OFF }
  | PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
}

<remote_data_archive_option> ::=
{
    REMOTE_DATA_ARCHIVE =
    {
        ON ( SERVER = <server_name> ,
{CREDENTIAL = <db_scoped_credential_name>
   | FEDERATED_SERVICE_ACCOUNT = ON | OFF
}
      )
      | OFF
    }
}

<service_broker_option> ::=
{
    ENABLE_BROKER
  | DISABLE_BROKER
  | NEW_BROKER
  | ERROR_BROKER_CONVERSATIONS  
  | HONOR_BROKER_PRIORITY { ON | OFF}
}

<snapshot_option> ::=
{
    ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
  | READ_COMMITTED_SNAPSHOT {ON | OFF }
  | MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = {ON | OFF }
}
<sql_option> ::=
{
    ANSI_NULL_DEFAULT { ON | OFF }
  | ANSI_NULLS { ON | OFF }
  | ANSI_PADDING { ON | OFF }
  | ANSI_WARNINGS { ON | OFF }
  | ARITHABORT { ON | OFF }
  | COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 }
  | CONCAT_NULL_YIELDS_NULL { ON | OFF }
  | NUMERIC_ROUNDABORT { ON | OFF }
  | QUOTED_IDENTIFIER { ON | OFF }
  | RECURSIVE_TRIGGERS { ON | OFF }
}

<target_recovery_time_option> ::=
    TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }

<termination>::=
{
    ROLLBACK AFTER number [ SECONDS ]
  | ROLLBACK IMMEDIATE
  | NO_WAIT
}
<temporal_history_retention>::=TEMPORAL_HISTORY_RETENTION { ON | OFF }

引數Arguments

database_name database_name
要修改之資料庫的名稱。The name of the database to be modified.

CURRENTCURRENT
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本)Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later)

會在目前資料庫中執行動作。Runs the action in the current database. 所有選項在所有內容中不支援 CURRENTCURRENT isn't supported for all options in all contexts. 如果 CURRENT 失敗,請提供資料庫名稱。If CURRENT fails, provide the database name.

<accelerated_database_recovery> ::= <accelerated_database_recovery> ::=
適用於SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x))

啟用每個資料庫的加速資料庫復原 (ADR)。Enables accelerated database recovery (ADR) per-database. 依據 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 中的預設,ADR 設定為 OFF。ADR is set to OFF by default in SQL Server 2019 (15.x)SQL Server 2019 (15.x). 透過使用此語法,您就有為持續版本存放區 (PVS) 資料指定特定檔案群組的選項。By using this syntax, you have the option to designate a specific file group for the Persistent Version Store (PVS) data. 若沒有指定檔案群組,PVS 將會儲存在 PRIMARY 檔案群組中。If no file group is specified, the PVS will be stored in the PRIMARY file group. 如需範例和詳細資訊,請參閱加速資料庫復原For examples and more information, see Accelerated database recovery.

<auto_option> ::=<auto_option> ::=

控制自動選項。Controls automatic options.

AUTO_CLOSE { ON | OFF }AUTO_CLOSE { ON | OFF }
ONON
資料庫會完整關閉,而當最後一位使用者結束之後,便會將其資源釋放出來。The database is shut down cleanly and its resources are freed after the last user exits.

當使用者試圖重新使用資料庫時,便會自動重新開啟資料庫。The database automatically reopens when a user tries to use the database again. 例如,當使用者發出 USE database_name 陳述式時,就會發生重新開啟行為。For example, this reopen behavior occurs when a user issues a USE database_name statement. 當 AUTO_CLOSE 設定為 ON 時,資料庫可能會正常關機。The database may shut down cleanly with AUTO_CLOSE set to ON. 如果是這種情況,則要等到使用者嘗試在下次重新啟動Database EngineDatabase Engine時使用資料庫之後,才會重新開啟資料庫。If so, the database doesn't reopen until a user tries to use the database the next time the Database EngineDatabase Engine restarts.

OFFOFF
在最後一個使用者結束之後,資料庫仍保持開啟狀態。The database remains open after the last user exits.

對於桌面資料庫而言,AUTO_CLOSE 選項非常有用,因為它可讓您將資料庫檔案當做一般檔案來管理。The AUTO_CLOSE option is useful for desktop databases because it allows for database files to be managed as regular files. 您可以移動它們、複製它們來建立備份,甚至可以用電子郵件將它們傳給其他使用者。They can be moved, copied to make backups, or even emailed to other users. AUTO_CLOSE 處理序為非同步;重複開啟和關閉資料庫不會降低效能。The AUTO_CLOSE process is asynchronous; repeatedly opening and closing the database doesn't reduce performance.

注意

自主資料庫或 SQL DatabaseSQL Database 無法使用 AUTO_CLOSE 選項。The AUTO_CLOSE option isn't available in a contained database or on SQL DatabaseSQL Database. 您可以檢查 sys.databases 目錄檢視中 is_auto_close_on 資料行或 DATABASEPROPERTYEX 函式 IsAutoClose 屬性來判斷這個選項的狀態。You can determine this option's status by examining the is_auto_close_on column in the sys.databases catalog view or the IsAutoClose property of the DATABASEPROPERTYEX function.

當 AUTO_CLOSE 設為 ON 時,sys.databases 目錄檢視中某些資料行及 DATABASEPROPERTYEX 函式會傳回 NULL,因為資料庫無法擷取資料。When AUTO_CLOSE is set to ON, some columns in the sys.databases catalog view and the DATABASEPROPERTYEX function will return NULL because the database is unavailable to retrieve the data. 若要解決這個問題,請執行 USE 陳述式來開啟資料庫。To resolve this issue, run a USE statement to open the database.

資料庫鏡像需要 AUTO_CLOSE OFF。Database mirroring requires AUTO_CLOSE OFF.

當資料庫設定為 AUTOCLOSE = ON 時,起始自動資料庫關閉的作業會清除 SQL ServerSQL Server 執行個體的計畫快取。When the database is set to AUTOCLOSE = ON, an operation that initiates an automatic database shutdown clears the plan cache for the instance of SQL ServerSQL Server. 清除計畫快取會導致重新編譯所有後續執行計畫,而且可能會導致查詢效能突然暫時下降。Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. SQL Server 2005 (9.x)SQL Server 2005 (9.x)Service Pack 2 和更新版本中,針對每個清除計畫快取的快取存放區,SQL ServerSQL Server 錯誤記錄檔會包含下列資訊訊息:「由於某些資料庫維護或重新設定作業,SQL ServerSQL Server 的 '%s' 快取存放區 (計畫快取的一部分) 發生 %d 次快取存放區排清」。In SQL Server 2005 (9.x)SQL Server 2005 (9.x) Service Pack 2 and later, for each cleared cache store in the plan cache, the SQL ServerSQL Server error log contains the following informational message: " SQL ServerSQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations." 只要在該時間間隔內快取發生排清,這個訊息就會每五分鐘記錄一次。This message is logged every five minutes as long as the cache is flushed within that time interval.

AUTO_CREATE_STATISTICS { ON | OFF }AUTO_CREATE_STATISTICS { ON | OFF }
ONON
查詢最佳化工具會視需要針對查詢述詞中的單一資料行建立統計資料,以便改善查詢計劃和查詢效能。Query Optimizer creates statistics on single columns in query predicates, as necessary, to improve query plans and query performance. 這些單一資料行統計資料是在查詢最佳化工具編譯查詢時所建立。These single-column statistics are created when Query Optimizer compiles queries. 它只會針對尚未成為現有統計資料物件第一個資料行的資料行建立單一資料行統計資料。The single-column statistics are created only on columns that aren't already the first column of an existing statistics object.

預設值是 ON。The default setting is ON. 我們建議您針對大部分資料庫使用預設設定。We recommend that you use the default setting for most databases.

OFFOFF
查詢最佳化工具不會在編譯查詢時針對查詢述詞中的單一資料行建立統計資料。Query Optimizer doesn't create statistics on single columns in query predicates when it's compiling queries. 將這個選項設定為 OFF 可能會導致次佳查詢計劃並降低查詢效能。Setting this option to OFF can cause suboptimal query plans and degraded query performance.

您可以檢查 sys.databases 目錄檢視中 is_auto_create_stats_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_auto_create_stats_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAutoCreateStatistics 屬性來判斷狀態。You can also determine the status by examining the IsAutoCreateStatistics property of the DATABASEPROPERTYEX function.

如需詳細資訊,請參閱統計資料中的<使用資料庫範圍統計資料選項>一節。For more information, see the section "Using the Database-wide statistics options" in Statistics.

INCREMENTAL = ON | OFF INCREMENTAL = ON | OFF
適用於SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later) and Azure SQL DatabaseAzure SQL Database

將 AUTO_CREATE_STATISTICS 設定為 ON,並將 INCREMENTAL 設定為 ON。Set AUTO_CREATE_STATISTICS to ON, and set INCREMENTAL to ON. 此設定會在每次支援累加統計資料時,以累加方式自動建立統計資料。This sets automatically created stats as incremental whenever incremental stats are supported. 預設值是 OFF。The default value is OFF. 如需詳細資訊,請參閱 CREATE STATISTICSFor more information, see CREATE STATISTICS.

AUTO_SHRINK { ON | OFF } ONAUTO_SHRINK { ON | OFF } ON
資料庫檔案是定期壓縮的候選項。The database files are candidates for periodic shrinking.

資料檔案和記錄檔都可以自動壓縮。Both data files and log files can be automatically shrunk. 只有在您將資料庫設定為 SIMPLE 復原模式或備份記錄時,AUTO_SHRINK 才會縮減交易記錄的大小。AUTO_SHRINK reduces the size of the transaction log only if you set the database to SIMPLE recovery model or if you back up the log. 當您將 AUTO_SHRINK 設定為 OFF 時,便不會在定期檢查未用空間時,自動壓縮資料庫檔案。When you set AUTO_SHRINK to OFF, the database files aren't automatically shrunk during periodic checks for unused space.

當超出 25% 的檔案包含未用空間時,AUTO_SHRINK 選項便會壓縮檔案。The AUTO_SHRINK option shrinks files when more than 25 percent of the file contains unused space. 它會將檔案壓縮成兩種大小的其中一種 (以較大者為準):It shrinks the file to one of two sizes (whichever is larger):

  • 25% 的檔案是未用空間大小The size at which 25 percent of the file is unused space
  • 檔案建立時的大小The size of the file when it was created

您無法壓縮唯讀資料庫。You can't shrink a read-only database.

OFFOFF
在定期檢查未用空間時,不自動壓縮資料庫檔案。The database files are not automatically shrunk during periodic checks for unused space.

您可以檢查 sys.databases 目錄檢視中 is_auto_shrink_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_auto_shrink_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAutoShrink 屬性來判斷狀態。You can also determine the status by examining the IsAutoShrink property of the DATABASEPROPERTYEX function.

注意

自主資料庫無法使用 AUTO_SHRINK 選項。The AUTO_SHRINK option isn't available in a Contained Database.

AUTO_UPDATE_STATISTICS { ON | OFF }AUTO_UPDATE_STATISTICS { ON | OFF }
ONON
指定當查詢使用統計資料且它們可能已過期時,查詢最佳化工具就會更新這些統計資料。Specifies that Query Optimizer updates statistics when they're used by a query and when they might be out-of-date. 當插入、更新、刪除或合併作業變更資料表或索引檢視表中的資料分佈之後,統計資料就會變成過期。Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. 查詢最佳化工具會計算自從上次更新統計資料以來資料修改的次數,並將修改次數與某個閾值比較,藉以判斷統計資料是否可能已經過期。Query Optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. 此臨界值是以資料表或索引檢視表中的資料列數目為基礎。The threshold is based on the number of rows in the table or indexed view.

查詢最佳化工具會在編譯查詢及執行快取查詢計劃之前,檢查是否有過期的統計資料。Query Optimizer checks for out-of-date statistics before it compiles a query and runs a cached query plan. 查詢最佳化工具會在查詢述詞中使用資料行、資料表和索引檢視表,來判斷哪些統計資料可能已過期。Query Optimizer uses the columns, tables, and indexed views in the query predicate to determine which statistics might be out-of-date. 查詢最佳化工具會在編譯查詢之前判斷這項資訊。Query Optimizer determines this information before it compiles a query. 在執行快取查詢計劃之前,Database EngineDatabase Engine 會確認查詢計劃是否參考最新的統計資料。Before running a cached query plan, the Database EngineDatabase Engine verifies that the query plan references up-to-date statistics.

AUTO_UPDATE_STATISTICS 選項會套用至針對索引所建立的統計資料、查詢述詞中的單一資料行,以及使用 CREATE STATISTICS 陳述式所建立的統計資料。The AUTO_UPDATE_STATISTICS option applies to statistics created for indexes, single-columns in query predicates, and statistics that are created by using the CREATE STATISTICS statement. 此外,這個選項也會套用至篩選的統計資料。This option also applies to filtered statistics.

預設值是 ON。The default is ON. 我們建議您針對大部分資料庫使用預設設定。We recommend that you use the default setting for most databases.

您可以使用 AUTO_UPDATE_STATISTICS_ASYNC 選項來指定要以同步或非同步方式更新統計資料。Use the AUTO_UPDATE_STATISTICS_ASYNC option to specify whether the statistics are updated synchronously or asynchronously.

OFFOFF
指定當查詢使用統計資料時,查詢最佳化工具不會更新這些統計資料。Specifies that Query Optimizer doesn't update statistics when they're used by a query. 當統計資料可能已過期時,查詢最佳化工具也不會更新這些統計資料。Query Optimizer also doesn't update statistics when they might be out-of-date. 將這個選項設定為 OFF 可能會導致次佳查詢計劃並降低查詢效能。Setting this option to OFF can cause suboptimal query plans and degraded query performance.

您可以檢查 sys.databases 目錄檢視中 is_auto_update_stats_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_auto_update_stats_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAutoUpdateStatistics 屬性來判斷狀態。You can also determine the status by examining the IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.

如需詳細資訊,請參閱統計資料中的<使用資料庫範圍統計資料選項>一節。For more information, see the section "Using the Database-wide statistics options" in Statistics.

AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
ONON
指定 AUTO_UPDATE_STATISTICS 選項的統計資料更新是非同步的。Specifies that statistics updates for the AUTO_UPDATE_STATISTICS option are asynchronous. 查詢最佳化工具在編譯查詢之前,不會等候統計資料更新完成。Query Optimizer doesn't wait for statistics updates to complete before it compiles queries.

除非 AUTO_UPDATE_STATISTICS 設為 ON,否則將這個選項設為 ON 沒有作用。Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is set to ON.

根據預設,AUTO_UPDATE_STATISTICS_ASYNC 選項為 OFF,而查詢最佳化工具會同步更新統計資料。By default, the AUTO_UPDATE_STATISTICS_ASYNC option is OFF, and Query Optimizer updates statistics synchronously.

OFFOFF
指定 AUTO_UPDATE_STATISTICS 選項的統計資料更新是同步的。Specifies that statistics updates for the AUTO_UPDATE_STATISTICS option are synchronous. 查詢最佳化工具在編譯查詢之前,會先等候統計資料更新完成。Query Optimizer waits for statistics updates to complete before it compiles queries.

注意

除非 AUTO_UPDATE_STATISTICS 設為 ON,否則將這個選項設為 OFF 沒有作用。Setting this option to OFF has no effect unless AUTO_UPDATE_STATISTICS is set to ON.

您可以檢查 sys.databases 目錄檢視中 is_auto_update_stats_async_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_auto_update_stats_async_on column in the sys.databases catalog view.

如需描述何時應使用同步或非同步統計資料更新的詳細資訊,請參閱統計資料中的<統計資料選項>一節。For more information that describes when to use synchronous or asynchronous statistics updates, see the "Statistics options" section in Statistics.

<automatic_tuning_option> ::= <automatic_tuning_option> ::=
適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x))

啟用或停用FORCE_LAST_GOOD_PLAN 自動調整選項。Enables or disables FORCE_LAST_GOOD_PLAN Automatic tuning option.

FORCE_LAST_GOOD_PLAN = { ON | OFF }FORCE_LAST_GOOD_PLAN = { ON | OFF }
ONON
Database EngineDatabase Engine 會對新查詢計劃將造成效能衰退的 Transact-SQLTransact-SQL 查詢,自動強制執行最後一個已知的良好計劃。The Database EngineDatabase Engine automatically forces the last known good plan on the Transact-SQLTransact-SQL queries where new query plan causes performance regressions. Database EngineDatabase Engine 會使用強制方案持續監視 Transact-SQLTransact-SQL 查詢的查詢效能。The Database EngineDatabase Engine continuously monitors query performance of the Transact-SQLTransact-SQL query with the forced plan.

如果效能有所提升,Database EngineDatabase Engine 會繼續使用最後一個已知的良好計劃。If there are performance gains, the Database EngineDatabase Engine will keep using last known good plan. 如果未偵測到效能提升,Database EngineDatabase Engine 會產生新的查詢計劃。If performance gains are not detected, the Database EngineDatabase Engine will produce a new query plan. 如果未啟用查詢存放區,或其不在「讀寫」 模式中,陳述式便會失敗。The statement will fail if the Query Store isn't enabled or if the Query Store isn't in Read-Write mode.

OFFOFF
Database EngineDatabase Engine 會在 sys.dm_db_tuning_recommendations 檢視中報告查詢計劃變更所造成可能的查詢效能衰退。The Database EngineDatabase Engine reports potential query performance regressions caused by query plan changes in sys.dm_db_tuning_recommendations view. 不過,不會自動套用這些建議。However, these recommendations are not automatically applied. 使用者可以套用檢視中顯示的 Transact-SQLTransact-SQL 指令碼,來監視使用中建議並修正已識別的問題。Users can monitor active recommendations and fix identified problems by applying Transact-SQLTransact-SQL scripts that are shown in the view. 預設值是 OFF。The default value is OFF.

<change_tracking_option> ::= <change_tracking_option> ::=
適用於SQL ServerSQL ServerSQL DatabaseSQL DatabaseApplies to: SQL ServerSQL Server and SQL DatabaseSQL Database

控制變更追蹤選項。Controls change tracking options. 您可以啟用變更追蹤、設定選項、變更選項,以及停用變更追蹤。You can enable change tracking, set options, change options, and disable change tracking. 如需範例,請參閱本文稍後的<範例>一節。For examples, see the "Examples" section later in this article.

ONON
啟用資料庫的變更追蹤。Enables change tracking for the database. 當您啟用變更追蹤時,也可以設定 AUTO CLEANUP 和 CHANGE RETENTION 選項。When you enable change tracking, you can also set the AUTO CLEANUP and CHANGE RETENTION options.

AUTO_CLEANUP = { ON | OFF }AUTO_CLEANUP = { ON | OFF }
ONON
在經過指定的保留週期後,將會自動移除變更追蹤資訊。Change tracking information is automatically removed after the specified retention period.

OFFOFF
不會從資料庫中自動移除變更追蹤資料。Change tracking data isn't automatically removed from the database.

CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }
指定在資料庫中保存變更追蹤資訊的最小週期。Specifies the minimum period for keeping change tracking information in the database. 只有當 AUTO_CLEANUP 值為 ON 時,才會移除資料。Data is removed only when the AUTO_CLEANUP value is ON.

retention_period 是一個整數,它會指定保留週期的數值元件。retention_period is an integer that specifies the numerical component of the retention period.

預設保留期間為 2 天The default retention period is 2 days. 最小保留週期是 1 分鐘。The minimum retention period is 1 minute. 預設保留期類型為 DAYSThe default retention type is DAYS.

OFFOFF
停用資料庫的變更追蹤。Disables change tracking for the database. 在您停用資料庫的變更追蹤之前,請先在所有資料表上停用變更追蹤。Disable change tracking on all tables before you disable change tracking off the database.

<containment_option> ::= <containment_option> ::=
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本)Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later)

控制資料庫內含項目選項。Controls database containment options.

CONTAINMENT = { NONE | PARTIAL}CONTAINMENT = { NONE | PARTIAL}
NONE
資料庫不是自主資料庫。The database isn't a contained database.

PARTIALPARTIAL
資料庫是自主資料庫。The database is a contained database. 如果資料庫已啟用複寫、變更資料擷取或變更追蹤,無法將資料庫內含項目設為部分。Setting database containment to partial will fail if the database has replication, change data capture, or change tracking enabled. 在一次失敗之後錯誤檢查會停止。Error checking stops after one failure. 如需自主資料庫的詳細資訊,請參閱 自主資料庫For more information about contained databases, see Contained Databases.

<cursor_option> ::=<cursor_option> ::=

控制資料指標選項。Controls cursor options.

CURSOR_CLOSE_ON_COMMIT { ON | OFF }CURSOR_CLOSE_ON_COMMIT { ON | OFF }
ONON
當您認可或回復交易時,將會關閉任何開啟的資料指標。Any cursors open when you commit or roll back a transaction are closed.

OFFOFF
當認可交易時,資料指標維持開啟狀態;回復交易會關閉任何資料指標,但定義為 INSENSITIVE 或 STATIC 的資料指標除外。Cursors remain open when a transaction is committed; rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.

利用 SET 陳述式來設定的連接層級設定會覆寫 CURSOR_CLOSE_ON_COMMIT 的預設資料庫設定。Connection-level settings that are set by using the SET statement override the default database setting for CURSOR_CLOSE_ON_COMMIT. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將工作階段的 CURSOR_CLOSE_ON_COMMIT 設定為 OFF。ODBC and OLE DB clients issue a connection-level SET statement setting CURSOR_CLOSE_ON_COMMIT to OFF for the session, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET CURSOR_CLOSE_ON_COMMITFor more information, see SET CURSOR_CLOSE_ON_COMMIT.

您可以檢查 sys.databases 目錄檢視中 is_cursor_close_on_commit_on 資料行或 DATABASEPROPERTYEX 函式的 IsCloseCursorsOnCommitEnabled 屬性來判斷這個選項的狀態。You can determine this option's status by examining the is_cursor_close_on_commit_on column in the sys.databases catalog view or the IsCloseCursorsOnCommitEnabled property of the DATABASEPROPERTYEX function.

CURSOR_DEFAULT { LOCAL | GLOBAL }CURSOR_DEFAULT { LOCAL | GLOBAL }
適用於SQL ServerSQL ServerApplies to: SQL ServerSQL Server

控制資料指標範圍是使用 LOCAL 還是 GLOBAL。Controls whether cursor scope uses LOCAL or GLOBAL.

LOCALLOCAL
如果您指定 LOCAL,且未在建立資料指標時將資料指標定義為 GLOBAL,則資料指標的範圍為本機。When you specify LOCAL and don't define a cursor as GLOBAL when you create the cursor, the cursor's scope is local. 具體來說,此範圍是您建立資料指標所在批次、預存程序或觸發程序的本機範圍。Specifically, the scope is local to the batch, stored procedure, or trigger in which you created the cursor. 資料指標名稱只在這個範圍內有效。The cursor name is valid only within this scope.

批次、預存程序或觸發程序內的區域資料指標變數或是預存程序 OUTPUT 參數可以參考資料指標。The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. 當批次、預存程序或觸發程序結束時,會隱含地解除配置資料指標。The cursor is implicitly deallocated when the batch, stored procedure, or trigger ends. 除非在 OUTPUT 參數中傳回資料指標,否則會解除配置資料指標。The cursor is deallocated unless it was passed back in an OUTPUT parameter. 資料指標可能會在 OUTPUT 參數中傳回。The cursor might be passed back in an OUTPUT parameter. 如果資料指標以此方式傳回,則當最後一個參考資料指標的變數解除配置或離開範圍時,便會解除配置資料指標。If the cursor passes back this way, the cursor is deallocated when the last variable that references the cursor is deallocated or goes out of scope.

GLOBALGLOBAL
如果指定 GLOBAL,且資料指標並未在建立時定義為 LOCAL,則資料指標範圍即為連線的全域範圍。When GLOBAL is specified, and a cursor isn't defined as LOCAL when created, the scope of the cursor is global to the connection. 連接所執行的任何預存程序或批次內都可以參考資料指標名稱。The cursor name can be referenced in any stored procedure or batch executed by the connection.

只有在中斷連接時,才會隱含地取消配置資料指標。The cursor is implicitly deallocated only at disconnect. 如需詳細資訊,請參閱 DECLARE CURSORFor more information, see DECLARE CURSOR.

您可以檢查 sys.databases 目錄檢視中 is_local_cursor_default 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_local_cursor_default column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsLocalCursorsDefault 屬性來判斷狀態。You can also determine the status by examining the IsLocalCursorsDefault property of the DATABASEPROPERTYEX function.

<database_mirroring> <database_mirroring>
適用於SQL ServerSQL ServerApplies to: SQL ServerSQL Server

如需引數描述,請參閱 ALTER DATABASE 資料庫鏡像For the argument descriptions, see ALTER DATABASE Database Mirroring.

<date_correlation_optimization_option> ::= <date_correlation_optimization_option> ::=
適用於SQL ServerSQL ServerApplies to: SQL ServerSQL Server

控制 date_correlation_optimization 選項。Controls the date_correlation_optimization option.

DATE_CORRELATION_OPTIMIZATION { ON | OFF }DATE_CORRELATION_OPTIMIZATION { ON | OFF }
ONON
SQL ServerSQL Server 會維護相互關聯統計資料,其中 FOREIGN KEY 條件約束會連結資料庫中的任意兩個資料表,且這些資料表具有 datetime 資料行。maintains correlation statistics where a FOREIGN KEY constraint links any two tables in the database and the tables have datetime columns.

OFFOFF
不維護相互關聯統計資料。Correlation statistics are not maintained.

若要將 DATE_CORRELATION_OPTIMIZATION 設定為 ON,除了正在執行 ALTER DATABASE 陳述式的連線外,資料庫都不可以有使用中連線。To set DATE_CORRELATION_OPTIMIZATION to ON, there must be no active connections to the database except for the connection that's executing the ALTER DATABASE statement. 之後就可以支援多個連接。Afterwards, multiple connections are supported.

您可以檢查 sys.databases 目錄檢視中 is_date_correlation_on 資料行來判斷此選項的目前設定。The current setting of this option can be determined by examining the is_date_correlation_on column in the sys.databases catalog view.

<db_encryption_option> ::=<db_encryption_option> ::=

控制資料庫加密狀態。Controls the database encryption state.

ENCRYPTION { ON | OFF | SUSPEND | RESUME }ENCRYPTION { ON | OFF | SUSPEND | RESUME }
ONON
將資料庫設為加密。Sets the database to be encrypted.

OFFOFF
將資料庫設為不加密。Sets the database to not be encrypted.

SUSPENDSUSPEND
適用於SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x))
可在透明資料加密已啟用或停用之後,或是加密金鑰已變更之後,用來暫停加密掃描。Can be used to pause the encryption scan after Transparent Data Encryption has been enabled or disabled, or after the encryption key has been changed.

RESUMERESUME
適用於SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x))
可用來繼續先前暫停的加密掃描。Can be used to resume a previously paused encryption scan.

如需資料庫加密的詳細資訊,請參閱透明資料加密Azure SQL Database 的透明資料加密For more information about database encryption, see Transparent Data Encryption, and Transparent Data Encryption with Azure SQL Database.

在資料庫層級啟用加密時,所有的檔案群組都會加密。When encryption is enabled at the database level, all file groups will be encrypted. 任何新檔案群組都會繼承加密的屬性。Any new file groups will inherit the encrypted property. 如果資料庫中有任何檔案群組設定為 READ ONLY,則資料庫加密作業將會失敗。If any file groups in the database are set to READ ONLY, the database encryption operation will fail.

您可以使用 sys.dm_database_encryption_keys 動態管理檢視來查看資料庫的加密狀態,以及加密掃描的狀態。You can see the encryption state of the database as well as the state of the encryption scan by using the sys.dm_database_encryption_keys dynamic management view.

<db_state_option> ::= <db_state_option> ::=
適用於SQL ServerSQL ServerApplies to: SQL ServerSQL Server

控制資料庫的狀態。Controls the state of the database.

OFFLINEOFFLINE
關閉資料庫,並將其完整關機,再標示為離線。The database is closed, shut down cleanly, and marked offline. 資料庫在離線狀態時,無法修改。The database can't be modified while it's offline.

ONLINEONLINE
資料庫在開啟狀態,可供使用。The database is open and available for use.

EMERGENCYEMERGENCY
資料庫標示為 READ_ONLY、記錄已停用並限定只有系統管理員固定伺服器角色的成員才可存取。The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY 主要是做為疑難排解的用途。EMERGENCY is primarily used for troubleshooting purposes. 例如,由於記錄檔損毀而被標示有疑問的資料庫可以設定為 EMERGENCY 狀態。For example, a database marked as suspect because of a corrupted log file can be set to the EMERGENCY state. 此設定可讓系統管理員進行資料庫的唯讀存取。This setting could enable the system administrator read-only access to the database. 只有系統管理員 (sysadmin) 固定伺服器角色的成員,可以將資料庫設定為 EMERGENCY 狀態。Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.

需要主題資料庫的 ALTER DATABASE 權限,才能將資料庫變更為離線或緊急狀態,且需要伺服器層級 ALTER ANY DATABASE 權限,才能將資料庫從離線移動到線上。Requires the ALTER DATABASE permission for the subject database, to change a database to the offline or emergency state, and the server level ALTER ANY DATABASE permission to move a database from offline to online.

您可以檢查 sys.databases 目錄檢視中 statestate_desc 資料行來判斷此選項的狀態。You can determine this option's status by examining the state and state_desc columns in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 Status 屬性來判斷狀態。You can also determine the status by examining the Status property of the DATABASEPROPERTYEX function. 如需詳細資訊,請參閱 Database StatesFor more information, see Database States.

標示為 RESTORING 的資料庫不能設定為 OFFLINE、ONLINE 或 EMERGENCY。A database marked as RESTORING can't be set to OFFLINE, ONLINE, or EMERGENCY. 在使用中的還原作業期間,或是由於備份檔損毀導致資料庫或記錄檔的還原作業失敗時,資料庫都有可能處於 RESTORING 狀態。A database may be in the RESTORING state during an active restore operation or when a restore operation of a database or log file fails because of a corrupted backup file.

<db_update_option> ::=<db_update_option> ::=

控制是否允許更新資料庫。Controls whether updates are allowed on the database.

READ_ONLYREAD_ONLY
使用者可以從資料庫中讀取資料,但無法加以修改。Users can read data from the database but not modify it.

注意

如果要提高查詢的效能,請先更新統計資料,再將資料庫設為 READ_ONLY。To improve query performance, update statistics before setting a database to READ_ONLY. 如果資料庫設為 READ_ONLY 之後,還需要其他的統計資料,Database EngineDatabase Engine 將會在 tempdb 中建立統計資料。If additional statistics are needed after a database is set to READ_ONLY, the Database EngineDatabase Engine will create statistics in tempdb. 如需唯讀資料庫統計資料的詳細資訊,請參閱統計資料For more information about statistics for a read-only database, see Statistics.

READ_WRITEREAD_WRITE
資料庫可以執行讀寫作業。The database is available for read and write operations.

若要變更這個狀態,您必須具有資料庫的獨佔存取權。To change this state, you must have exclusive access to the database. 如需詳細資訊,請參閱 SINGLE_USER 子句。For more information, see the SINGLE_USER clause.

注意

SQL DatabaseSQL Database同盟資料庫上,SET { READ_ONLY | READ_WRITE } 會停用。On SQL DatabaseSQL Database federated databases, SET { READ_ONLY | READ_WRITE } is disabled.

<db_user_access_option> ::=<db_user_access_option> ::=

控制使用者對資料庫的存取權。Controls user access to the database.

SINGLE_USER 適用於SQL ServerSQL ServerSINGLE_USER Applies to: SQL ServerSQL Server

指定每次只能有一位使用者存取資料庫。Specifies that only one user at a time can access the database. 如果您指定 SINGLE_USER,且沒有其他使用者連線到資料庫,就會封鎖 ALTER DATABASE 陳述式,直到所有使用者都中斷與指定資料庫的連線為止。If you specify SINGLE_USER and other users connect to the database, the ALTER DATABASE statement is blocked until all users disconnect from the specified database. 若要覆寫這個行為,請參閱 WITH <termination> 子句。To override this behavior, see the WITH <termination> clause.

資料庫會保留在 SINGLE_USER 模式中,即使當設定選項的使用者已登出也一樣。此時其他使用者可以連接到這個資料庫,但只能有一位。The database remains in SINGLE_USER mode even if the user that set the option signs out. At that point, a different user, but only one, can connect to the database.

將資料庫設為 SINGLE_USER 之前,請先確定 AUTO_UPDATE_STATISTICS_ASYNC 選項是否設為 OFF。Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. 當設定為 ON 時,用來更新統計資料的背景執行緒會取得資料庫連線,而您將無法以單一使用者模式存取資料庫。When set to ON, the background thread used to update statistics takes a connection against the database, and you'll be unable to access the database in single-user mode. 若要檢視這個選項的狀態,請查詢 sys.databases 目錄檢視中的 is_auto_update_stats_async_on 資料行。To view the status of this option, query the is_auto_update_stats_async_on column in the sys.databases catalog view. 如果選項設為 ON,請執行下列工作:If the option is set to ON, perform the following tasks:

  1. 將 AUTO_UPDATE_STATISTICS_ASYNC 設為 OFF。Set AUTO_UPDATE_STATISTICS_ASYNC to OFF.

  2. 查詢 sys.dm_exec_background_job_queue 動態管理檢視,檢查是否有作用中的非同步統計資料作業。Check for active asynchronous statistics jobs by querying the sys.dm_exec_background_job_queue dynamic management view.

如果有使用中的作業,請等待作業完成,或使用 KILL STATS JOB 手動終止作業。If there are active jobs, either allow the jobs to complete or manually terminate them by using KILL STATS JOB.

RESTRICTED_USERRESTRICTED_USER
只允許 db_owner 固定資料庫角色與 dbcreatorsysadmin 固定伺服器角色的成員連線至資料庫。Allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database. RESTRICTED_USER 不會限制其數目。RESTRICTED_USER doesn't limit their number. 使用 ALTER DATABASE 陳述式 termination 子句指定的時間範圍,中斷與資料庫的所有連線。Disconnect all connections to the database using the timeframe specified by the ALTER DATABASE statement's termination clause. 在資料庫進入 RESTRICTED_USER 狀態之後,不合格使用者的連接嘗試都會遭到拒絕。After the database has transitioned to the RESTRICTED_USER state, connection attempts by unqualified users are refused.

MULTI_USERMULTI_USER
允許所有具備適當權限來連接資料庫的使用者。All users that have the appropriate permissions to connect to the database are allowed.

您可以檢查 sys.databases 目錄檢視中 user_access 資料行來判斷這個選項的狀態。You can determine this option's status by examining the user_access column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 UserAccess 屬性來判斷狀態。You can also determine the status by examining the UserAccess property of the DATABASEPROPERTYEX function.

<delayed_durability_option> ::= <delayed_durability_option> ::=
適用於SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更新版本)Applies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later)

控制交易是否認可完全持久或延遲的持久。Controls whether transactions commit fully durable or delayed durable.

DISABLEDDISABLED
接在 SET DISABLED 後面的所有交易都是完全持久。All transactions following SET DISABLED are fully durable. 在不可部分完成的區塊或 Commit 陳述式中設定的任何持久性選項都會被忽略。Any durability options set in an atomic block or commit statement are ignored.

ALLOWEDALLOWED
接在 SET ALLOWED 後面的所有交易都是完全持久或延遲持久,視不可部分完成的區塊或 Commit 陳述式中所設定持久性選項而定。All transactions following SET ALLOWED are either fully durable or delayed durable, depending upon the durability option set in the atomic block or commit statement.

FORCEDFORCED
接在 SET FORCED 後面的所有交易都是延遲持久。All transactions following SET FORCED are delayed durable. 在不可部分完成的區塊或 Commit 陳述式中設定的任何持久性選項都會被忽略。Any durability options set in an atomic block or commit statement are ignored.

<external_access_option> ::= <external_access_option> ::=
適用於SQL ServerSQL ServerApplies to: SQL ServerSQL Server

控制外部資源 (如另一個資料庫的物件) 是否能夠存取資料庫。Controls whether the database can be accessed by external resources, such as objects from another database.

DB_CHAINING { ON | OFF }DB_CHAINING { ON | OFF }
ONON
資料庫可以是跨資料庫擁有權鏈結的來源或目標。Database can be the source or target of a cross-database ownership chain.

OFFOFF
資料庫無法參與跨資料庫擁有權鏈結。Database can't participate in cross-database ownership chaining.

重要

當 cross db ownership chaining 伺服器選項為 0 (OFF) 時,SQL ServerSQL Server 的執行個體可以辨識這項設定。The instance of SQL ServerSQL Server will recognize this setting when the cross db ownership chaining server option is 0 (OFF). 當 cross db ownership chaining 為 1 (ON) 時,不論這個選項的值為何,所有使用者資料庫都可以參與跨資料庫擁有權鏈結。When cross db ownership chaining is 1 (ON), all user databases can participate in cross-database ownership chains, regardless of the value of this option. 您可以使用 sp_configure 來設定這個選項。This option is set by using sp_configure.

若要設定這個選項,則需要有資料庫的 CONTROL SERVER 權限。To set this option, requires CONTROL SERVER permission on the database.

您無法在 master、model 和 tempdb 系統資料庫上設定 DB_CHAINING 選項。The DB_CHAINING option can't be set on the master, model, and tempdb system databases.

您可以檢查 sys.databases 目錄檢視中 is_db_chaining_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_db_chaining_on column in the sys.databases catalog view.

TRUSTWORTHY { ON | OFF }TRUSTWORTHY { ON | OFF }
ONON
使用模擬內容的資料庫模組 (如使用者定義函式或預存程序) 能夠在資料庫之外存取資源。Database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database.

OFFOFF
模擬內容中的資料庫模組無法存取資料庫之外資源。Database modules in an impersonation context can't access resources outside the database.

每當附加資料庫時,TRUSTWORTHY 都設為 OFF。TRUSTWORTHY is set to OFF whenever the database is attached.

依預設,除了 msdb 資料庫以外,所有的系統資料庫都會將 TRUSTWORTHY 設為 OFF。By default, all system databases except the msdb database have TRUSTWORTHY set to OFF. model 和 tempdb 資料庫的這個值不可變更。The value can't be changed for the model and tempdb databases. 建議您絕對不要將 master 資料庫的 TRUSTWORTHY 選項設為 ON。We recommend that you never set the TRUSTWORTHY option to ON for the master database.

若要設定這個選項,則需要有資料庫的 CONTROL SERVER 權限。To set this option, requires CONTROL SERVER permission on the database.

您可以檢查 sys.databases 目錄檢視中 is_trustworthy_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_trustworthy_on column in the sys.databases catalog view.

DEFAULT_FULLTEXT_LANGUAGEDEFAULT_FULLTEXT_LANGUAGE
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本)Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later)

指定全文檢索索引資料行的預設語言值。Specifies the default language value for full-text indexed columns.

重要

只有當 CONTAINMENT 已經設為 PARTIAL 時,才允許這個選項。This option is allowable only when CONTAINMENT has been set to PARTIAL. 如果 CONTAINMENT 設定為 NONE,便會發生錯誤。If CONTAINMENT is set to NONE, errors will occur.

DEFAULT_LANGUAGEDEFAULT_LANGUAGE
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本)Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later)

指定所有新建立之登入的預設語言。Specifies the default language for all newly created logins. 語言可藉由提供地區設定識別碼 (LCID)、語言名稱或語言別名來指定。Language can be specified by providing the local ID (lcid), the language name, or the language alias. 如需可接受語言名稱和別名的清單,請參閱 sys.syslanguagesFor a list of acceptable language names and aliases, see sys.syslanguages. 只有當 CONTAINMENT 已經設為 PARTIAL 時,才允許這個選項。This option is allowable only when CONTAINMENT has been set to PARTIAL. 如果 CONTAINMENT 設定為 NONE,便會發生錯誤。If CONTAINMENT is set to NONE, errors will occur.

NESTED_TRIGGERSNESTED_TRIGGERS
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本)Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later)

指定 AFTER 觸發程序是否可以重疊顯示;亦即,執行起始另一個觸發程序的動作,後者再起始另一個觸發程序等。Specifies whether an AFTER trigger can cascade; that is, perform an action that initiates another trigger, which initiates another trigger, and so on. 只有當 CONTAINMENT 已經設為 PARTIAL 時,才允許這個選項。This option is allowable only when CONTAINMENT has been set to PARTIAL. 如果 CONTAINMENT 設定為 NONE,便會發生錯誤。If CONTAINMENT is set to NONE, errors will occur.

TRANSFORM_NOISE_WORDSTRANSFORM_NOISE_WORDS
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本)Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later)

如果非搜尋字或停用字詞造成全文檢索查詢的布林運算失敗,用來隱藏錯誤訊息。Used to suppress an error message if noise words, or stopwords, cause a Boolean operation on a full-text query to fail. 只有當 CONTAINMENT 已經設為 PARTIAL 時,才允許這個選項。This option is allowable only when CONTAINMENT has been set to PARTIAL. 如果 CONTAINMENT 設定為 NONE,便會發生錯誤。If CONTAINMENT is set to NONE, errors will occur.

TWO_DIGIT_YEAR_CUTOFFTWO_DIGIT_YEAR_CUTOFF
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本)Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later)

從 1753 到 9999 中指定一個整數來表示截止年份,以便將兩位數年份解譯為四位數年份。Specifies an integer from 1753 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years. 只有當 CONTAINMENT 已經設為 PARTIAL 時,才允許這個選項。This option is allowable only when CONTAINMENT has been set to PARTIAL. 如果 CONTAINMENT 設定為 NONE,便會發生錯誤。If CONTAINMENT is set to NONE, errors will occur.

<FILESTREAM_option> ::= <FILESTREAM_option> ::=
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本)Applies to: SQL ServerSQL Server ( SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later)

控制 FileTable 的設定。Controls the settings for FileTables.

NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }
OFFOFF
已停用 FileTable 資料的非交易式存取。Non-transactional access to FileTable data is disabled.

READ_ONLYREAD_ONLY
非交易式處理序可以讀取此資料庫中 FileTable 的 FILESTREAM 資料。FILESTREAM data in FileTables in this database can be read by non-transactional processes.

FULLFULL
啟用 FileTable 中 FILESTREAM 資料的完整非交易式存取。Enables full, non-transactional access to FILESTREAM data in FileTables is enabled.

DIRECTORY_NAME = <目錄名稱> DIRECTORY_NAME = <directory_name>
Windows 相容的目錄名稱。A windows-compatible directory name. 此名稱在 SQL ServerSQL Server 執行個體的所有資料庫層級目錄名稱之間必須是唯一的。This name should be unique among all the database-level directory names in the SQL ServerSQL Server instance. 無論定序設定為何,唯一性比較皆不會區分大小寫。Uniqueness comparison is case-insensitive, regardless of collation settings. 在此資料庫中建立 FileTable 之前,必須先設定這個選項。This option must be set before creating a FileTable in this database.

<HADR_options> ::= <HADR_options> ::=
適用於SQL ServerSQL ServerApplies to: SQL ServerSQL Server

請參閱 ALTER DATABASE SET HADRSee ALTER DATABASE SET HADR.

<mixed_page_allocation_option> ::= <mixed_page_allocation_option> ::=
適用於SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本)Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later)

控制資料庫是否可以針對資料表或索引的前八個頁面使用混合範圍來建立初始頁面。Controls whether the database can create initial pages using a mixed extent for the first eight pages of a table or index.

MIXED_PAGE_ALLOCATION { OFF | ON }MIXED_PAGE_ALLOCATION { OFF | ON }
OFFOFF
資料庫一律使用制式範圍建立初始頁面。The database always creates initial pages using uniform extents. OFF 是預設值。OFF is the default value.

ONON
資料庫可以使用混合範圍建立起始頁面。The database can create initial pages using mixed extents.

對於所有系統資料庫,這項設定是 ON。This setting is ON for all system databases. tempdb是支援 OFF 的唯一系統資料庫。tempdb is the only system database that supports OFF.

<PARAMETERIZATION_option> ::=<PARAMETERIZATION_option> ::=

控制參數化選項。Controls the parameterization option. 如需參數化的詳細資訊,請參閱查詢處理架構指南For more information on parameterization, see the Query Processing Architecture Guide.

PARAMETERIZATION { SIMPLE | FORCED }PARAMETERIZATION { SIMPLE | FORCED }
SIMPLESIMPLE
根據資料庫的預設行為,將查詢參數化。Queries are parameterized based on the default behavior of the database.

FORCEDFORCED
SQL ServerSQL Server 會將資料庫中的所有查詢參數化。parameterizes all queries in the database.

您可以檢查 sys.databases 目錄檢視中 is_parameterization_forced column 來判斷此選項的目前設定。The current setting of this option can be determined by examining the is_parameterization_forced column in the sys.databases catalog view.

<query_store_options> ::= <query_store_options> ::=
適用於SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本)Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later)

ON | OFF | CLEAR [ ALL ]ON | OFF | CLEAR [ ALL ]
控制是否在此資料庫中啟用查詢存放區,且控制查詢存放區內容的移除。Controls whether the Query Store is enabled in this database, and also controls removing the contents of the Query Store. 如需詳細資訊,請參閱查詢存放區使用案例For more information, see Query Store Usage Scenarios.

ONON
啟用查詢存放區。Enables the Query Store.

OFFOFF
停用查詢存放區。Disables the Query Store. OFF 是預設值。OFF is the default value.

CLEARCLEAR
移除查詢存放區的內容。Remove the contents of the Query Store.

OPERATION_MODE {READ_ONLY |READ_WRITE}OPERATION_MODE { READ_ONLY | READ_WRITE }
描述查詢存放區的作業模式。Describes the operation mode of the Query Store.

READ_WRITEREAD_WRITE
查詢存放區會收集並保存查詢計劃和執行階段執行統計資料資訊。The Query Store collects and persists query plan and runtime execution statistics information.

READ_ONLYREAD_ONLY
可以從查詢存放區讀取資訊,但不會新增資訊。Information can be read from the Query Store, but new information isn't added. 如果查詢存放區所發出的最大空間已用盡,則查詢存放區會將作業模式變更為 READ_ONLY。If the maximum issued space of the Query Store has been exhausted, the Query Store will change is operation mode to READ_ONLY.

CLEANUP_POLICYCLEANUP_POLICY
描述查詢存放區的資料保留原則。Describes the data retention policy of the Query Store. STALE_QUERY_THRESHOLD_DAYS 會決定在查詢存放區中保留查詢資訊的天數。STALE_QUERY_THRESHOLD_DAYS determines the number of days for which the information for a query is kept in the Query Store. STALE_QUERY_THRESHOLD_DAYS 的類型為 bigintSTALE_QUERY_THRESHOLD_DAYS is type bigint.

DATA_FLUSH_INTERVAL_SECONDSDATA_FLUSH_INTERVAL_SECONDS
決定將寫入查詢存放區之資料保存到磁碟的頻率。Determines the frequency at which data written to the Query Store is persisted to disk. 為了獲得最佳效能,查詢存放區所收集的資料會以非同步方式寫入磁碟。To optimize for performance, data collected by the Query Store is asynchronously written to the disk. 此非同步傳輸發生的頻率是使用 DATA_FLUSH_INTERVAL_SECONDS 引數所設定。The frequency at which this asynchronous transfer occurs is configured by using the DATA_FLUSH_INTERVAL_SECONDS argument. DATA_FLUSH_INTERVAL_SECONDS 的類型為 bigintDATA_FLUSH_INTERVAL_SECONDS is type bigint.

MAX_STORAGE_SIZE_MBMAX_STORAGE_SIZE_MB
決定發給查詢存放區的空間。Determines the space issued to the Query Store. MAX_STORAGE_SIZE_MB 的類型為 bigintMAX_STORAGE_SIZE_MB is type bigint.

注意

系統不會嚴格強制執行 MAX_STORAGE_SIZE_MB 的限制。MAX_STORAGE_SIZE_MB limit isn't strictly enforced. 只有當查詢存放區將資料寫入磁碟時,系統才會檢查儲存體大小。Storage size is checked only when Query Store writes data to disk. 這個間隔是由 DATA_FLUSH_INTERVAL_SECONDS 選項或是 Management StudioManagement Studio 查詢存放區對話方塊選項的 [資料排清間隔] 所設定。This interval is set by the DATA_FLUSH_INTERVAL_SECONDS option or the Management StudioManagement Studio Query Store dialog box option Data Flush Interval. 間隔預設值為 900 秒 (15 分鐘)。The interval default value is 900 seconds (or 15 minutes).
如果查詢存放區違反儲存體大小檢查之間的 MAX_STORAGE_SIZE_MB 限制,即會轉換為唯讀模式。If the Query Store has breached the MAX_STORAGE_SIZE_MB limit between storage size checks, it will transition to read-only mode. 如果啟用 SIZE_BASED_CLEANUP_MOD,也會觸發強制執行 MAX_STORAGE_SIZE_MB 限制的清除機制。If SIZE_BASED_CLEANUP_MODE is enabled, the cleanup mechanism to enforce the MAX_STORAGE_SIZE_MB limit is also triggered.

INTERVAL_LENGTH_MINUTESINTERVAL_LENGTH_MINUTES
決定執行階段執行統計資料彙總至查詢存放區的時間間隔。Determines the time interval at which runtime execution statistics data is aggregated into the Query Store. 若要將空間使用量最佳化,在執行階段統計資料存放區中的執行階段執行統計資料會透過固定的時段彙總。To optimize for space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed time window. 這個固定的時段是使用 INTERVAL_LENGTH_MINUTES 引數所設定。This fixed time window is configured by using the INTERVAL_LENGTH_MINUTES argument. INTERVAL_LENGTH_MINUTES 的類型為 bigintINTERVAL_LENGTH_MINUTES is type bigint.

SIZE_BASED_CLEANUP_MODE { AUTO | OFF }SIZE_BASED_CLEANUP_MODE { AUTO | OFF }
控制當總資料量接近大小上限時,是否會自動啟用清除。Controls whether cleanup automatically activates when the total amount of data gets close to maximum size.

AUTOAUTO
當磁碟上大小達到 90% 的 MAX_STORAGE_SIZE_MB 時,就會自動啟用以大小為依據的清除。Size-based cleanup will be automatically activated when size on disk reaches 90% of MAX_STORAGE_SIZE_MB. 以大小為依據之清除會先移除成本最高和最舊的查詢。Size-based cleanup removes the least expensive and oldest queries first. 它會在達到 MAX_STORAGE_SIZE_MB 的 80% 左右停止。這個值是預設設定值。It stops at approximately 80% of MAX_STORAGE_SIZE_MB.This value is the default configuration value.

OFFOFF
不會自動啟用以大小為依據的清除。Size-based cleanup won't be automatically activated.

SIZE_BASED_CLEANUP_MODE 的類型為 nvarcharSIZE_BASED_CLEANUP_MODE is type nvarchar.

QUERY_CAPTURE_MODE { ALL | AUTO | NONE | CUSTOM }QUERY_CAPTURE_MODE { ALL | AUTO | NONE | CUSTOM }
指定目前使用中的查詢擷取模式。Designates the currently active query capture mode. 每個模式都會定義特定的查詢擷取原則。Each mode defines specific query capture policies.

注意

當查詢擷取模式被設定為 ALL、AUTO 或 CUSTOM 時,一律都會擷取資料指標、預存程序中的查詢,以及原生編譯的查詢。Cursors, queries inside Stored Procedures, and Natively compiled queries are always captured when the query capture mode is set to ALL, AUTO, or CUSTOM.

ALLALL
擷取所有的查詢。Captures all queries. ALLSQL ServerSQL Server (從 SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x)) 的預設組態值。ALL is the default configuration value for SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017 (14.x)SQL Server 2017 (14.x)).

AUTOAUTO
根據執行計數和資源耗用量擷取相關的查詢。Capture relevant queries based on execution count and resource consumption. 這是 SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始) 和 Azure SQL DatabaseAzure SQL Database 的預設組態值。This is the default configuration value for SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)) and Azure SQL DatabaseAzure SQL Database.

NONE
停止擷取新的查詢。Stop capturing new queries. 查詢存放區將會繼續收集已擷取查詢的編譯和執行階段統計資料。The Query Store will continue to collect compile and runtime statistics for queries that were captured already. 因為您可能會錯過擷取重要查詢,請小心使用此設定。Use this configuration with caution since you may miss capturing important queries.

CUSTOMCUSTOM
適用於SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 3.0 開始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 3.0)

允許對 QUERY_CAPTURE_POLICY 選項的控制。Allows control over the QUERY_CAPTURE_POLICY options.

QUERY_CAPTURE_MODE 的類型為 nvarcharQUERY_CAPTURE_MODE is type nvarchar.

max_plans_per_queryMAX_PLANS_PER_QUERY
定義為每個查詢維持的計劃最大數目。Defines the maximum number of plans maintained for each query. 預設值是 200。The default is 200. MAX_PLANS_PER_QUERY 的類型為 intMAX_PLANS_PER_QUERY is type int.

<query_capture_policy_option_list> :: = <query_capture_policy_option_list> :: =
適用於SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 3.0 開始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 3.0)

控制查詢存放區擷取原則選項。Controls the Query Store capture policy options. 除了 STALE_CAPTURE_POLICY_THRESHOLD,這些選項會定義必須進行的 OR 條件,以便在定義的過時擷取原則閾值中擷取查詢。Except for STALE_CAPTURE_POLICY_THRESHOLD, these options define the OR conditions that need to happen for queries to be captured in the defined Stale Capture Policy Threshold value.

STALE_CAPTURE_POLICY_THRESHOLD = number { DAYS | HOURS }STALE_CAPTURE_POLICY_THRESHOLD = number { DAYS | HOURS }
定義評估間隔週期以判斷是否應該擷取查詢。Defines the evaluation interval period to determine if a query should be captured. 預設為 1 天,且可設為 1 小時到七天。The default is 1 day, and it can be set from 1 hour to seven days. number 的類型為 intnumber is type int.

EXECUTION_COUNTEXECUTION_COUNT
定義在評估週期內執行查詢的次數。Defines the number of times a query is executed over the evaluation period. 預設為 30,這表示針對過時擷取原則閾值的預設值,查詢必須在一天內至少執行 30 次才能保存在查詢存放區中。The default is 30, which means that for the default Stale Capture Policy Threshold, a query must execute at least 30 times in one day to be persisted in the Query Store. EXECUTION_COUNT 的類型為 intEXECUTION_COUNT is type int.

TOTAL_COMPILE_CPU_TIME_MSTOTAL_COMPILE_CPU_TIME_MS
定義查詢在評估週期內使用的總耗用編譯 CPU 時間。Defines total elapsed compile CPU time used by a query over the evaluation period. 預設為 1000,這表示針對過時擷取原則閾值的預設值,查詢在一天之中的查詢編譯期間內,必須總共至少有一秒的 CPU 時間,才能保存在查詢存放區中。The default is 1000 which means that for the default Stale Capture Policy Threshold, a query must have a total of at least one second of CPU time spent during query compilation in one day to be persisted in the Query Store. TOTAL_COMPILE_CPU_TIME_MS 的類型為 intTOTAL_COMPILE_CPU_TIME_MS is type int.

TOTAL_EXECUTION_CPU_TIME_MSTOTAL_EXECUTION_CPU_TIME_MS
定義查詢在評估週期內使用的總耗用執行 CPU 時間。Defines total elapsed execution CPU time used by a query over the evaluation period. 預設為 100,這表示針對過時擷取原則閾值的預設值,查詢在一天之中的執行期間內,必須總共至少有 100 毫秒的 CPU 時間,才能保存在查詢存放區中。The default is 100 which means that for the default Stale Capture Policy Threshold, a query must have a total of at least 100 ms of CPU time spent during execution in one day to be persisted in the Query Store. TOTAL_EXECUTION_CPU_TIME_MS 的類型為 intTOTAL_EXECUTION_CPU_TIME_MS is type int.

<recovery_option> ::= <recovery_option> ::=
適用於SQL ServerSQL ServerApplies to: SQL ServerSQL Server

控制資料庫復原選項及磁碟 I/O 錯誤檢查。Controls database recovery options and disk I/O error checking.

FULLFULL
在媒體失敗之後,使用交易記錄備份來提供完整復原。Provides full recovery after media failure by using transaction log backups. 如果資料檔案損毀,媒體復原可以還原所有已認可的交易。If a data file is damaged, media recovery can restore all committed transactions. 如需詳細資訊,請參閱復原模式For more information, see Recovery Models.

BULK_LOGGEDBULK_LOGGED
在媒體失敗之後提供復原。Provides recovery after media failure. 結合某些大規模或大量作業之最佳效能與最少記錄使用空間的優點。Combines the best performance and least amount of log-space use for certain large-scale or bulk operations. 如需哪些作業只記錄基本資訊的詳細資訊,請參閱交易記錄For information about what operations can be minimally logged, see The Transaction Log. 在 BULK_LOGGED 復原模式之下,這些作業的只會記錄基本資訊。Under the BULK_LOGGED recovery model, logging for these operations is minimal. 如需詳細資訊,請參閱復原模式For more information, see Recovery Models.

SIMPLESIMPLE
提供使用最少記錄空間的簡單備份策略。A simple backup strategy that uses minimal log space is provided. 當伺服器失敗復原不再需要記錄空間,會自動重複使用這個記錄空間。Log space can be automatically reused when it's no longer required for server failure recovery. 如需詳細資訊,請參閱復原模式For more information, see Recovery Models.

重要

簡單復原模式比另兩種模式更容易管理,但在資料檔案損毀時,遺失資料的風險比較大。The simple recovery model is easier to manage than the other two models but at the expense of greater data loss exposure if a data file is damaged. 在最近的資料庫或差異資料庫備份之後進行的所有變更都會遺失,必須以手動方式重新輸入。All changes since the most recent database or differential database backup are lost and must be manually reentered.

預設復原模式取決於 model 資料庫的復原模式。The default recovery model is determined by the recovery model of the model database. 如需有關選取適當復原模式的詳細資訊,請參閱復原模式For more information about selecting the appropriate recovery model, see Recovery Models.

您可以檢查 sys.databases 目錄檢視中 recovery_modelrecovery_model_desc 資料行來判斷這個選項的狀態。You can determine this option's status by examining the recovery_model and recovery_model_desc columns in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 Recovery 屬性來判斷狀態。You can also determine the status by examining the Recovery property of the DATABASEPROPERTYEX function.

TORN_PAGE_DETECTION { ON | OFF }TORN_PAGE_DETECTION { ON | OFF }
ONON
Database EngineDatabase Engine 能夠偵測出不完整的頁面。Incomplete pages can be detected by the Database EngineDatabase Engine.

OFFOFF
Database EngineDatabase Engine 無法偵測出不完整的頁面。Incomplete pages can't be detected by the Database EngineDatabase Engine.

重要

在未來的 SQL ServerSQL Server 版本中,將移除 TORN_PAGE_DETECTION ON | OFF 語法結構。The syntax structure TORN_PAGE_DETECTION ON | OFF will be removed in a future version of SQL ServerSQL Server. 請避免在新的開發工作中使用這項語法結構,並規劃修改目前使用這項語法結構的應用程式。Avoid using this syntax structure in new development work, and plan to modify applications that currently use the syntax structure. 請改用 PAGE_VERIFY 選項。Use the PAGE_VERIFY option instead.

PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
探索 I/O 路徑錯誤所造成的損毀資料庫頁面。Discovers damaged database pages caused by disk I/O path errors. 磁碟 I/O 路徑錯誤可能是資料庫損毀問題的原因。Disk I/O path errors can be the cause of database corruption problems. 這些錯誤最常是因為頁面寫入磁碟時發生電源故障或磁碟硬體故障所造成。These errors are most often caused by power failures or disk hardware failures that occur at the time the page is written to disk.

CHECKSUMCHECKSUM
計算整個頁面內容的總和檢查碼,當頁面寫入磁碟時,將值儲存在頁首。Calculates a checksum over the contents of the whole page and stores the value in the page header when a page is written to disk. 從磁碟讀取頁面時,總和檢查碼會重新計算並與頁首所儲存的總和檢查碼值做比較。When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header. 如果值都不符,便會在 SQL ServerSQL Server 錯誤記錄檔和 Windows 事件記錄檔中,報告錯誤訊息 824 (表示總和檢查碼失敗)。If the values don't match, error message 824 (indicating a checksum failure) is reported to both the SQL ServerSQL Server error log and the Windows event log. 總和檢查碼失敗表示 I/O 路徑發生問題。A checksum failure indicates an I/O path problem. 判斷主要原因時,需要調查硬體、韌體驅動程式、BIOS、篩選驅動程式 (例如,病毒軟體) 和其他 I/O 路徑元件。To determine the root cause requires investigation of the hardware, firmware drivers, BIOS, filter drivers (such as virus software), and other I/O path components.

TORN_PAGE_DETECTIONTORN_PAGE_DETECTION
將每個 512 位元組磁區的 2 位元模式儲存在 8 KB 資料庫頁面上,當頁面寫入磁碟時,便將其儲存在資料庫頁首。Saves a specific 2-bit pattern for each 512-byte sector in the 8-kilobyte (KB) database page and stored in the database page header when the page is written to disk. 當從磁碟中讀取頁面時,會比較頁首中所儲存的損毀位元和實際的頁面磁區資訊。When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information.

值不符合表示該頁面只有一部分寫入磁碟中。Unmatched values indicate that only part of the page was written to disk. 在這個狀況下,會在 SQL ServerSQL Server 錯誤記錄檔和 Windows 事件記錄檔中,報告錯誤訊息 824 (表示發生損毀頁的錯誤)。In this situation, error message 824 (indicating a torn page error) is reported to both the SQL ServerSQL Server error log and the Windows event log. 如果真的是頁面寫入不完整,通常會由資料庫復原作業來偵測出損毀頁。Torn pages are typically detected by database recovery if it's truly an incomplete write of a page. 不過,其他 I/O 路徑失敗也可能隨時造成損毀頁。However, other I/O path failures can cause a torn page at any time.

NONE
資料庫頁面寫入不會產生 CHECKSUM 或 TORN_PAGE_DETECTION 值。Database page writes won't generate a CHECKSUM or TORN_PAGE_DETECTION value. 在讀取期間,SQL ServerSQL Server 即使頁首包含 CHECKSUM 或 TORN_PAGE_DETECTION 值,亦不會驗證總和檢查碼或損毀頁。SQL ServerSQL Server will not verify a checksum or torn page during a read even if a CHECKSUM or TORN_PAGE_DETECTION value is present in the page header.

當您使用 PAGE_VERIFY 選項時,請考慮下列要點:Consider the following important points when you use the PAGE_VERIFY option:

  • 預設為 CHECKSUMThe default is CHECKSUM.

  • 當使用者或系統資料庫升級至 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 或更新版本時,PAGE_VERIFY 值 (NONE 或 TORN_PAGE_DETECTION) 不會變更。When a user or system database is upgraded to SQL Server 2005 (9.x)SQL Server 2005 (9.x) or a later version, the PAGE_VERIFY value (NONE or TORN_PAGE_DETECTION) isn't changed. 我們建議您變更為 CHECKSUM。We recommend that you change to CHECKSUM.

    注意

    在舊版 SQL ServerSQL Server 中,TempDB 資料庫的 PAGE_VERIFY 資料庫選項設定為 NONE 且無法修改。In earlier versions of SQL ServerSQL Server, the PAGE_VERIFY database option is set to NONE for the TempDB database and can't be modified. SQL Server 2008SQL Server 2008 和更新版本中,新安裝 SQL ServerSQL Server TempDB 資料庫的預設值為 CHECKSUM。In SQL Server 2008SQL Server 2008 and later versions, the default value for the TempDB database is CHECKSUM for new installations of SQL ServerSQL Server. 升級 SQL ServerSQL Server安裝時,預設值仍然維持 NONE。When upgrading an installation SQL ServerSQL Server, the default value remains NONE. 此選項可以進行修改。The option can be modified. 我們建議您針對 tempdb 資料庫使用 CHECKSUM。We recommend that you use CHECKSUM for the tempdb database.

  • TORN_PAGE_DETECTION 可以使用較少資源,但所提供的 CHECKSUM 保護最少。TORN_PAGE_DETECTION may use fewer resources but provides a minimal subset of the CHECKSUM protection.

  • 在資料庫不離線、不鎖定,或不妨礙資料庫並行作業的情況下,可以設定 PAGE_VERIFY。PAGE_VERIFY can be set without taking the database offline, locking the database, or otherwise impeding concurrency on that database.

  • CHECKSUM 與 TORN_PAGE_DETECTION 互斥。CHECKSUM is mutually exclusive to TORN_PAGE_DETECTION. 這兩個選項無法同時啟用。Both options can't be enabled at the same time.

當偵測到損毀頁或總和檢查碼失敗時,您可以還原資料來加以復原,如果失敗只限於索引頁面,您可以重建索引。When a torn page or checksum failure is detected, you can recover by restoring the data or potentially rebuilding the index if the failure is limited only to index pages. 如果您發現總和檢查碼失敗,且要判斷受影響的資料庫頁面類型,請執行 DBCC CHECKDB。If you encounter a checksum failure, to determine the type of database page or pages affected, run DBCC CHECKDB. 如需還原選項的詳細資訊,請參閱 RESTORE 引數For more information about restore options, see RESTORE Arguments. 雖然還原資料可以解決資料損毀問題,但您仍應診斷主要原因 (如磁碟硬體故障),並盡快更正,以防止繼續發生錯誤。Although restoring the data will resolve the data corruption problem, the root cause, for example, disk hardware failure, should be diagnosed and corrected as soon as possible to prevent continuing errors.

SQL ServerSQL Server 會重試任何因總和檢查碼、損毀頁或其他 I/O 錯誤而失敗的讀取作業四次。will retry any read that fails with a checksum, torn page, or other I/O error four times. 如果任何一次重試讀取成功,都會將訊息寫入至錯誤記錄檔。If the read is successful in any one of the retry attempts, a message is written to the error log. 並將繼續執行觸發讀取的命令。The command that triggered the read will continue. 如果重試失敗,此命令便會失敗,且會出現錯誤訊息 824。The command will fail with error message 824 if the retry attempts fail.

如需錯誤訊息 823、824 和 825 的詳細資訊,請參閱:For more information about error messages 823, 824 and 825, see:

您可以檢查 sys.databases 目錄檢視中的 page_verify_option 資料行,或檢查 DATABASEPROPERTYEX 函式的 IsTornPageDetectionEnabled 屬性,以判斷這個選項的目前設定。The current setting of this option can be determined by examining the page_verify_option column in the sys.databases catalog view or the IsTornPageDetectionEnabled property of the DATABASEPROPERTYEX function.

<remote_data_archive_option> ::= <remote_data_archive_option> ::=
適用於SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本)Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later)

為資料庫啟用或停用 Stretch Database。Enables or disables Stretch Database for the database. 如需詳細資訊,請參閱 Stretch DatabaseFor more info, see Stretch Database.

REMOTE_DATA_ARCHIVE = { ON ( SERVER = <server_name> , { CREDENTIAL = <db_scoped_credential_name> | FEDERATED_SERVICE_ACCOUNT = ON | OFF } )| OFF REMOTE_DATA_ARCHIVE = { ON ( SERVER = <server_name> , { CREDENTIAL = <db_scoped_credential_name> | FEDERATED_SERVICE_ACCOUNT = ON | OFF } )| OFF
ONON
為資料庫啟用 Stretch Database。Enables Stretch Database for the database. 如需詳細資訊,包括其他必要條件,請參閱為資料庫啟用 Stretch DatabaseFor more info, including additional prerequisites, see Enable Stretch Database for a database.

需要 db_owner 權限才能為資料表啟用 Stretch Database。Requires db_owner permission to enable Stretch Database for a table. 需要 db_ownerCONTROL DATABASE 權限才能為資料庫啟用 Stretch Database。Requires db_owner and CONTROL DATABASE permissions to enable Stretch Database for a database.

SERVER = <server_name>SERVER = <server_name>
指定 Azure 伺服器的位址。Specifies the address of the Azure server. 包含名稱的 .database.windows.net 部分。Include the .database.windows.net portion of the name. 例如, MyStretchDatabaseServer.database.windows.netFor example, MyStretchDatabaseServer.database.windows.net.

CREDENTIAL = <db_scoped_credential_name>CREDENTIAL = <db_scoped_credential_name>
指定 SQL ServerSQL Server 執行個體用來連接到 Azure 伺服器的資料庫範圍認證。Specifies the database scoped credential that the instance of SQL ServerSQL Server uses to connect to the Azure server. 請先確定認證已存在,再執行此命令。Make sure the credential exists before you run this command. 如需詳細資訊,請參閱 CREATE DATABASE SCOPED CREDENTIALFor more info, see CREATE DATABASE SCOPED CREDENTIAL.

FEDERATED_SERVICE_ACCOUNT = { ON | OFF }FEDERATED_SERVICE_ACCOUNT = { ON | OFF }
當下列條件成立時,您可以使用內部部署 SQL Server 的同盟服務帳戶來與遠端 Azure 伺服器通訊。You can use a federated service account for the on-premises SQL Server to communicate with the remote Azure server when the following conditions are all true.

  • 正在執行之 SQL Server 執行個體下的服務帳戶是網域帳戶。The service account under which the instance of SQL Server is running is a domain account.
  • 網域帳戶所屬的網域,其 Active Directory 與 Azure Active Directory 同盟。The domain account belongs to a domain whose Active Directory is federated with Azure Active Directory.
  • 遠端 Azure 伺服器已設定支援 Azure Active Directory 驗證。The remote Azure server is configured to support Azure Active Directory authentication.
  • 正在執行的 SQL Server 執行個體下服務帳戶必須設定為遠端 Azure 伺服器上 dbmanagersysadmin 帳戶。The service account under which the instance of SQL Server is running must be configured as a dbmanager or sysadmin account on the remote Azure server.

如果您指定同盟服務帳戶為 ON,則您無法也指定 CREDENTIAL 引數。If you specify that the federated service account is ON, you can't also specify the CREDENTIAL argument. 如果您指定 OFF,請提供 CREDENTIAL 引數。Provide the CREDENTIAL argument if you specify OFF.

OFFOFF
為資料庫停用 Stretch Database。Disables Stretch Database for the database. 如需詳細資訊,請參閱 停用 Stretch Database 並帶回遠端資料For more info, see Disable Stretch Database and bring back remote data.

只有資料庫不再包含任何已啟用 Stretch Database 的資料表後,您才能為資料庫停用 Stretch Database。You can only disable Stretch Database for a database after the database no longer contains any tables that are enabled for Stretch Database. 停用 Stretch Database 之後,資料移轉即會停止。After you disable Stretch Database, data migration stops. 此外,查詢結果不再包含來自遠端資料表的結果。Also, query results no longer include results from remote tables.

停用 Stretch 並不會移除遠端資料庫。Disabling Stretch doesn't remove the remote database. 若要刪除遠端資料庫,請使用 Azure 入口網站將其卸除。To delete the remote database, drop it by using the Azure portal.

<service_broker_option> ::= <service_broker_option> ::=
適用於SQL ServerSQL ServerApplies to: SQL ServerSQL Server

控制下列 Service BrokerService Broker 選項:啟用或停用訊息傳遞、設定新的 Service BrokerService Broker 識別碼,或是將交談優先權設定為 ON 或 OFF。Controls the following Service BrokerService Broker options: enables or disables message delivery, sets a new Service BrokerService Broker identifier, or sets conversation priorities to ON or OFF.

ENABLE_BROKERENABLE_BROKER
指定啟用指定之資料庫的 Service BrokerService BrokerSpecifies that Service BrokerService Broker is enabled for the specified database. 啟動訊息傳遞,且 sys.databases 目錄檢視中的 is_broker_enabled 旗標會設為 true。Message delivery is started, and the is_broker_enabled flag is set to true in the sys.databases catalog view. 資料庫會保留現有的 Service BrokerService Broker 識別碼。The database keeps the existing Service BrokerService Broker identifier. 當資料庫是資料庫鏡像設定中的主體時,無法啟用 Service Broker。Service broker can't be enabled while the database is the principal in a database mirroring configuration.

注意

ENABLE_BROKER 需要獨佔式資料庫鎖定。ENABLE_BROKER requires an exclusive database lock. 如果其他工作階段已鎖定資料庫內的資源,ENABLE_BROKER 將會等候到其他工作階段釋放其鎖定為止。If other sessions have locked resources in the database, ENABLE_BROKER will wait until the other sessions release their locks. 若要在使用者資料庫內啟用 Service BrokerService Broker,請確定在您執行 ALTER DATABASE SET ENABLE_BROKER 陳述式之前,沒有其他工作階段正在使用此資料庫,例如將資料庫置於單一使用者模式。To enable Service BrokerService Broker in a user database, ensure that no other sessions are using the database before you run the ALTER DATABASE SET ENABLE_BROKER statement, such as by putting the database in single user mode. 若要在 msdb 資料庫中啟用 Service BrokerService Broker,請先停止 SQL ServerSQL Server Agent,使 Service BrokerService Broker 可以取得必要的鎖定。To enable Service BrokerService Broker in the msdb database, first stop SQL ServerSQL Server Agent so that Service BrokerService Broker can obtain the necessary lock.

DISABLE_BROKERDISABLE_BROKER
指定針對指定的資料庫停用 Service BrokerService BrokerSpecifies that Service BrokerService Broker is disabled for the specified database. 停止訊息傳遞,且 sys.databases 目錄檢視中的 is_broker_enabled 旗標會設為 false。Message delivery is stopped, and the is_broker_enabled flag is set to false in the sys.databases catalog view. 資料庫會保留現有的 Service BrokerService Broker 識別碼。The database keeps the existing Service BrokerService Broker identifier.

NEW_BROKERNEW_BROKER
指定資料庫應該接收新的 Broker 識別碼。Specifies that the database should receive a new broker identifier. 資料庫會作為新的 Service Broker。The database acts as a new service broker. 因此,系統會立即移除資料庫中所有現有的交談,不會產生結束對話訊息。As such, all existing conversations in the database are immediately removed without producing end dialog messages. 您必須使用新的識別碼來重新建立參考舊 Service BrokerService Broker 識別碼的任何路由。Any route that references the old Service BrokerService Broker identifier must be re-created with the new identifier.

ERROR_BROKER_CONVERSATIONSERROR_BROKER_CONVERSATIONS
指定 Service BrokerService Broker 訊息傳遞已啟用。Specifies that Service BrokerService Broker message delivery is enabled. 此設定會保留資料庫的現有 Service BrokerService Broker 識別碼。This setting preserves the existing Service BrokerService Broker identifier for the database. Service BrokerService Broker 會結束資料庫中的所有交談,並產生錯誤。ends all conversations in the database with an error. 此設定可讓應用程式執行現有交談的正規清除工作。This setting enables applications to run regular cleanup for existing conversations.

HONOR_BROKER_PRIORITY {ON | OFF}HONOR_BROKER_PRIORITY {ON | OFF}
ONON
傳送作業會將指派給交談的優先權等級列入考量。Send operations take into consideration the priority levels that are assigned to conversations. 系統會先傳送具高優先權層級交談的訊息,再傳送獲指派低優先權層級交談的訊息。Messages from conversations that have high priority levels are sent before messages from conversations that are assigned low-priority levels.

OFFOFF
傳送作業的執行方式,就像是所有交談都有預設優先權等級一樣。Send operations run as if all conversations have the default priority level.

HONOR_BROKER_PRIORITY 選項的變更對於沒有等候要傳送之訊息的新對話或對話將會立即生效。Changes to the HONOR_BROKER_PRIORITY option take effect immediately for new dialogs or dialogs that have no messages waiting to be sent. 執行 ALTER DATABASE 時具有要傳送訊息之對話要等到對話的某些訊息傳送以後,才會收取新的設定。Dialogs with messages to be sent when ALTER DATABASE is run won't pick up the new setting until some of the messages for the dialog are sent. 所有對話開始使用新設定之前的時間長短可能會有很大的變化。The amount of time before all dialogs start using the new setting can vary considerably.

此屬性的前設定會在 sys.databases 目錄檢視的 is_broker_priority_honored 資料行中報告。The current setting of this property is reported in the is_broker_priority_honored column in the sys.databases catalog view.

<snapshot_option> ::=<snapshot_option> ::=

計算交易隔離等級。Calculates the transaction isolation level.

ALLOW_SNAPSHOT_ISOLATION { ON | OFF }ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
ONON
在資料庫層級啟用快照集選項。Enables Snapshot option at the database level. 啟用時,即使沒有交易使用快照隔離,DML 陳述式也會開始產生資料列版本。When it's enabled, DML statements start generating row versions even when no transaction uses Snapshot Isolation. 啟用此選項之後,交易可以指定 SNAPSHOT 交易隔離等級。Once this option is enabled, transactions can specify the SNAPSHOT transaction isolation level. 當交易執行的隔離等級是 SNAPSHOT 時,所有陳述式都會見到在交易開頭便存在的資料快照集。When a transaction runs at the SNAPSHOT isolation level, all statements see a snapshot of data as it exists at the start of the transaction. 如果執行 SNAPSHOT 隔離等級的交易存取多個資料庫中的資料,此時所有資料庫中的 ALLOW_SNAPSHOT_ISOLATION 都必須設為 ON,或是每當 FROM 子句參考 ALLOW_SNAPSHOT_ISOLATION 是 OFF 的資料庫中的資料表時,交易中的每個陳述式都必須使用鎖定提示。If a transaction running at the SNAPSHOT isolation level accesses data in multiple databases, either ALLOW_SNAPSHOT_ISOLATION must be set to ON in all the databases, or each statement in the transaction must use locking hints on any reference in a FROM clause to a table in a database where ALLOW_SNAPSHOT_ISOLATION is OFF.

OFFOFF
在資料庫層級關閉快照集選項。Turns off the Snapshot option at the database level. 交易無法指定 SNAPSHOT 交易隔離等級。Transactions can't specify the SNAPSHOT transaction isolation level.

當您將 ALLOW_SNAPSHOT_ISOLATION 設為新狀態 (從 ON 設成 OFF,或從 OFF 設成 ON) 時,在認可資料庫中的所有現有交易之前,ALTER DATABASE 並不會將控制權傳回呼叫端。When you set ALLOW_SNAPSHOT_ISOLATION to a new state (from ON to OFF, or from OFF to ON), ALTER DATABASE doesn't return control to the caller until all existing transactions in the database are committed. 如果資料庫已在 ALTER DATABASE 陳述式所指定的狀態中,控制權會立即傳回呼叫端。If the database is already in the state specified in the ALTER DATABASE statement, control is returned to the caller immediately. 如果 ALTER DATABASE 陳述式並沒有很快傳回,請使用 sys.dm_tran_active_snapshot_database_transactions 來判斷是否有長期執行的交易。If the ALTER DATABASE statement doesn't return quickly, use sys.dm_tran_active_snapshot_database_transactions to determine whether there are long-running transactions. 如果取消了 ALTER DATABASE 陳述式,資料庫會保留在 ALTER DATABASE 啟動時的狀態中。If the ALTER DATABASE statement is canceled, the database remains in the state it was in when ALTER DATABASE was started. sys.databases 目錄檢視指出資料庫中快照集隔離交易的狀態。The sys.databases catalog view indicates the state of snapshot-isolation transactions in the database. 如果 snapshot_isolation_state_desc = IN_TRANSITION_TO_ON,ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF 會暫停六秒,然後重試作業。If snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF will pause six seconds and retry the operation.

如果資料庫是 OFFLINE,則您無法變更 ALLOW_SNAPSHOT_ISOLATION 的狀態。You can't change the state of ALLOW_SNAPSHOT_ISOLATION if the database is OFFLINE.

如果您在 READ_ONLY 資料庫中設定 ALLOW_SNAPSHOT_ISOLATION,資料庫後來又設為 READ_WRITE,會保留這個設定。If you set ALLOW_SNAPSHOT_ISOLATION in a READ_ONLY database, the setting will be kept if the database is later set to READ_WRITE.

您可以變更 master、model、msdb 和 tempdb 等資料庫的 ALLOW_SNAPSHOT_ISOLATION 設定。You can change the ALLOW_SNAPSHOT_ISOLATION settings for the master, model, msdb, and tempdb databases. 如果您變更 tempdb 的設定,每次停止和重新啟動 Database EngineDatabase Engine 執行個體時,會保留這個設定。The setting is kept every time the instance of the Database EngineDatabase Engine is stopped and restarted if you change the setting for tempdb. 如果您變更模型的設定,除了 tempdb 以外,任何新建資料庫都會以這個設定為預設值。If you change the setting for model, that setting becomes the default for any new databases that are created, except for tempdb.

根據預設,master 和 msdb 資料庫的這個選項為 ON。The option is ON by default for the master and msdb databases.

您可以檢查 sys.databases 目錄檢視中 snapshot_isolation_state 資料行來判斷此選項的目前設定。The current setting of this option can be determined by examining the snapshot_isolation_state column in the sys.databases catalog view.

READ_COMMITTED_SNAPSHOT { ON | OFF }READ_COMMITTED_SNAPSHOT { ON | OFF }
ONON
在資料庫層級啟用讀取認可快照集選項。Enables Read-Committed Snapshot option at the database level. 啟用時,即使沒有交易使用快照隔離,DML 陳述式也會開始產生資料列版本。When it's enabled, DML statements start generating row versions even when no transaction uses Snapshot Isolation. 啟用此選項之後,指定讀取認可隔離等級的交易即會使用資料列版本設定,而不是鎖定。Once this option is enabled, the transactions specifying the read committed isolation level use row versioning instead of locking. 在 READ COMMITTED 隔離等級執行交易時,所有陳述式都會看到資料的快照,就與陳述式開始時所存在的資料一樣。All statements see a snapshot of data as it exists at the start of the statement when a transaction runs at the READ COMMITTED isolation level.

OFFOFF
在資料庫層級關閉讀取認可快照集選項。Turns off Read-Committed Snapshot option at the database level. 指定 READ COMMITTED 隔離等級的交易會使用鎖定。Transactions specifying the READ COMMITTED isolation level use locking.

若要設定 READ_COMMITTED_SNAPSHOT ON 或 OFF,除了執行 ALTER DATABASE 命令的連接之外,不能有任何使用中資料庫連線。To set READ_COMMITTED_SNAPSHOT ON or OFF, there must be no active connections to the database except for the connection running the ALTER DATABASE command. 不過,資料庫不一定要處於單一使用者模式。However, the database doesn't have to be in single-user mode. 當資料庫是 OFFLINE 時,您無法變更這個選項的狀態。You can't change the state of this option when the database is OFFLINE.

如果您在 READ_ONLY 資料庫中設定 READ_COMMITTED_SNAPSHOT,則當資料庫後來又設為 READ_WRITE 時,會保留這個設定。If you set READ_COMMITTED_SNAPSHOT in a READ_ONLY database, the setting will be kept when the database is later set to READ_WRITE.

master、tempdb 或 msdb 系統資料庫的 READ_COMMITTED_SNAPSHOT 不能設為 ON。READ_COMMITTED_SNAPSHOT can't be turned ON for the master, tempdb, or msdb system databases. 如果您變更 model 的設定,除了 tempdb 以外,這項設定會成為任何新建資料庫的預設值。If you change the setting for model, that setting becomes the default for any new databases created, except for tempdb.

您可以檢查 sys.databases 目錄檢視中 is_read_committed_snapshot_on 資料行來判斷此選項的目前設定。The current setting of this option can be determined by examining the is_read_committed_snapshot_on column in the sys.databases catalog view.

警告

使用 DURABILITY = SCHEMA_ONLY, 和 READ_COMMITTED_SNAPSHOT 建立的資料表,在使用 ALTER DATABASE 進行變更時, 資料表中的資料會遺失。When a table is created with DURABILITY = SCHEMA_ONLY, and READ_COMMITTED_SNAPSHOT is subsequently changed using ALTER DATABASE, data in the table will be lost.

MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT { ON | OFF }MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT { ON | OFF }
適用於SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更新版本)Applies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later)

ONON
當交易隔離等級設定為 SNAPSHOT 以下的任何隔離等級時,記憶體最佳化資料表上所有解譯的 Transact-SQLTransact-SQL 作業都會在 SNAPSHOT 隔離下執行。When the transaction isolation level is set to any isolation level lower than SNAPSHOT, all interpreted Transact-SQLTransact-SQL operations on memory-optimized tables are run under SNAPSHOT isolation. SNAPSHOT 以下的隔離等級範例包括 READ COMMITTED 或 READ UNCOMMITTED。Examples of isolation levels lower than snapshot are READ COMMITTED or READ UNCOMMITTED. 不論是在工作階段層級明確設定交易隔離等級,或隱含使用預設值,都會執行這些作業。These operations run whether the transaction isolation level is set explicitly at the session level, or the default is used implicitly.

OFFOFF
不會為經記憶體最佳化資料表上解譯的 Transact-SQLTransact-SQL 作業提高交易隔離等級。Doesn't elevate the transaction isolation level for interpreted Transact-SQLTransact-SQL operations on memory-optimized tables.

如果資料庫是 OFFLINE,則您無法變更 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 的狀態。You can't change the state of MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT if the database is OFFLINE.

預設選項為 OFF。The default option is OFF.

您可以檢查 sys.databases 目錄檢視中 is_memory_optimized_elevate_to_snapshot_on 資料行來判斷此選項的目前設定。The current setting of this option can be determined by examining the is_memory_optimized_elevate_to_snapshot_on column in the sys.databases catalog view.

<sql_option> ::=<sql_option> ::=

控制資料庫層級的 ANSI 合規性選項。Controls the ANSI compliance options at the database level.

ANSI_NULL_DEFAULT { ON | OFF } 決定未在 CREATE TABLE 或 ALTER TABLE 陳述式中明確定義其可 NULL 性的資料行或CLR 使用者定義型別,其預設值為 NULL 或 NOT NULL。ANSI_NULL_DEFAULT { ON | OFF } Determines the default value, NULL or NOT NULL, of a column or CLR user-defined type for which the nullability isn't explicitly defined in CREATE TABLE or ALTER TABLE statements. 條件約束所定義的資料行會遵循條件約束規則,不論這個設定可能為何。Columns that are defined with constraints follow constraint rules whatever this setting may be.

ONON
未定義資料行的預設值為 NULL。The default value for an undefined column is NULL.

OFFOFF
未定義資料行的預設值為 NOT NULL。The default value for an undefined column is NOT NULL.

利用 SET 陳述式來設定的連接層級設定會覆寫 ANSI_NULL_DEFAULT 的預設資料庫層級設定。Connection-level settings that are set by using the SET statement override the default database-level setting for ANSI_NULL_DEFAULT. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將工作階段的 ANSI_NULL_DEFAULT 設定為 ON。ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULL_DEFAULT to ON for the session, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET ANSI_NULL_DFLT_ONFor more information, see SET ANSI_NULL_DFLT_ON.

對於 ANSI 相容性而言,將資料庫選項 ANSI_NULL_DEFAULT 設為 ON 會將資料庫預設值改成 NULL。For ANSI compatibility, setting the database option ANSI_NULL_DEFAULT to ON changes the database default to NULL.

您可以檢查 sys.databases 目錄檢視中 is_ansi_null_default_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_ansi_null_default_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAnsiNullDefault 屬性來判斷狀態。You can also determine the status by examining the IsAnsiNullDefault property of the DATABASEPROPERTYEX function.

ANSI_NULLS { ON | OFF }ANSI_NULLS { ON | OFF }
ONON
所有對於 Null 值的比較,都會得出 UNKNOWN。All comparisons to a null value evaluate to UNKNOWN.

OFFOFF
比較非 UNICODE 值和 Null 值,如果兩個值都是 NULL,便會得出 TRUE。Comparisons of non-UNICODE values to a null value evaluate to TRUE if both values are NULL.

重要

在未來的 SQL ServerSQL Server 版本中,ANSI_NULLS 一律為 ON,而且明確將此選項設定為 OFF 的任何應用程式都會產生錯誤。In a future version of SQL ServerSQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will produce an error. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

利用 SET 陳述式來設定的連接層級設定會覆寫 ANSI_NULLS 的預設資料庫設定。Connection-level settings that are set by using the SET statement override the default database setting for ANSI_NULLS. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將工作階段的 ANSI_NULLS 設定為 ON。ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULLS to ON for the session, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET ANSI_NULLSFor more information, see SET ANSI_NULLS.

重要

當您建立或變更計算資料行索引或索引檢視表時,SET ANSI_NULLS 也必須設為 ON。SET ANSI_NULLS also must be set to ON when you create or make changes to indexes on computed columns or indexed views.

您可以檢查 sys.databases 目錄檢視中 is_ansi_nulls_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_ansi_nulls_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAnsiNullsEnabled 屬性來判斷狀態。You can also determine the status by examining the IsAnsiNullsEnabled property of the DATABASEPROPERTYEX function.

ANSI_PADDING { ON | OFF }ANSI_PADDING { ON | OFF }
ONON
轉換字串前先填補至相同的長度。Strings are padded to the same length before conversion. 也會填補到相同的長度,再插入 varcharnvarchar 資料類型。Also padded to the same length before inserting to a varchar or nvarchar data type.

OFFOFF
varcharnvarchar 資料行會插入字元值的尾端空格。Inserts trailing blanks in character values into varchar or nvarchar columns. 已插入 varbinary 資料行的二進位值尾端零也會保留。Also leaves trailing zeros in binary values that are inserted into varbinary columns. 值不會填補到資料行的長度。Values aren't padded to the length of the column.

當指定 OFF 時,這個設定只會影響新資料行的定義。When OFF is specified, this setting affects only the definition of new columns.

重要

在未來的 SQL ServerSQL Server 版本中,ANSI_PADDING 一律為 ON,而且明確將此選項設定為 OFF 的應用程式將會產生錯誤。In a future version of SQL ServerSQL Server, ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature. 我們建議您一律將 ANSI_PADDING 設為 ON。We recommend that you always set ANSI_PADDING to ON. 當您建立或操作計算資料行索引或索引檢視表時,ANSI_PADDING 也必須是 ON。ANSI_PADDING must be ON when you create or manipulate indexes on computed columns or indexed views.

當 ANSI_PADDING 設定為 ON 時,允許 Null 的 char(n)binary(n) 資料行會填補到資料行長度。char(n) and binary(n) columns that allow for nulls are padded to the column length when ANSI_PADDING is set to ON. 當 ANSI_PADDING 為 OFF 時,則會修剪尾端空格和尾端零。Trailing blanks and zeros are trimmed when ANSI_PADDING is OFF. 不允許 Null 的 char(n)binary(n) 資料行一律會填補到資料行的長度。char(n) and binary(n) columns that don't allow nulls are always padded to the length of the column.

利用 SET 陳述式來設定的連接層級設定會覆寫 ANSI_PADDING 的預設資料庫層級設定。Connection-level settings that are set by using the SET statement override the default database-level setting for ANSI_PADDING. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將工作階段的 ANSI_PADDING 設定為 ON。ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_PADDING to ON for the session, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET ANSI_PADDINGFor more information, see SET ANSI_PADDING.

您可以檢查 sys.databases 目錄檢視中 is_ansi_padding_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_ansi_padding_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAnsiPaddingEnabled 屬性來判斷狀態。You can also determine the status by examining the IsAnsiPaddingEnabled property of the DATABASEPROPERTYEX function.

ANSI_WARNINGS { ON | OFF }ANSI_WARNINGS { ON | OFF }
ONON
如果發生「除以零」之類的狀況,便會發出錯誤或警告。Errors or warnings are issued when conditions such as divide-by-zero occur. 當彙總函式中出現 Null 值時,也會發出錯誤或警告。Errors and warnings are also issued when null values appear in aggregate functions.

OFFOFF
當發生除以零之類的情況時,不會產生警告,但會傳回 NULL 值。No warnings are raised and null values are returned when conditions such as divide-by-zero occur.

重要

當您建立或變更計算資料行索引或索引檢視表時,SET ANSI_WARNINGS 必須設為 ON。SET ANSI_WARNINGS must be set to ON when you create or make changes to indexes on computed columns or indexed views.

利用 SET 陳述式來設定的連接層級設定會覆寫 ANSI_WARNINGS 的預設資料庫設定。Connection-level settings that are set by using the SET statement override the default database setting for ANSI_WARNINGS. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將工作階段的 ANSI_WARNINGS 設定為 ON。ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_WARNINGS to ON for the session, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET ANSI_WARNINGSFor more information, see SET ANSI_WARNINGS.

您可以檢查 sys.databases 目錄檢視中 is_ansi_warnings_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_ansi_warnings_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAnsiWarningsEnabled 屬性來判斷狀態。You can also determine the status by examining the IsAnsiWarningsEnabled property of the DATABASEPROPERTYEX function.

ARITHABORT { ON | OFF }ARITHABORT { ON | OFF }
ONON
在查詢執行期間,當發生溢位或除以零的錯誤時,查詢會結束。A query is ended when an overflow or divide-by-zero error occurs during query execution.

OFFOFF
當發生這些錯誤之一時,畫面上會顯示警告訊息。A warning message is displayed when one of these errors occurs. 即使顯示警告,查詢、批次或交易還是會繼續處理,如同未發生任何錯誤一樣。The query, batch, or transaction continues to process as if no error occurred even if a warning is displayed.

重要

當您建立或變更計算資料行索引或索引檢視表時,SET ARITHABORT 必須設為 ON。SET ARITHABORT must be set to ON when you create or make changes to indexes on computed columns or indexed views.

您可以檢查 sys.databases 目錄檢視中 is_arithabort_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_arithabort_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsArithmeticAbortEnabled 屬性來判斷狀態。You can also determine the status by examining the IsArithmeticAbortEnabled property of the DATABASEPROPERTYEX function.

COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 }COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 }

如需詳細資訊,請參閱 ALTER DATABASE 相容性層級For more information, see ALTER DATABASE Compatibility Level.

CONCAT_NULL_YIELDS_NULL { ON | OFF }CONCAT_NULL_YIELDS_NULL { ON | OFF }
ONON
當任何一個運算元是 NULL 時,串連作業的結果都會是 NULL。The result of a concatenation operation is NULL when either operand is NULL. 例如,串連字元字串 "This is" 和 NULL 會得出 NULL 值,而不是 "This is" 值。For example, concatenating the character string "This is" and NULL returns the NULL value instead of the "This is" value.

OFFOFF
將 Null 值當做空的字元字串來處理。The null value is treated as an empty character string.

[重要] 當您建立或變更計算資料行索引或索引檢視表時,CONCAT_NULL_YIELDS_NULL 也必須設為 ON。[IMPORTANT] CONCAT_NULL_YIELDS_NULL must be set to ON when you create or make changes to indexes on computed columns or indexed views.

重要

在將來的 SQL ServerSQL Server 版本中,CONCAT_NULL_YIELDS_NULL 一律會是 ON,且明確將此選項設定為 OFF 的應用程式將會觸發錯誤。In upcoming versions of SQL ServerSQL Server, CONCAT_NULL_YIELDS_NULL will always be ON, and any applications that explicitly set the option to OFF will trigger an error. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

利用 SET 陳述式來設定的連接層級設定會覆寫 CONCAT_NULL_YIELDS_NULL 的預設資料庫設定。Connection-level settings that are set by using the SET statement override the default database setting for CONCAT_NULL_YIELDS_NULL. 根據預設,當連接到 SQL ServerSQL Server 執行個體時,ODBC 和 OLE DB 用戶端會發出連接層級的 SET 陳述式,將工作階段的 CONCAT_NULL_YIELDS_NULL 設為 ON。By default, ODBC and OLE DB clients issue a connection-level SET statement setting CONCAT_NULL_YIELDS_NULL to ON for the session when connecting to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET CONCAT_NULL_YIELDS_NULLFor more information, see SET CONCAT_NULL_YIELDS_NULL.

您可以檢查 sys.databases 目錄檢視中 is_concat_null_yields_null_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_concat_null_yields_null_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsNullConcat 屬性來判斷狀態。You can also determine the status by examining the IsNullConcat property of the DATABASEPROPERTYEX function.

QUOTED_IDENTIFIER { ON | OFF }QUOTED_IDENTIFIER { ON | OFF }
ONON
可以使用雙引號來含括分隔識別碼。Double quotation marks can be used to enclose delimited identifiers.

用雙引號來分隔的所有字串都會解譯為物件識別碼。All strings delimited by double quotation marks are interpreted as object identifiers. 附加引號的識別碼不需要遵循 Transact-SQLTransact-SQL 的識別碼規則。Quoted identifiers don't have to follow the Transact-SQLTransact-SQL rules for identifiers. 它們可以是關鍵字,也可以包括 Transact-SQLTransact-SQL 識別碼不允許的字元。They can be keywords and can include characters that aren't allowed in Transact-SQLTransact-SQL identifiers. 如果單引號 (') 是文字字串的一部分,您可以用雙引號 (") 來表示它。If a single quotation mark (') is part of the literal string, it can be represented by double quotation marks (").

OFFOFF
識別碼不能放在引號中,且必須遵循所有 Transact-SQLTransact-SQL 識別碼規則。Identifiers can't be in quotation marks and must follow all Transact-SQLTransact-SQL rules for identifiers. 文字可以用單引號或雙引號來分隔。Literals can be delimited by either single or double quotation marks.

SQL ServerSQL Server 也允許用方括號 ([ ]) 來分隔識別碼。also allows for identifiers to be delimited by square brackets ([ ]). 不論 QUOTED_IDENTIFIER 設定為何,用方括弧括住的識別項一律可以使用。Bracketed identifiers can always be used, whatever the QUOTED_IDENTIFIER setting is. 如需詳細資訊,請參閱< Database Identifiers>。For more information, see Database Identifiers.

當建立資料表時,一律會在資料表的中繼資料中,將 QUOTED IDENTIFIER 選項儲存成 ON。When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the metadata of the table. 即使建立資料表時,將選項設定為 OFF,也會儲存此選項。The option is stored even if the option is set to OFF when the table is created.

利用 SET 陳述式來設定的連接層級設定會覆寫 QUOTED_IDENTIFIER 的預設資料庫設定。Connection-level settings that are set by using the SET statement override the default database setting for QUOTED_IDENTIFIER. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將 QUOTED_IDENTIFIER 設定為 ON。ODBC and OLE DB clients issue a connection-level SET statement setting QUOTED_IDENTIFIER to ON, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET QUOTED_IDENTIFIERFor more information, see SET QUOTED_IDENTIFIER.

您可以檢查 sys.databases 目錄檢視中 is_quoted_identifier_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_quoted_identifier_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsQuotedIdentifiersEnabled 屬性來判斷狀態。You can also determine the status by examining the IsQuotedIdentifiersEnabled property of the DATABASEPROPERTYEX function.

NUMERIC_ROUNDABORT { ON | OFF }NUMERIC_ROUNDABORT { ON | OFF }
ONON
當運算式中遺失有效位數時,系統會產生錯誤。An error is generated when loss of precision occurs in an expression.

OFFOFF
遺失有效位數並不會產生錯誤訊息,且結果會四捨五入到儲存結果的資料行或變數有效位數。Loss of precision doesn't generate an error message, and the result is rounded to the precision of the column or variable storing the result.

重要

當您建立或變更計算資料行索引或索引檢視表時,NUMERIC_ROUNDABORT 必須設為 OFF。NUMERIC_ROUNDABORT must be set to OFF when you create or make changes to indexes on computed columns or indexed views.

您可以檢查 sys.databases 目錄檢視中 is_numeric_roundabort_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_numeric_roundabort_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsNumericRoundAbortEnabled 屬性來判斷狀態。You can also determine the status by examining the IsNumericRoundAbortEnabled property of the DATABASEPROPERTYEX function.

RECURSIVE_TRIGGERS { ON | OFF }RECURSIVE_TRIGGERS { ON | OFF }
ONON
允許遞迴引發 AFTER 觸發程序。Recursive firing of AFTER triggers is allowed.

OFFOFF
您可以檢查 sys.databases 目錄檢視中 is_recursive_triggers_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_recursive_triggers_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsRecursiveTriggersEnabled 屬性來判斷狀態。You can also determine the status by examining the IsRecursiveTriggersEnabled property of the DATABASEPROPERTYEX function.

注意

當 RECURSIVE_TRIGGERS 設為 OFF 時,只防止直接遞迴。Only direct recursion is prevented when RECURSIVE_TRIGGERS is set to OFF. 若要停用間接遞迴,您也必須將巢狀觸發程序伺服器選項設為 0。To disable indirect recursion, you must also set the nested triggers server option to 0.

您可以檢查 sys.databases 目錄檢視中 is_recursive_triggers_on 資料行或 DATABASEPROPERTYEX 函式 IsRecursiveTriggersEnabled 屬性來判斷這個選項的狀態。You can determine this option's status by examining the is_recursive_triggers_on column in the sys.databases catalog view or the IsRecursiveTriggersEnabled property of the DATABASEPROPERTYEX function.

<target_recovery_time_option> ::= <target_recovery_time_option> ::=
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本)Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later)

為每個資料庫指定間接檢查點的頻率。Specifies the frequency of indirect checkpoints on a per-database basis. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,新資料庫的預設值為 1 分鐘,這表示資料庫將會使用間接檢查點。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) the default value for new databases is 1 minute, which indicates database will use indirect checkpoints. 舊版的預設值為 0,這表示資料庫將使用自動檢查點,其頻率取決於伺服器執行個體的復原間隔設定。For older versions the default is 0, which indicates that the database will use automatic checkpoints, whose frequency depends on the recovery interval setting of the server instance. 對於大多數的系統,MicrosoftMicrosoft 建議使用 1 分鐘。MicrosoftMicrosoft recommends 1 minute for most systems.

TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }
target_recovery_time target_recovery_time
指定萬一發生損毀時,復原指定之資料庫的時間上限。Specifies the maximum bound on the time to recover the specified database in the event of a crash. target_recovery_time 的類型為 inttarget_recovery_time is type int.

SECONDSSECONDS
指出 target_recovery_time 應以秒數表示。Indicates that target_recovery_time is expressed as the number of seconds.

MINUTESMINUTES
指出 target_recovery_time 應以分鐘數表示。Indicates that target_recovery_time is expressed as the number of minutes.

如需間接檢查點的詳細資訊,請參閱資料庫檢查點For more information about indirect checkpoints, see Database Checkpoints.

WITH <termination> ::=WITH <termination> ::=

指定資料庫狀態轉換時,何時回復不完整的交易。Specifies when to roll back incomplete transactions when the database is transitioned from one state to another. 如果省略 termination 子句,且資料庫有任何鎖定,則 ALTER DATABASE 陳述式會無限等候。If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely if there's any lock on the database. 只能指定一個 termination 子句,它在 SET 子句之後。Only one termination clause can be specified, and it follows the SET clauses.

注意

並非所有的資料庫選項都會使用 WITH <termination> 子句。Not all database options use the WITH <termination> clause. 如需詳細資訊,請參閱本文<備註>一節中<設定選項>下的表格。For more information, see the table under "Setting options of the "Remarks" section of this article.

ROLLBACK AFTER number [SECONDS] | ROLLBACK IMMEDIATEROLLBACK AFTER number [SECONDS] | ROLLBACK IMMEDIATE

指定在指定的秒數之後回復,或是立即回復。Specifies whether to roll back after the specified number of seconds or immediately. number 的類型為 intnumber is type int.

NO_WAITNO_WAIT
指定如果要求的資料庫狀態或選項變更無法立即完成,要求將會失敗。Specifies that the request will fail if the requested database state or option change can't complete immediately. 立即完成表示不等候交易自行認可或回復。Completing immediately means not waiting for transactions to commit or roll back on their own.

設定選項Setting options

若要擷取資料庫選項的目前設定,請使用 sys.databases 目錄檢視或 DATABASEPROPERTYEXTo retrieve current settings for database options, use the sys.databases catalog view or DATABASEPROPERTYEX

設好資料庫選項之後,新設定會立即生效。After you set a database option, the new setting takes effect immediately.

您可以針對所有新建立的資料庫,變更任何一個資料庫選項的預設值。You can change the default values for any one of the database options for all newly created databases. 若要執行這項操作,請變更 model 資料庫中的適當資料庫選項。To do so, change the appropriate database option in the model database.

並非所有資料庫選項都會使用 WITH <termination> 子句,也並非所有資料庫選項都能夠結合其他選項來指定。Not all database options use the WITH <termination> clause or can be specified in combination with other options. 下表列出這些選項及其選項和終止狀態。The following table lists these options and their option and termination status.

選項類別目錄Options category 可以搭配其他選項指定Can be specified with other options 可以使用 WITH <termination> 子句Can use the WITH <termination> clause
<db_state_option><db_state_option> Yes Yes
<db_user_access_option><db_user_access_option> Yes Yes
<db_update_option><db_update_option> Yes Yes
<delayed_durability_option><delayed_durability_option> Yes Yes
<external_access_option><external_access_option> Yes No
<cursor_option><cursor_option> Yes No
<auto_option><auto_option> Yes No
<sql_option><sql_option> Yes No
<recovery_option><recovery_option> Yes No
<target_recovery_time_option><target_recovery_time_option> No Yes
<database_mirroring_option><database_mirroring_option> No No
ALLOW_SNAPSHOT_ISOLATIONALLOW_SNAPSHOT_ISOLATION No No
READ_COMMITTED_SNAPSHOTREAD_COMMITTED_SNAPSHOT No Yes
MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOTMEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT Yes Yes
<service_broker_option><service_broker_option> Yes No
DATE_CORRELATION_OPTIMIZATIONDATE_CORRELATION_OPTIMIZATION Yes Yes
<parameterization_option><parameterization_option> Yes Yes
<change_tracking_option><change_tracking_option> Yes Yes
<db_encryption_option><db_encryption_option> Yes No

設定下列其中一個選項,以清除 SQL ServerSQL Server 執行個體的計畫快取:The plan cache for the instance of SQL ServerSQL Server is cleared by setting one of the following options:

OFFLINEOFFLINE READ_WRITEREAD_WRITE
ONLINEONLINE MODIFY FILEGROUP DEFAULTMODIFY FILEGROUP DEFAULT
MODIFY_NAMEMODIFY_NAME MODIFY FILEGROUP READ_WRITEMODIFY FILEGROUP READ_WRITE
COLLATECOLLATE MODIFY FILEGROUP READ_ONLYMODIFY FILEGROUP READ_ONLY
READ_ONLYREAD_ONLY

在下列情況下也會排清計劃快取。The plan cache is also flushed in the following scenarios.

  • 資料庫將 AUTO_CLOSE 資料庫選項設定為 ON。A database has the AUTO_CLOSE database option set to ON. 當沒有任何使用者連接參考或使用資料庫時,背景工作嘗試關閉並自動關閉資料庫。When no user connection references or uses the database, the background task tries to close and shut down the database automatically.
  • 您針對有預設選項的資料庫執行幾個查詢。You run several queries against a database that has default options. 然後卸除資料庫。Then, the database is dropped.
  • 卸除來源資料庫的資料庫快照集。A database snapshot for a source database is dropped.
  • 您已成功重建資料庫的交易記錄。You successfully rebuild the transaction log for a database.
  • 您還原資料庫備份。You restore a database backup.
  • 您卸離資料庫。You detach a database.

清除計畫快取會導致重新編譯所有後續執行計畫,而且可能會導致查詢效能突然暫時下降。Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. 針對計畫快取中每個清除的快取存放區,SQL ServerSQL Server 錯誤記錄檔會包含下列資訊訊息:「由於某些資料庫維護或重新設定作業,SQL ServerSQL Server 的 '%s' 快取存放區 (計畫快取的一部分) 發生 %d 次快取存放區排清」。For each cleared cache store in the plan cache, the SQL ServerSQL Server error log contains the following informational message: " SQL ServerSQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". 只要在該時間間隔內快取發生排清,這個訊息就會每五分鐘記錄一次。This message is logged every five minutes as long as the cache is flushed within that time interval.

範例Examples

A.A. 設定資料庫的選項Setting options on a database

下列範例會設定 AdventureWorks2012AdventureWorks2012 範例資料庫的復原模式和資料頁面驗證選項。The following example sets the recovery model and data page verification options for the AdventureWorks2012AdventureWorks2012 sample database.

USE master;
GO
ALTER DATABASE [database_name]
SET RECOVERY FULL PAGE_VERIFY CHECKSUM;
GO

B.B. 將資料庫設為 READ_ONLYSetting the database to READ_ONLY

將資料庫或檔案群組的狀態改成 READ_ONLY 或 READ_WRITE 時,需要資料庫的獨佔存取權。Changing the state of a database or file group to READ_ONLY or READ_WRITE requires exclusive access to the database. 下列範例會將資料庫設成 SINGLE_USER 模式來取得獨佔存取。The following example sets the database to SINGLE_USER mode to obtain exclusive access. 之後,範例會將 AdventureWorks2012AdventureWorks2012 資料庫的狀態設成 READ_ONLY ,並將資料庫的存取權還給所有使用者。The example then sets the state of the AdventureWorks2012AdventureWorks2012 database to READ_ONLY and returns access to the database to all users.

注意

這個範例在第一個 WITH ROLLBACK IMMEDIATE 陳述式中,使用終止選項 ALTER DATABASEThis example uses the termination option WITH ROLLBACK IMMEDIATE in the first ALTER DATABASE statement. 所有未完成的交易都會回復,而且 AdventureWorks2012AdventureWorks2012 資料庫的任何其他連接都會立即中斷。All incomplete transactions will be rolled back and any other connections to the AdventureWorks2012AdventureWorks2012 database will be immediately disconnected.

USE master;
GO
ALTER DATABASE [database_name]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [database_name]
SET READ_ONLY
GO
ALTER DATABASE [database_name]
SET MULTI_USER;
GO

C.C. 啟用資料庫的快照集隔離Enabling snapshot isolation on a database

下列範例會啟用 AdventureWorks2012AdventureWorks2012 資料庫的快照集隔離架構選項。The following example enables the snapshot isolation framework option for the AdventureWorks2012AdventureWorks2012 database.

USE [database_name];
USE master;
GO
ALTER DATABASE [database_name]
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Check the state of the snapshot_isolation_framework
-- in the database.
SELECT name, snapshot_isolation_state,
    snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'[database_name]';
GO

結果集顯示啟用快照集隔離架構。The result set shows that the snapshot isolation framework is enabled.

NAMEname snapshot_isolation_statesnapshot_isolation_state descriptiondescription
[database_name][database_name] 11 ONON

D.D. 啟用、修改及停用變更追蹤Enabling, modifying, and disabling change tracking

下列範例會啟用 AdventureWorks2012AdventureWorks2012 資料庫的變更追蹤,並將保留週期設定為 2 天。The following example enables change tracking for the AdventureWorks2012AdventureWorks2012 database and sets the retention period to 2 days.

ALTER DATABASE [database_name]
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP = ON, CHANGE_RETENTION = 2 DAYS);

下列範例會示範如何將保留週期變更為 3 天。The following example shows how to change the retention period to 3 days.

ALTER DATABASE [database_name]
SET CHANGE_TRACKING (CHANGE_RETENTION = 3 DAYS);

下列範例會示範如何停用 AdventureWorks2012AdventureWorks2012 資料庫的變更追蹤。The following example shows how to disable change tracking for the AdventureWorks2012AdventureWorks2012 database.

ALTER DATABASE [database_name]
SET CHANGE_TRACKING = OFF;

E.E. 啟用查詢存放區Enabling the Query Store

適用於SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本)Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later)

下列範例會啟用查詢存放區並設定其參數。The following example enables the Query Store and configures its parameters.

ALTER DATABASE [database_name]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1024,
      INTERVAL_LENGTH_MINUTES = 60
    );

F.F. 啟用包含等候統計資料的查詢存放區Enabling the Query Store with wait statistics

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x))

下列範例會啟用查詢存放區並設定其參數。The following example enables the Query Store and configures its parameters.

ALTER DATABASE [database_name]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1024,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
    );

G.G. 啟用包含自訂擷取原則選項的查詢存放區Enabling the Query Store with custom capture policy options

適用於SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x))

下列範例會啟用查詢存放區並設定其參數。The following example enables the Query Store and configures its parameters.

ALTER DATABASE [database_name]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1024,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

另請參閱See Also

SQL ServerSQL Server * SQL Database
單一資料庫/彈性集區 *
 
* SQL Database
single database/elastic pool *
 
SQL Database
受控執行個體
SQL Database
managed instance
SQL 資料
倉儲
SQL Data
Warehouse

 

Azure SQL Database 單一資料庫/彈性集區Azure SQL Database single database/elastic pool

相容性層級為 SET 選項,但在 ALTER DATABASE 相容性層級中描述。Compatibility levels are SET options but are described in ALTER DATABASE Compatibility Level.

注意

目前工作階段的許多資料庫 SET 選項都可以使用 SET 陳述式來設定,而且通常是在應用程式連線時由其加以設定。Many database set options can be configured for the current session by using SET Statements and are often configured by applications when they connect. 工作階段層級 SET 選項會覆寫 ALTER DATABASE SET 值。Session-level set options override the ALTER DATABASE SET values. 下列章節所述的資料庫選項皆為未明確提供其他 SET 選項值,因而可針對工作階段進行設定的值。The database options described in the following sections are values that can be set for sessions that don't explicitly provide other set option values.

語法Syntax

ALTER DATABASE { database_name | Current }
SET
{
    <option_spec> [ ,...n ] [ WITH <termination> ]
}
;

<option_spec> ::=
{
    <auto_option>
  | <automatic_tuning_option>
  | <change_tracking_option>
  | <cursor_option>
  | <db_encryption_option>
  | <db_update_option>
  | <db_user_access_option>
  | <delayed_durability_option>
  | <parameterization_option>
  | <query_store_options>
  | <snapshot_option>
  | <sql_option>
  | <target_recovery_time_option>
  | <termination>
  | <temporal_history_retention>
}
;

<auto_option> ::=
{
    AUTO_CREATE_STATISTICS { OFF | ON [ ( INCREMENTAL = { ON | OFF } ) ] }
  | AUTO_SHRINK { ON | OFF }
  | AUTO_UPDATE_STATISTICS { ON | OFF }
  | AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}

<automatic_tuning_option> ::=
{
    AUTOMATIC_TUNING = { AUTO | INHERIT | CUSTOM }
  | AUTOMATIC_TUNING ( CREATE_INDEX = { DEFAULT | ON | OFF } )
  | AUTOMATIC_TUNING ( DROP_INDEX = { DEFAULT | ON | OFF } )
  | AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = { DEFAULT | ON | OFF } )
}

<change_tracking_option> ::=
{
    CHANGE_TRACKING
    {
        = OFF
      | = ON [ ( <change_tracking_option_list > [,...n] ) ]
      | ( <change_tracking_option_list> [,...n] )
    }
}

<change_tracking_option_list> ::=
   {
       AUTO_CLEANUP = { ON | OFF }
     | CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }
   }

<cursor_option> ::=
{
    CURSOR_CLOSE_ON_COMMIT { ON | OFF }
}

<db_encryption_option> ::=
  ENCRYPTION { ON | OFF }

<db_update_option> ::=
  { READ_ONLY | READ_WRITE }

<db_user_access_option> ::=
  { RESTRICTED_USER | MULTI_USER }

<delayed_durability_option> ::= DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

<parameterization_option> ::=
  PARAMETERIZATION { SIMPLE | FORCED }

<query_store_options> ::=
{
  QUERY_STORE
  {
      = OFF
    | = ON [ ( <query_store_option_list> [,... n] ) ]
    | ( < query_store_option_list> [,... n] )
    | CLEAR [ ALL ]
  }
}

<query_store_option_list> ::=
{
  OPERATION_MODE = { READ_WRITE | READ_ONLY }
  | CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = number )
  | DATA_FLUSH_INTERVAL_SECONDS = number
  | MAX_STORAGE_SIZE_MB = number
  | INTERVAL_LENGTH_MINUTES = number
  | SIZE_BASED_CLEANUP_MODE = [ AUTO | OFF ]
  | QUERY_CAPTURE_MODE = [ ALL | AUTO | NONE ]
  | MAX_PLANS_PER_QUERY = number
}

<snapshot_option> ::=
{
    ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
  | READ_COMMITTED_SNAPSHOT {ON | OFF }
  | MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT {ON | OFF }
}
<sql_option> ::=
{
    ANSI_NULL_DEFAULT { ON | OFF }
  | ANSI_NULLS { ON | OFF }
  | ANSI_PADDING { ON | OFF }
  | ANSI_WARNINGS { ON | OFF }
  | ARITHABORT { ON | OFF }
  | COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 }
  | CONCAT_NULL_YIELDS_NULL { ON | OFF }
  | NUMERIC_ROUNDABORT { ON | OFF }
  | QUOTED_IDENTIFIER { ON | OFF }
  | RECURSIVE_TRIGGERS { ON | OFF }
}

<termination>::=
{
    ROLLBACK AFTER integer [ SECONDS ]
  | ROLLBACK IMMEDIATE
  | NO_WAIT
}

<temporal_history_retention>::=TEMPORAL_HISTORY_RETENTION { ON | OFF }

引數Arguments

database_name database_name
這是要修改之資料庫的名稱。Is the name of the database to be modified.

CURRENTCURRENT
CURRENT 會在目前資料庫中執行動作。CURRENT runs the action in the current database. 所有選項在所有內容中不支援 CURRENTCURRENT isn't supported for all options in all contexts. 如果 CURRENT 失敗,請提供資料庫名稱。If CURRENT fails, provide the database name.

<auto_option> ::=<auto_option> ::=

控制自動選項。Controls automatic options.

AUTO_CREATE_STATISTICS { ON | OFF }AUTO_CREATE_STATISTICS { ON | OFF }
ONON
查詢最佳化工具會視需要針對查詢述詞中的單一資料行建立統計資料,以便改善查詢計劃和查詢效能。Query Optimizer creates statistics on single columns in query predicates, as necessary, to improve query plans and query performance. 這些單一資料行統計資料是在查詢最佳化工具編譯查詢時所建立。These single-column statistics are created when Query Optimizer compiles queries. 它只會針對尚未成為現有統計資料物件之第一個資料行的資料行建立單一資料行統計資料。The single-column statistics are created only on columns that are not already the first column of an existing statistics object.

預設值是 ON。The default is ON. 我們建議您針對大部分資料庫使用預設設定。We recommend that you use the default setting for most databases.

OFFOFF
查詢最佳化工具不會在編譯查詢時針對查詢述詞中的單一資料行建立統計資料。Query Optimizer doesn't create statistics on single columns in query predicates when it's compiling queries. 將這個選項設定為 OFF 可能會導致次佳查詢計劃並降低查詢效能。Setting this option to OFF can cause suboptimal query plans and degraded query performance.

您可以檢查 sys.databases 目錄檢視中 is_auto_create_stats_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_auto_create_stats_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAutoCreateStatistics 屬性來判斷狀態。You can also determine the status by examining the IsAutoCreateStatistics property of the DATABASEPROPERTYEX function.

如需詳細資訊,請參閱統計資料中的<統計資料選項>一節。For more information, see the "Statistics options" section in Statistics.

INCREMENTAL = ON | OFF INCREMENTAL = ON | OFF
將 AUTO_CREATE_STATISTICS 設定為 ON,並將 INCREMENTAL 設定為 ON。Set AUTO_CREATE_STATISTICS to ON, and set INCREMENTAL to ON. 此設定會在每次支援累加統計資料時,以累加方式建立自動建立的統計資料。This setting creates automatically created stats as incremental whenever incremental stats are supported. 預設值是 OFF。The default value is OFF. 如需詳細資訊,請參閱 CREATE STATISTICSFor more information, see CREATE STATISTICS.

AUTO_SHRINK { ON | OFF }AUTO_SHRINK { ON | OFF }
ONON
資料庫檔案是定期壓縮的候選項。The database files are candidates for periodic shrinking.

資料檔案和記錄檔都可以自動壓縮。Both data file and log files can be automatically shrunk. 只有在您將資料庫設定為 SIMPLE 復原模式或備份記錄時,AUTO_SHRINK 才會縮減交易記錄的大小。AUTO_SHRINK reduces the size of the transaction log only if you set the database to SIMPLE recovery model or if you back up the log. 當設定為 OFF 時,便不會在定期檢查未用空間時,自動壓縮資料庫檔案。When set to OFF, the database files aren't automatically shrunk during periodic checks for unused space.

當超出 25% 的檔案包含未用空間時,AUTO_SHRINK 選項便會壓縮檔案。The AUTO_SHRINK option causes files to be shrunk when more than 25 percent of the file contains unused space. 此選項會使檔案壓縮成兩種大小之一。The option causes the file to shrink to one of two sizes. 它會壓縮成兩者中的較大者:It shrinks to whichever is larger:

  • 25% 的檔案是未使用空間的大小The size where 25 percent of the file is unused space
  • 檔案建立時的大小The size of the file when it was created

您無法壓縮唯讀資料庫。You can't shrink a read-only database.

OFFOFF
在定期檢查未用空間時,不自動壓縮資料庫檔案。The database files are not automatically shrunk during periodic checks for unused space.

您可以檢查 sys.databases 目錄檢視中 is_auto_shrink_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_auto_shrink_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAutoShrink 屬性來判斷狀態。You can also determine the status by examining the IsAutoShrink property of the DATABASEPROPERTYEX function.

注意

自主資料庫無法使用 AUTO_SHRINK 選項。The AUTO_SHRINK option isn't available in a contained database.

AUTO_UPDATE_STATISTICS { ON | OFF }AUTO_UPDATE_STATISTICS { ON | OFF }
ONON
指定當查詢使用統計資料且它們可能已過期時,查詢最佳化工具就會更新這些統計資料。Specifies that Query Optimizer updates statistics when they're used by a query and when they might be out-of-date. 當插入、更新、刪除或合併作業變更資料表或索引檢視表中的資料分佈之後,統計資料就會變成過期。Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. 查詢最佳化工具會計算自從上次更新統計資料以來資料修改的次數,並將修改次數與某個閾值比較,藉以判斷統計資料是否可能已經過期。Query Optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. 此臨界值是以資料表或索引檢視表中的資料列數目為基礎。The threshold is based on the number of rows in the table or indexed view.

查詢最佳化工具會在編譯查詢及執行快取查詢計劃之前,檢查是否有過期的統計資料。Query Optimizer checks for out-of-date statistics before it compiles a query and runs a cached query plan. 查詢最佳化工具會在查詢述詞中使用資料行、資料表和索引檢視表,來判斷哪些統計資料可能已過期。Query Optimizer uses the columns, tables, and indexed views in the query predicate to determine which statistics might be out-of-date. 查詢最佳化工具會在編譯查詢之前判斷這項資訊。Query Optimizer determines this information before it compiles a query. 在執行快取查詢計劃之前,Database EngineDatabase Engine 會確認查詢計劃是否參考最新的統計資料。Before running a cached query plan, the Database EngineDatabase Engine verifies that the query plan references up-to-date statistics.

AUTO_UPDATE_STATISTICS 選項會套用至針對索引所建立的統計資料、查詢述詞中的單一資料行,以及使用 CREATE STATISTICS 陳述式所建立的統計資料。The AUTO_UPDATE_STATISTICS option applies to statistics created for indexes, single-columns in query predicates, and statistics that are created by using the CREATE STATISTICS statement. 此外,這個選項也會套用至篩選的統計資料。This option also applies to filtered statistics.

預設值是 ON。The default is ON. 我們建議您針對大部分資料庫使用預設設定。We recommend that you use the default setting for most databases.

您可以使用 AUTO_UPDATE_STATISTICS_ASYNC 選項來指定要以同步或非同步方式更新統計資料。Use the AUTO_UPDATE_STATISTICS_ASYNC option to specify whether the statistics are updated synchronously or asynchronously.

OFFOFF
指定當查詢使用統計資料時,查詢最佳化工具不會更新這些統計資料。Specifies that Query Optimizer doesn't update statistics when they're used by a query. 當統計資料可能已過期時,查詢最佳化工具也不會更新這些統計資料。Query Optimizer also doesn't update statistics when they might be out-of-date. 將這個選項設定為 OFF 可能會導致次佳查詢計劃並降低查詢效能。Setting this option to OFF can cause suboptimal query plans and degraded query performance.

您可以檢查 sys.databases 目錄檢視中 is_auto_update_stats_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_auto_update_stats_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAutoUpdateStatistics 屬性來判斷狀態。You can also determine the status by examining the IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.

如需詳細資訊,請參閱統計資料中的<統計資料選項>一節。For more information, see the "Statistics options" section in Statistics.

AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
ONON
指定 AUTO_UPDATE_STATISTICS 選項的統計資料更新是非同步的。Specifies that statistics updates for the AUTO_UPDATE_STATISTICS option are asynchronous. 查詢最佳化工具在編譯查詢之前,不會等候統計資料更新完成。Query Optimizer doesn't wait for statistics updates to complete before it compiles queries.

除非 AUTO_UPDATE_STATISTICS 設為 ON,否則將這個選項設為 ON 沒有作用。Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is set to ON.

預設會將 AUTO_UPDATE_STATISTICS_ASYNC 選項設為 OFF,且查詢最佳化工具會同步更新統計資料。By default, the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF, and Query Optimizer updates statistics synchronously.

OFFOFF
指定 AUTO_UPDATE_STATISTICS 選項的統計資料更新是同步的。Specifies that statistics updates for the AUTO_UPDATE_STATISTICS option are synchronous. 查詢最佳化工具在編譯查詢之前,會先等候統計資料更新完成。Query Optimizer waits for statistics updates to complete before it compiles queries.

除非 AUTO_UPDATE_STATISTICS 設為 ON,否則將這個選項設為 OFF 沒有作用。Setting this option to OFF has no effect unless AUTO_UPDATE_STATISTICS is set to ON.

您可以檢查 sys.databases 目錄檢視中 is_auto_update_stats_async_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_auto_update_stats_async_on column in the sys.databases catalog view.

如需描述何時應使用同步或非同步統計資料更新的詳細資訊,請參閱統計資料中的<統計資料選項>一節。For more information that describes when to use synchronous or asynchronous statistics updates, see the "Statistics options" section in Statistics.

<automatic_tuning_option> ::= <automatic_tuning_option> ::=
適用於SQL Server 2017 (14.x)SQL Server 2017 (14.x)Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x)

控制自動調整的自動選項。Controls automatic options for Automatic tuning.

AUTOMATIC_TUNING = { AUTO | INHERIT | CUSTOM }AUTOMATIC_TUNING = { AUTO | INHERIT | CUSTOM }
AUTOAUTO
將自動調整值設定為 AUTO,會套用 Azure 設定預設值以進行自動調整。Setting the Automatic tuning value to AUTO will apply Azure configuration defaults for Automatic tuning.

INHERITINHERIT
使用 INHERIT 值會從父伺服器繼承預設設定。Using the INHERIT value inherits the default configuration from the parent server. 如果您想在父伺服器自訂自動調整設定,並讓這種伺服器上的所有資料庫都 INHERIT 這些自訂設定,這會特別有用。This is especially useful if you would like to customize Automatic tuning configuration on a parent server, and have all the databases on such server INHERIT these custom settings. 請注意,為了讓繼承有效,FORCE_LAST_GOOD_PLAN、CREATE_INDEX 及 DROP_INDEX 這三個個別的調整選項就必須在伺服器上設為 DEFAULT。Please note that in order for the inheritance to work, the three individual tuning options FORCE_LAST_GOOD_PLAN, CREATE_INDEX and DROP_INDEX need to be set to DEFAULT on databases.

CUSTOMCUSTOM
使用 CUSTOM 值,您就必須手動自訂設定資料庫上可用的各個自動調整選項。Using the CUSTOM value, you'll need to custom-configure each of the Automatic Tuning options available on databases.

啟用或停用自動調整的自動索引管理 CREATE_INDEX 選項。Enables or disables automatic index management CREATE_INDEX option of Automatic tuning.

CREATE_INDEX = { DEFAULT | ON | OFF }CREATE_INDEX = { DEFAULT | ON | OFF }
DEFAULTDEFAULT
從伺服器繼承預設設定。Inherits default settings from the server. 在這種情況下,就會在伺服器層級定義啟用或停用個別自動調整功能的選項。In this case, options of enabling or disabling individual Automatic tuning features are defined at the server level.

ONON
啟用時,會在資料庫自動產生缺少的索引。When enabled, missing indexes are automatically generated on a database. 在建立索引之後,會驗證工作負載效能的增量。Following the index creation, gains to the performance of the workload are verified. 當這類建立的索引不再對工作負載效能有助益時,會自動還原。When such created index no longer provides benefits to the workload performance, it's automatically reverted. 自動建立的索引會加上旗標,表示是系統產生的索引。Indexes automatically created are flagged as a system generated indexed.

OFFOFF
不會自動產生資料庫上遺漏的索引。Doesn't automatically generate missing indexes on the database.

啟用或停用自動調整的自動索引管理 DROP_INDEX 選項。Enables or disables automatic index management DROP_INDEX option of Automatic Tuning.

DROP_INDEX = { DEFAULT | ON | OFF }DROP_INDEX = { DEFAULT | ON | OFF }
DEFAULTDEFAULT
從伺服器繼承預設設定。Inherits default settings from the server. 在這種情況下,就會在伺服器層級定義啟用或停用個別自動調整功能的選項。In this case, options of enabling or disabling individual Automatic tuning features are defined at the server level.

ONON
自動卸除重複索引或不再對效能工作負載有用的索引。Automatically drops duplicate or no longer useful indexes to the performance workload.

OFFOFF
不會自動卸除資料庫上遺漏的索引。Doesn't automatically drop missing indexes on the database.

啟用或停用自動調整的自動計劃修正 FORCE_LAST_GOOD_PLAN 選項。Enables or disables automatic plan correction FORCE_LAST_GOOD_PLAN option of Automatic tuning.

FORCE_LAST_GOOD_PLAN = { DEFAULT | ON | OFF }FORCE_LAST_GOOD_PLAN = { DEFAULT | ON | OFF }
DEFAULTDEFAULT
從伺服器繼承預設設定。Inherits default settings from the server. 在這種情況下,就會在伺服器層級定義啟用或停用個別自動調整功能的選項。In this case, options of enabling or disabling individual Automatic tuning features are defined at the server level.

ONON
Database EngineDatabase Engine 會對新查詢計劃將造成效能衰退的 Transact-SQLTransact-SQL 查詢,自動強制執行最後一個已知的良好計劃。The Database EngineDatabase Engine automatically forces the last known good plan on the Transact-SQLTransact-SQL queries where new query plan causes performance regressions. Database EngineDatabase Engine 會使用強制方案持續監視 Transact-SQLTransact-SQL 查詢的查詢效能。The Database EngineDatabase Engine continuously monitors query performance of the Transact-SQLTransact-SQL query with the forced plan. 如果效能有所提升,Database EngineDatabase Engine 會繼續使用最後一個已知的良好計劃。If there are performance gains, the Database EngineDatabase Engine will keep using last known good plan. 如果未偵測到效能提升,Database EngineDatabase Engine 會產生新的查詢計劃。If performance gains are not detected, the Database EngineDatabase Engine will produce a new query plan. 如果未啟用查詢存放區,或其不在「讀寫」 模式中,陳述式便會失敗。The statement will fail if the Query Store isn't enabled or if it's not in Read-Write mode.

OFFOFF
Database EngineDatabase Engine 會在 sys.dm_db_tuning_recommendations 檢視中報告查詢計劃變更所造成可能的查詢效能衰退。The Database EngineDatabase Engine reports potential query performance regressions caused by query plan changes in sys.dm_db_tuning_recommendations view. 不過,不會自動套用這些建議。However, these recommendations are not automatically applied. 使用者可以套用檢視中顯示的 Transact-SQLTransact-SQL 指令碼,來監視使用中建議並修正已識別的問題。Users can monitor active recommendations and fix identified problems by applying Transact-SQLTransact-SQL scripts that are shown in the view. 這是預設值。This is the default value.

<change_tracking_option> ::=<change_tracking_option> ::=

控制變更追蹤選項。Controls change tracking options. 您可以啟用變更追蹤、設定選項、變更選項,以及停用變更追蹤。You can enable change tracking, set options, change options, and disable change tracking. 如需範例,請參閱本文稍後的<範例>一節。For examples, see the "Examples" section later in this article.

ONON
啟用資料庫的變更追蹤。Enables change tracking for the database. 當您啟用變更追蹤時,也可以設定 AUTO CLEANUP 和 CHANGE RETENTION 選項。When you enable change tracking, you can also set the AUTO CLEANUP and CHANGE RETENTION options.

AUTO_CLEANUP = { ON | OFF }AUTO_CLEANUP = { ON | OFF }
ONON
在經過指定的保留週期後,將會自動移除變更追蹤資訊。Change tracking information is automatically removed after the specified retention period.

OFFOFF
不會從資料庫中移除變更追蹤資料。Change tracking data isn't removed from the database.

CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }
指定在資料庫中保存變更追蹤資訊的最小週期。Specifies the minimum period for keeping change tracking information in the database. 只有當 AUTO_CLEANUP 值為 ON 時,才會移除資料。Data is removed only when the AUTO_CLEANUP value is ON.

retention_period 是一個整數,它會指定保留週期的數值元件。retention_period is an integer that specifies the numerical component of the retention period.

預設保留期間為 2 天The default retention period is 2 days. 最小保留週期是 1 分鐘。The minimum retention period is 1 minute. 預設保留期類型為 DAYSThe default retention type is DAYS.

OFFOFF
停用資料庫的變更追蹤。Disables change tracking for the database. 在您停用資料庫的變更追蹤之前,請先在所有資料表上停用變更追蹤。Disable change tracking on all tables before you disable change tracking off the database.

<cursor_option> ::=<cursor_option> ::=

控制資料指標選項。Controls cursor options.

CURSOR_CLOSE_ON_COMMIT { ON | OFF }CURSOR_CLOSE_ON_COMMIT { ON | OFF }
ONON
當您認可或回復交易時,將會關閉任何開啟的資料指標。Any cursors open when you commit or roll back a transaction are closed.

OFFOFF
當認可交易時,資料指標維持開啟狀態;回復交易會關閉任何資料指標,但定義為 INSENSITIVE 或 STATIC 的資料指標除外。Cursors remain open when a transaction is committed; rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.

利用 SET 陳述式來設定的連接層級設定會覆寫 CURSOR_CLOSE_ON_COMMIT 的預設資料庫設定。Connection-level settings that are set by using the SET statement override the default database setting for CURSOR_CLOSE_ON_COMMIT. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將工作階段的 CURSOR_CLOSE_ON_COMMIT 設定為 OFF。ODBC and OLE DB clients issue a connection-level SET statement setting CURSOR_CLOSE_ON_COMMIT to OFF for the session, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET CURSOR_CLOSE_ON_COMMITFor more information, see SET CURSOR_CLOSE_ON_COMMIT.

您可以檢查 sys.databases 目錄檢視中 is_cursor_close_on_commit_on 資料行或 DATABASEPROPERTYEX 函式的 IsCloseCursorsOnCommitEnabled 屬性來判斷這個選項的狀態。You can determine this option's status by examining the is_cursor_close_on_commit_on column in the sys.databases catalog view or the IsCloseCursorsOnCommitEnabled property of the DATABASEPROPERTYEX function. 只有在中斷連接時,才會隱含地取消配置資料指標。The cursor is implicitly deallocated only at disconnect. 如需詳細資訊,請參閱 DECLARE CURSORFor more information, see DECLARE CURSOR.

<db_encryption_option> ::=<db_encryption_option> ::=

控制資料庫加密狀態。Controls the database encryption state.

ENCRYPTION { ON | OFF }ENCRYPTION { ON | OFF }
設定資料庫要加密 (ON) 或是不要加密 (OFF)。Sets the database to be encrypted (ON) or not encrypted (OFF). 如需資料庫加密的詳細資訊,請參閱透明資料加密Azure SQL Database 的透明資料加密For more information about database encryption, see Transparent Data Encryption, and Transparent Data Encryption with Azure SQL Database.

在資料庫層級啟用加密時,所有的檔案群組都會加密。When encryption is enabled at the database level, all file groups will be encrypted. 任何新檔案群組都會繼承加密的屬性。Any new file groups will inherit the encrypted property. 如果資料庫中有任何檔案群組設定為 READ ONLY,則資料庫加密作業將會失敗。If any file groups in the database are set to READ ONLY, the database encryption operation will fail.

您可以使用 sys.dm_database_encryption_keys 動態管理檢視來查看資料庫的加密狀態。You can see the encryption state of the database by using the sys.dm_database_encryption_keys dynamic management view.

<db_update_option> ::=<db_update_option> ::=

控制是否允許更新資料庫。Controls whether updates are allowed on the database.

READ_ONLYREAD_ONLY
使用者可以從資料庫中讀取資料,但無法加以修改。Users can read data from the database but not modify it.

注意

如果要提高查詢的效能,請先更新統計資料,再將資料庫設為 READ_ONLY。To improve query performance, update statistics before setting a database to READ_ONLY. 如果資料庫設為 READ_ONLY 之後,還需要其他的統計資料,Database EngineDatabase Engine 將會在 tempdb 中建立統計資料。If additional statistics are needed after a database is set to READ_ONLY, the Database EngineDatabase Engine will create statistics in tempdb. 如需唯讀資料庫統計資料的詳細資訊,請參閱統計資料For more information about statistics for a read-only database, see Statistics.

READ_WRITEREAD_WRITE
資料庫可以執行讀寫作業。The database is available for read and write operations.

若要變更這個狀態,您必須具有資料庫的獨佔存取權。To change this state, you must have exclusive access to the database. 如需詳細資訊,請參閱 SINGLE_USER 子句。For more information, see the SINGLE_USER clause.

注意

SQL DatabaseSQL Database同盟資料庫上,SET { READ_ONLY | READ_WRITE } 會停用。On SQL DatabaseSQL Database federated databases, SET { READ_ONLY | READ_WRITE } is disabled.

<db_user_access_option> ::=<db_user_access_option> ::=

控制使用者對資料庫的存取權。Controls user access to the database.

RESTRICTED_USERRESTRICTED_USER
只允許 db_owner 固定資料庫角色與 dbcreatorsysadmin 固定伺服器角色的成員連線到資料庫,但不限制其數目。Allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but doesn't limit their number. 在 ALTER DATABASE 陳述式的 termination 子句所指定的時間範圍中,會中斷資料庫的所有連接。All connections to the database are disconnected in the timeframe specified by the termination clause of the ALTER DATABASE statement. 在資料庫進入 RESTRICTED_USER 狀態之後,不合格使用者的連接嘗試都會遭到拒絕。After the database has transitioned to the RESTRICTED_USER state, connection attempts by unqualified users are refused. RESTRICTED_USER 無法使用 SQL Database 受控執行個體來修改。RESTRICTED_USER can't be modified with SQL Database managed instance.

MULTI_USERMULTI_USER
允許所有具備適當權限來連接資料庫的使用者。All users that have the appropriate permissions to connect to the database are allowed.

您可以檢查 sys.databases 目錄檢視中 user_access 資料行或 DATABASEPROPERTYEX 函式 UserAccess 屬性來判斷這個選項的狀態。You can determine this option's status by examining the user_access column in the sys.databases catalog view or the UserAccess property of the DATABASEPROPERTYEX function.

<delayed_durability_option> ::=<delayed_durability_option> ::=

控制交易是否認可完全持久或延遲的持久。Controls whether transactions commit fully durable or delayed durable.

DISABLEDDISABLED
接在 SET DISABLED 後面的所有交易都是完全持久。All transactions following SET DISABLED are fully durable. 在不可部分完成的區塊或 Commit 陳述式中設定的任何持久性選項都會被忽略。Any durability options set in an atomic block or commit statement are ignored.

ALLOWEDALLOWED
接在 SET ALLOWED 後面的所有交易都是完全持久或延遲持久,視不可部分完成的區塊或 Commit 陳述式中所設定持久性選項而定。All transactions following SET ALLOWED are either fully durable or delayed durable, depending upon the durability option set in the atomic block or commit statement.

FORCEDFORCED
接在 SET FORCED 後面的所有交易都是延遲持久。All transactions following SET FORCED are delayed durable. 在不可部分完成的區塊或 Commit 陳述式中設定的任何持久性選項都會被忽略。Any durability options set in an atomic block or commit statement are ignored.

<PARAMETERIZATION_option> ::=<PARAMETERIZATION_option> ::=

控制參數化選項。Controls the parameterization option.

PARAMETERIZATION { SIMPLE | FORCED }PARAMETERIZATION { SIMPLE | FORCED }
SIMPLESIMPLE
根據資料庫的預設行為,將查詢參數化。Queries are parameterized based on the default behavior of the database.

FORCEDFORCED
SQL ServerSQL Server 會將資料庫中的所有查詢參數化。parameterizes all queries in the database.

您可以檢查 sys.databases 目錄檢視中 is_parameterization_forced 資料行來判斷此選項的目前設定。The current setting of this option can be determined by examining the is_parameterization_forced column in the sys.databases catalog view.

<query_store_options> ::=<query_store_options> ::=

ON | OFF | CLEAR [ ALL ]ON | OFF | CLEAR [ ALL ]
控制是否在此資料庫中啟用查詢存放區,且控制查詢存放區內容的移除。Controls whether the Query Store is enabled in this database, and also controls removing the contents of the Query Store.

ONON
啟用查詢存放區。Enables the Query Store.

OFFOFF
停用查詢存放區。Disables the Query Store. 這是預設值。This is the default value.

CLEARCLEAR
移除查詢存放區的內容。Remove the contents of the Query Store.

OPERATION_MODEOPERATION_MODE
描述查詢存放區的作業模式。Describes the operation mode of the Query Store. 有效值為 READ_ONLY 和 READ_WRITE。Valid values are READ_ONLY and READ_WRITE. 在 READ_WRITE 模式中,查詢存放區會收集並保存查詢計劃和執行階段執行統計資料資訊。In READ_WRITE mode, the Query Store collects and persists query plan and runtime execution statistics information. 在 READ_ONLY 模式中,可以從查詢存放區讀取資訊,但不會新增資訊。In READ_ONLY mode, information can be read from the Query Store, but new information isn't added. 如果查詢存放區所配置的最大空間已用盡,則查詢存放區會將作業模式變更為 READ_ONLY。If the maximum allocated space of the Query Store has been exhausted, the Query Store will change is operation mode to READ_ONLY.

CLEANUP_POLICYCLEANUP_POLICY
描述查詢存放區的資料保留原則。Describes the data retention policy of the Query Store. STALE_QUERY_THRESHOLD_DAYS 會決定在查詢存放區中保留查詢資訊的天數。STALE_QUERY_THRESHOLD_DAYS determines the number of days for which the information for a query is kept in the Query Store. STALE_QUERY_THRESHOLD_DAYS 的類型為 bigintSTALE_QUERY_THRESHOLD_DAYS is type bigint.

DATA_FLUSH_INTERVAL_SECONDSDATA_FLUSH_INTERVAL_SECONDS
決定將寫入查詢存放區之資料保存到磁碟的頻率。Determines the frequency at which data written to the Query Store is persisted to disk. 為了獲得最佳效能,查詢存放區所收集的資料會以非同步方式寫入磁碟。To optimize for performance, data collected by the Query Store is asynchronously written to the disk. 此非同步傳輸發生的頻率是使用 DATA_FLUSH_INTERVAL_SECONDS 引數所設定。The frequency at which this asynchronous transfer occurs is configured by using the DATA_FLUSH_INTERVAL_SECONDS argument. DATA_FLUSH_INTERVAL_SECONDS 的類型為 bigintDATA_FLUSH_INTERVAL_SECONDS is type bigint.

MAX_STORAGE_SIZE_MBMAX_STORAGE_SIZE_MB
決定配置給查詢存放區的空間。Determines the space allocated to the Query Store. MAX_STORAGE_SIZE_MB 的類型為 bigintMAX_STORAGE_SIZE_MB is type bigint.

INTERVAL_LENGTH_MINUTESINTERVAL_LENGTH_MINUTES
決定執行階段執行統計資料彙總至查詢存放區的時間間隔。Determines the time interval at which runtime execution statistics data is aggregated into the Query Store. 若要將空間使用量最佳化,在執行階段統計資料存放區中的執行階段執行統計資料會透過固定的時段彙總。To optimize for space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed time window. 這個固定的時段是使用 INTERVAL_LENGTH_MINUTES 引數所設定。This fixed time window is configured by using the INTERVAL_LENGTH_MINUTES argument. INTERVAL_LENGTH_MINUTES 的類型為 bigintINTERVAL_LENGTH_MINUTES is type bigint.

SIZE_BASED_CLEANUP_MODESIZE_BASED_CLEANUP_MODE
控制當總資料量接近大小上限時,是否將自動啟用清除。Controls whether cleanup will be automatically activated when the total amount of data gets close to maximum size.

OFFOFF
不會自動啟用以大小為依據的清除。Size-based cleanup won't be automatically activated.

AUTOAUTO
當磁碟上的大小達到 90% 的 max_storage_size_mb 時,就會自動啟用以大小為依據的清除。Size-based cleanup will be automatically activated when size on disk reaches 90% of max_storage_size_mb. 以大小為依據之清除會先移除成本最高和最舊的查詢。Size-based cleanup removes the least expensive and oldest queries first. 它會在達到 max_storage_size_mb 的大約 80% 處停止。It stops at approximately 80% of max_storage_size_mb. 這是預設組態值。This is the default configuration value.

SIZE_BASED_CLEANUP_MODE 的類型為 nvarcharSIZE_BASED_CLEANUP_MODE is type nvarchar.

QUERY_CAPTURE_MODEQUERY_CAPTURE_MODE
指定目前使用中的查詢擷取模式:Designates the currently active query capture mode:

ALLALL
擷取所有查詢。All queries are captured.

AUTOAUTO
根據執行計數和資源耗用量擷取相關的查詢。Capture relevant queries based on execution count and resource consumption. 這是 Azure SQL DatabaseAzure SQL Database 的預設組態值。This is the default configuration value for Azure SQL DatabaseAzure SQL Database.

NONE
停止擷取新的查詢。Stop capturing new queries. 查詢存放區將會繼續收集已擷取查詢的編譯和執行階段統計資料。The Query Store will continue to collect compile and runtime statistics for queries that were captured already. 因為您可能會錯過擷取重要查詢,請小心使用此設定。Use this configuration with caution since you may miss capturing important queries.

QUERY_CAPTURE_MODE 的類型為 nvarcharQUERY_CAPTURE_MODE is type nvarchar.

max_plans_per_queryMAX_PLANS_PER_QUERY
表示維護每個查詢計劃的大數目的整數。An integer representing the maximum number of plans maintained for each query. 預設值為 200。Default is 200.

<snapshot_option> ::=<snapshot_option> ::=

決定交易隔離等級。Determines the transaction isolation level.

ALLOW_SNAPSHOT_ISOLATION { ON | OFF }ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
ONON
在資料庫層級啟用快照集選項。Enables Snapshot option at the database level. 啟用時,即使沒有交易使用快照隔離,DML 陳述式也會開始產生資料列版本。When it's enabled, DML statements start generating row versions even when no transaction uses Snapshot Isolation. 啟用此選項之後,交易可以指定 SNAPSHOT 交易隔離等級。Once this option is enabled, transactions can specify the SNAPSHOT transaction isolation level. 當交易執行的隔離等級是 SNAPSHOT 時,所有陳述式都會見到在交易開頭便存在的資料快照集。When a transaction runs at the SNAPSHOT isolation level, all statements see a snapshot of data as it exists at the start of the transaction. 如果執行 SNAPSHOT 隔離等級的交易存取多個資料庫中的資料,此時所有資料庫中的 ALLOW_SNAPSHOT_ISOLATION 都必須設為 ON,或是每當 FROM 子句參考 ALLOW_SNAPSHOT_ISOLATION 是 OFF 的資料庫中的資料表時,交易中的每個陳述式都必須使用鎖定提示。If a transaction running at the SNAPSHOT isolation level accesses data in multiple databases, either ALLOW_SNAPSHOT_ISOLATION must be set to ON in all the databases, or each statement in the transaction must use locking hints on any reference in a FROM clause to a table in a database where ALLOW_SNAPSHOT_ISOLATION is OFF.

OFFOFF
在資料庫層級關閉快照集選項。Turns off the Snapshot option at the database level. 交易無法指定 SNAPSHOT 交易隔離等級。Transactions can't specify the SNAPSHOT transaction isolation level.

當您將 ALLOW_SNAPSHOT_ISOLATION 設為新狀態 (從 ON 設成 OFF,或從 OFF 設成 ON) 時,在認可資料庫中的所有現有交易之前,ALTER DATABASE 並不會將控制權傳回呼叫端。When you set ALLOW_SNAPSHOT_ISOLATION to a new state (from ON to OFF, or from OFF to ON), ALTER DATABASE doesn't return control to the caller until all existing transactions in the database are committed. 如果資料庫已在 ALTER DATABASE 陳述式所指定的狀態中,控制權會立即傳回呼叫端。If the database is already in the state specified in the ALTER DATABASE statement, control is returned to the caller immediately. 如果 ALTER DATABASE 陳述式並沒有很快傳回,請使用 sys.dm_tran_active_snapshot_database_transactions 來判斷是否有長期執行的交易。If the ALTER DATABASE statement doesn't return quickly, use sys.dm_tran_active_snapshot_database_transactions to determine whether there are long-running transactions. 如果取消了 ALTER DATABASE 陳述式,資料庫會保留在 ALTER DATABASE 啟動時的狀態中。If the ALTER DATABASE statement is canceled, the database remains in the state it was in when ALTER DATABASE was started. sys.databases 目錄檢視指出資料庫中快照集隔離交易的狀態。The sys.databases catalog view indicates the state of snapshot-isolation transactions in the database. 如果 snapshot_isolation_state_desc = IN_TRANSITION_TO_ON,ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF 會暫停六秒,然後重試作業。If snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF will pause six seconds and retry the operation.

如果資料庫是 OFFLINE,則您無法變更 ALLOW_SNAPSHOT_ISOLATION 的狀態。You can't change the state of ALLOW_SNAPSHOT_ISOLATION if the database is OFFLINE.

如果您在 READ_ONLY 資料庫中設定 ALLOW_SNAPSHOT_ISOLATION,資料庫後來又設為 READ_WRITE,會保留這個設定。If you set ALLOW_SNAPSHOT_ISOLATION in a READ_ONLY database, the setting will be kept if the database is later set to READ_WRITE.

您可以變更 master、model、msdb 和 tempdb 等資料庫的 ALLOW_SNAPSHOT_ISOLATION 設定。You can change the ALLOW_SNAPSHOT_ISOLATION settings for the master, model, msdb, and tempdb databases. 如果您變更 tempdb 的設定,每次停止和重新啟動 Database EngineDatabase Engine 執行個體時,會保留這個設定。The setting is kept every time the instance of the Database EngineDatabase Engine is stopped and restarted if you change the setting for tempdb. 如果您變更模型的設定,除了 tempdb 以外,任何新建資料庫都會以這個設定為預設值。If you change the setting for model, that setting becomes the default for any new databases that are created, except for tempdb.

依預設,master 和 msdb 資料庫的這個選項是 ON。The option is ON, by default, for the master and msdb databases.

您可以檢查 sys.databases 目錄檢視中 snapshot_isolation_state 資料行來判斷此選項的目前設定。The current setting of this option can be determined by examining the snapshot_isolation_state column in the sys.databases catalog view.

READ_COMMITTED_SNAPSHOT { ON | OFF }READ_COMMITTED_SNAPSHOT { ON | OFF }
ONON
在資料庫層級啟用讀取認可快照集選項。Enables Read-Committed Snapshot option at the database level. 啟用時,即使沒有交易使用快照隔離,DML 陳述式也會開始產生資料列版本。When it's enabled, DML statements start generating row versions even when no transaction uses Snapshot Isolation. 啟用此選項之後,指定 READ COMMITTED 隔離等級的交易即會使用資料列版本設定,而不是鎖定。Once this option is enabled, the transactions specifying the READ COMMITTED isolation level use row versioning instead of locking. 在 READ COMMITTED 隔離等級執行交易時,所有陳述式都會看到資料的快照,就與陳述式開始時所存在的資料一樣。All statements see a snapshot of data as it exists at the start of the statement when a transaction runs at the READ COMMITTED isolation level.

OFFOFF
在資料庫層級關閉讀取認可快照集選項。Turns off Read-Committed Snapshot option at the database level. 指定 READ COMMITTED 隔離等級的交易會使用鎖定。Transactions specifying the READ COMMITTED isolation level use locking.

若要設定 READ_COMMITTED_SNAPSHOT ON 或 OFF,除了執行 ALTER DATABASE 命令的連接之外,不能有任何使用中資料庫連線。To set READ_COMMITTED_SNAPSHOT ON or OFF, there must be no active connections to the database except for the connection running the ALTER DATABASE command. 不過,資料庫不一定要處於單一使用者模式。However, the database doesn't have to be in single-user mode. 當資料庫是 OFFLINE 時,您無法變更這個選項的狀態。You can't change the state of this option when the database is OFFLINE.

如果您在 READ_ONLY 資料庫中設定 READ_COMMITTED_SNAPSHOT,則當資料庫後來又設為 READ_WRITE 時,會保留這個設定。If you set READ_COMMITTED_SNAPSHOT in a READ_ONLY database, the setting will be kept when the database is later set to READ_WRITE.

master、tempdb 或 msdb 系統資料庫的 READ_COMMITTED_SNAPSHOT 不能設為 ON。READ_COMMITTED_SNAPSHOT can't be turned ON for the master, tempdb, or msdb system databases. 如果您變更 model 的設定,除了 tempdb 以外,這項設定會成為任何新建資料庫的預設值。If you change the setting for model, that setting becomes the default for any new databases created, except for tempdb.

您可以檢查 sys.databases 目錄檢視中 is_read_committed_snapshot_on 資料行來判斷此選項的目前設定。The current setting of this option can be determined by examining the is_read_committed_snapshot_on column in the sys.databases catalog view.

警告

當資料表是以 DURABILITY = SCHEMA_ONLY 建立,而且後續會使用 ALTER DATABASE 來變更 READ_COMMITTED_SNAPSHOT 時,資料表中的資料將會遺失。When a table is created with DURABILITY = SCHEMA_ONLY, and READ_COMMITTED_SNAPSHOT is subsequently changed using ALTER DATABASE, data in the table will be lost.

MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT { ON | OFF }MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT { ON | OFF }
ONON
當交易隔離等級設定為 SNAPSHOT 以下的任何隔離等級時,記憶體最佳化資料表上所有解譯的 Transact-SQLTransact-SQL 作業都會在 SNAPSHOT 隔離下執行。When the transaction isolation level is set to any isolation level lower than SNAPSHOT, all interpreted Transact-SQLTransact-SQL operations on memory-optimized tables are run under SNAPSHOT isolation. SNAPSHOT 以下的隔離等級範例包括 READ COMMITTED 或 READ UNCOMMITTED。Examples of isolation levels lower than snapshot are READ COMMITTED or READ UNCOMMITTED. 不論是在工作階段層級明確設定交易隔離等級,或隱含使用預設值,都會執行這些作業。These operations run whether the transaction isolation level is set explicitly at the session level, or the default is used implicitly.

OFFOFF
不會為經記憶體最佳化資料表上解譯的 Transact-SQLTransact-SQL 作業提高交易隔離等級。Doesn't elevate the transaction isolation level for interpreted Transact-SQLTransact-SQL operations on memory-optimized tables.

如果資料庫是 OFFLINE,則您無法變更 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 的狀態。You can't change the state of MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT if the database is OFFLINE.

預設值是 OFF。The default value is OFF.

您可以檢查 sys.databases 目錄檢視中 is_memory_optimized_elevate_to_snapshot_on 資料行來判斷此選項的目前設定。The current setting of this option can be determined by examining the is_memory_optimized_elevate_to_snapshot_on column in the sys.databases catalog view.

<sql_option> ::=<sql_option> ::=

控制資料庫層級的 ANSI 合規性選項。Controls the ANSI compliance options at the database level.

ANSI_NULL_DEFAULT { ON | OFF }ANSI_NULL_DEFAULT { ON | OFF }
決定未在 CREATE TABLE 或 ALTER TABLE 陳述式中明確定義 Null 屬性之資料行或 CLR 使用者定義型別的預設值 (NULL 或 NOT NULL)。Determines the default value, NULL or NOT NULL, of a column or CLR user-defined type for which the nullability isn't explicitly defined in CREATE TABLE or ALTER TABLE statements. 條件約束所定義的資料行會遵循條件約束規則,不論這個設定可能為何。Columns that are defined with constraints follow constraint rules whatever this setting may be.

ONON
預設值是 NULL。The default value is NULL.

OFFOFF
預設值是 NOT NULL。The default value is NOT NULL.

利用 SET 陳述式來設定的連接層級設定會覆寫 ANSI_NULL_DEFAULT 的預設資料庫層級設定。Connection-level settings that are set by using the SET statement override the default database-level setting for ANSI_NULL_DEFAULT. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將工作階段的 ANSI_NULL_DEFAULT 設定為 ON。ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULL_DEFAULT to ON for the session, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET ANSI_NULL_DFLT_ONFor more information, see SET ANSI_NULL_DFLT_ON.

對於 ANSI 相容性而言,將資料庫選項 ANSI_NULL_DEFAULT 設為 ON 會將資料庫預設值改成 NULL。For ANSI compatibility, setting the database option ANSI_NULL_DEFAULT to ON changes the database default to NULL.

您可以檢查 sys.databases 目錄檢視中 is_ansi_null_default_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_ansi_null_default_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAnsiNullDefault 屬性來判斷狀態。You can also determine the status by examining the IsAnsiNullDefault property of the DATABASEPROPERTYEX function.

ANSI_NULLS { ON | OFF }ANSI_NULLS { ON | OFF }
ONON
所有對於 Null 值的比較,都會得出 UNKNOWN。All comparisons to a null value evaluate to UNKNOWN.

OFFOFF
比較非 UNICODE 值和 Null 值,如果兩個值都是 NULL,便會得出 TRUE。Comparisons of non-UNICODE values to a null value evaluate to TRUE if both values are NULL.

重要

在未來的 SQL ServerSQL Server 版本中,ANSI_NULLS 一律為 ON,而且明確將此選項設定為 OFF 的任何應用程式都會產生錯誤。In a future version of SQL ServerSQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will produce an error. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

利用 SET 陳述式來設定的連接層級設定會覆寫 ANSI_NULLS 的預設資料庫設定。Connection-level settings that are set by using the SET statement override the default database setting for ANSI_NULLS. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將工作階段的 ANSI_NULLS 設定為 ON。ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULLS to ON for the session, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET ANSI_NULLSFor more information, see SET ANSI_NULLS.

注意

當您建立或變更計算資料行索引或索引檢視表時,SET ANSI_NULLS 也必須設為 ON。SET ANSI_NULLS also must be set to ON when you create or make changes to indexes on computed columns or indexed views.

您可以檢查 sys.databases 目錄檢視中 is_ansi_nulls_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_ansi_nulls_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAnsiNullsEnabled 屬性來判斷狀態。You can also determine the status by examining the IsAnsiNullsEnabled property of the DATABASEPROPERTYEX function.

ANSI_PADDING { ON | OFF }ANSI_PADDING { ON | OFF }
ONON
轉換字串前先填補至相同的長度。Strings are padded to the same length before conversion. 也會填補到相同的長度,再插入 varcharnvarchar 資料類型。Also padded to the same length before inserting to a varchar or nvarchar data type.

OFFOFF
varcharnvarchar 資料行會插入字元值的尾端空格。Inserts trailing blanks in character values into varchar or nvarchar columns. 已插入 varbinary 資料行的二進位值尾端零也會保留。Also leaves trailing zeros in binary values that are inserted into varbinary columns. 值不會填補到資料行的長度。Values aren't padded to the length of the column.

當指定 OFF 時,這個設定只會影響新資料行的定義。When OFF is specified, this setting affects only the definition of new columns.

重要

在未來的 SQL ServerSQL Server 版本中,ANSI_PADDING 一律為 ON,而且明確將此選項設定為 OFF 的應用程式將會產生錯誤。In a future version of SQL ServerSQL Server, ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature. 我們建議您一律將 ANSI_PADDING 設為 ON。We recommend that you always set ANSI_PADDING to ON. 當您建立或操作計算資料行索引或索引檢視表時,ANSI_PADDING 也必須是 ON。ANSI_PADDING must be ON when you create or manipulate indexes on computed columns or indexed views.

當 ANSI_PADDING 設定為 ON 時,允許 Null 的 char(n)binary(n) 資料行會填補到資料行長度。char(n) and binary(n) columns that allow for nulls are padded to the column length when ANSI_PADDING is set to ON. 當 ANSI_PADDING 為 OFF 時,則會修剪尾端空格和尾端零。Trailing blanks and zeros are trimmed when ANSI_PADDING is OFF. 不允許 Null 的 char(n)binary(n) 資料行一律會填補到資料行的長度。char(n) and binary(n) columns that don't allow nulls are always padded to the length of the column.

利用 SET 陳述式來設定的連接層級設定會覆寫 ANSI_PADDING 的預設資料庫層級設定。Connection-level settings that are set by using the SET statement override the default database-level setting for ANSI_PADDING. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將工作階段的 ANSI_PADDING 設定為 ON。ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_PADDING to ON for the session, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET ANSI_PADDINGFor more information, see SET ANSI_PADDING.

您可以檢查 sys.databases 目錄檢視中 is_ansi_padding_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_ansi_padding_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAnsiPaddingEnabled 屬性來判斷狀態。You can also determine the status by examining the IsAnsiPaddingEnabled property of the DATABASEPROPERTYEX function.

ANSI_WARNINGS { ON | OFF }ANSI_WARNINGS { ON | OFF }
ONON
如果發生「除以零」之類的狀況,便會發出錯誤或警告。Errors or warnings are issued when conditions such as divide-by-zero occur. 當彙總函式中出現 Null 值時,也會發出錯誤或警告。Errors and warnings are also issued when null values appear in aggregate functions.

OFFOFF
當發生除以零之類的情況時,不會產生警告,但會傳回 NULL 值。No warnings are raised and null values are returned when conditions such as divide-by-zero occur.

注意

當您建立或變更計算資料行索引或索引檢視表時,SET ANSI_WARNINGS 必須設為 ON。SET ANSI_WARNINGS must be set to ON when you create or make changes to indexes on computed columns or indexed views.

利用 SET 陳述式來設定的連接層級設定會覆寫 ANSI_WARNINGS 的預設資料庫設定。Connection-level settings that are set by using the SET statement override the default database setting for ANSI_WARNINGS. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將工作階段的 ANSI_WARNINGS 設定為 ON。ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_WARNINGS to ON for the session, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET ANSI_WARNINGSFor more information, see SET ANSI_WARNINGS.

您可以檢查 sys.databases 目錄檢視中 is_ansi_warnings_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_ansi_warnings_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAnsiWarningsEnabled 屬性來判斷狀態。You can also determine the status by examining the IsAnsiWarningsEnabled property of the DATABASEPROPERTYEX function.

ARITHABORT { ON | OFF }ARITHABORT { ON | OFF }
ONON
在查詢執行期間,當發生溢位或除以零的錯誤時,查詢會結束。A query is ended when an overflow or divide-by-zero error occurs during query execution.

OFFOFF
當發生這些錯誤之一時,畫面上會顯示警告訊息。A warning message is displayed when one of these errors occurs. 即使顯示警告,查詢、批次或交易還是會繼續處理,如同未發生任何錯誤一樣。The query, batch, or transaction continues to process as if no error occurred even if a warning is displayed.

注意

當您建立或變更計算資料行索引或索引檢視表時,SET ARITHABORT 必須設為 ON。SET ARITHABORT must be set to ON when you create or make changes to indexes on computed columns or indexed views.

您可以檢查 sys.databases 目錄檢視中 is_arithabort_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_arithabort_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsArithmeticAbortEnabled 屬性來判斷狀態。You can also determine the status by examining the IsArithmeticAbortEnabled property of the DATABASEPROPERTYEX function.

COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 }COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 }
如需詳細資訊,請參閱 ALTER DATABASE 相容性層級For more information, see ALTER DATABASE Compatibility Level.

CONCAT_NULL_YIELDS_NULL { ON | OFF }CONCAT_NULL_YIELDS_NULL { ON | OFF }
ONON
當任何一個運算元是 NULL 時,串連作業的結果都會是 NULL。The result of a concatenation operation is NULL when either operand is NULL. 例如,串連字元字串 "This is" 和 NULL 會得出 NULL 值,而不是 "This is" 值。For example, concatenating the character string "This is" and NULL causes the value NULL, instead of the value "This is".

OFFOFF
將 Null 值當作空白字元字串來處理。The null value is treated as an empty character string.

注意

當您建立或變更計算資料行索引或索引檢視表時,CONCAT_NULL_YIELDS_NULL 也必須設為 ON。CONCAT_NULL_YIELDS_NULL must be set to ON when you create or make changes to indexes on computed columns or indexed views.

重要

在未來的 SQL ServerSQL Server 版本中,CONCAT_NULL_YIELDS_NULL 一律為 ON,而且明確將此選項設定為 OFF 的應用程式將會產生錯誤。In a future version of SQL ServerSQL Server, CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will produce an error. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

利用 SET 陳述式來設定的連接層級設定會覆寫 CONCAT_NULL_YIELDS_NULL 的預設資料庫設定。Connection-level settings that are set by using the SET statement override the default database setting for CONCAT_NULL_YIELDS_NULL. 根據預設,當連接到 SQL ServerSQL Server 執行個體時,ODBC 和 OLE DB 用戶端會發出連接層級的 SET 陳述式,將工作階段的 CONCAT_NULL_YIELDS_NULL 設為 ON。By default, ODBC and OLE DB clients issue a connection-level SET statement setting CONCAT_NULL_YIELDS_NULL to ON for the session when connecting to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET CONCAT_NULL_YIELDS_NULLFor more information, see SET CONCAT_NULL_YIELDS_NULL.

您可以檢查 sys.databases 目錄檢視中 is_concat_null_yields_null_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_concat_null_yields_null_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsNullConcat 屬性來判斷狀態。You can also determine the status by examining the IsNullConcat property of the DATABASEPROPERTYEX function.

QUOTED_IDENTIFIER { ON | OFF }QUOTED_IDENTIFIER { ON | OFF }
ONON
可以使用雙引號來含括分隔識別碼。Double quotation marks can be used to enclose delimited identifiers.

用雙引號來分隔的所有字串都會解譯為物件識別碼。All strings delimited by double quotation marks are interpreted as object identifiers. 附加引號的識別碼不需要遵循 Transact-SQLTransact-SQL 的識別碼規則。Quoted identifiers don't have to follow the Transact-SQLTransact-SQL rules for identifiers. 它們可以是關鍵字,也可以包括 Transact-SQLTransact-SQL 識別碼不允許的字元。They can be keywords and can include characters not allowed in Transact-SQLTransact-SQL identifiers. 如果單引號 (') 是文字字串的一部分,您可以用雙引號 (") 來表示它。If a single quotation mark (') is part of the literal string, it can be represented by double quotation marks (").

OFFOFF
識別碼不能放在引號中,且必須遵循所有 Transact-SQLTransact-SQL 識別碼規則。Identifiers can't be in quotation marks and must follow all Transact-SQLTransact-SQL rules for identifiers. 文字可以用單引號或雙引號來分隔。Literals can be delimited by either single or double quotation marks.

SQL ServerSQL Server 也允許用方括號 ([ ]) 來分隔識別碼。also allows for identifiers to be delimited by square brackets ([ ]). 不論 QUOTED_IDENTIFIER 設定為何,用方括弧括住的識別項一律可以使用。Bracketed identifiers can always be used, whatever the QUOTED_IDENTIFIER setting is. 如需詳細資訊,請參閱< Database Identifiers>。For more information, see Database Identifiers.

當建立資料表時,一律會在資料表的中繼資料中,將 QUOTED IDENTIFIER 選項儲存成 ON。When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the metadata of the table. 即使建立資料表時,將選項設定為 OFF,也會儲存此選項。The option is stored even if the option is set to OFF when the table is created.

利用 SET 陳述式來設定的連接層級設定會覆寫 QUOTED_IDENTIFIER 的預設資料庫設定。Connection-level settings that are set by using the SET statement override the default database setting for QUOTED_IDENTIFIER. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將 QUOTED_IDENTIFIER 設定為 ON。ODBC and OLE DB clients issue a connection-level SET statement setting QUOTED_IDENTIFIER to ON, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET QUOTED_IDENTIFIERFor more information, see SET QUOTED_IDENTIFIER.

您可以檢查 sys.databases 目錄檢視中 is_quoted_identifier_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_quoted_identifier_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsQuotedIdentifiersEnabled 屬性來判斷狀態。You can also determine the status by examining the IsQuotedIdentifiersEnabled property of the DATABASEPROPERTYEX function.

NUMERIC_ROUNDABORT { ON | OFF }NUMERIC_ROUNDABORT { ON | OFF }
ONON
當運算式中遺失有效位數時,系統會產生錯誤。An error is generated when loss of precision occurs in an expression.

OFFOFF
遺失有效位數並不會產生錯誤訊息,且結果會四捨五入到儲存結果的資料行或變數有效位數。Loss of precision doesn't generate an error message, and the result is rounded to the precision of the column or variable storing the result.

重要

當您建立或變更計算資料行索引或索引檢視表時,NUMERIC_ROUNDABORT 必須設為 OFF。NUMERIC_ROUNDABORT must be set to OFF when you create or make changes to indexes on computed columns or indexed views.

您可以檢查 sys.databases 目錄檢視中 is_numeric_roundabort_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_numeric_roundabort_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsNumericRoundAbortEnabled 屬性來判斷狀態。You can also determine the status by examining the IsNumericRoundAbortEnabled property of the DATABASEPROPERTYEX function.

RECURSIVE_TRIGGERS { ON | OFF }RECURSIVE_TRIGGERS { ON | OFF }
ONON
允許遞迴引發 AFTER 觸發程序。Recursive firing of AFTER triggers is allowed.

OFFOFF
您可以檢查 sys.databases 目錄檢視中 is_recursive_triggers_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_recursive_triggers_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsRecursiveTriggersEnabled 屬性來判斷狀態。You can also determine the status by examining the IsRecursiveTriggersEnabled property of the DATABASEPROPERTYEX function.

注意

當 RECURSIVE_TRIGGERS 設為 OFF 時,只防止直接遞迴。Only direct recursion is prevented when RECURSIVE_TRIGGERS is set to OFF. 若要停用間接遞迴,您也必須將巢狀觸發程序伺服器選項設為 0。To disable indirect recursion, you must also set the nested triggers server option to 0.

您可以檢查 sys.databases 目錄檢視中 is_recursive_triggers_on 資料行或 DATABASEPROPERTYEX 函式 IsRecursiveTriggersEnabled 屬性來判斷這個選項的狀態。You can determine this option's status by examining the is_recursive_triggers_on column in the sys.databases catalog view or the IsRecursiveTriggersEnabled property of the DATABASEPROPERTYEX function.

<target_recovery_time_option> ::=<target_recovery_time_option> ::=

為每個資料庫指定間接檢查點的頻率。Specifies the frequency of indirect checkpoints on a per-database basis. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,新資料庫的預設值為 1 分鐘,這表示資料庫將會使用間接檢查點。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) the default value for new databases is 1 minute, which indicates database will use indirect checkpoints. 舊版的預設值為 0,這表示資料庫將使用自動檢查點,其頻率取決於伺服器執行個體的復原間隔設定。For older versions the default is 0, which indicates that the database will use automatic checkpoints, whose frequency depends on the recovery interval setting of the server instance. 對於大多數的系統,MicrosoftMicrosoft 建議使用 1 分鐘。MicrosoftMicrosoft recommends 1 minute for most systems.

TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }
target_recovery_time target_recovery_time
指定萬一發生損毀時,復原指定之資料庫的時間上限。Specifies the maximum bound on the time to recover the specified database in the event of a crash.

SECONDSSECONDS
指出 target_recovery_time 應以秒數表示。Indicates that target_recovery_time is expressed as the number of seconds.

MINUTESMINUTES
指出 target_recovery_time 應以分鐘數表示。Indicates that target_recovery_time is expressed as the number of minutes.

如需間接檢查點的詳細資訊,請參閱資料庫檢查點For more information about indirect checkpoints, see Database Checkpoints.

WITH <termination> ::=WITH <termination> ::=

指定資料庫狀態轉換時,何時回復不完整的交易。Specifies when to roll back incomplete transactions when the database is transitioned from one state to another. 如果省略 termination 子句,且資料庫有任何鎖定,則 ALTER DATABASE 陳述式會無限等候。If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely if there's any lock on the database. 只能指定一個 termination 子句,它在 SET 子句之後。Only one termination clause can be specified, and it follows the SET clauses.

注意

並非所有的資料庫選項都會使用 WITH <termination> 子句。Not all database options use the WITH <termination> clause. 如需詳細資訊,請參閱本文<備註>一節中<設定選項>下的表格。For more information, see the table under "Setting options of the "Remarks" section of this article.

ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATEROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
指定在指定的秒數之後回復,或是立即回復。Specifies whether to roll back after the specified number of seconds or immediately.

NO_WAITNO_WAIT
指定如果要求的資料庫狀態或選項變更無法立即完成,要求將會失敗。Specifies that the request will fail if the requested database state or option change can't complete immediately. 立即完成表示不等候交易自行認可或回復。Completing immediately means not waiting for transactions to commit or roll back on their own.

設定選項Setting options

若要擷取資料庫選項的目前設定,請使用 sys.databases 目錄檢視或 DATABASEPROPERTYEXTo retrieve current settings for database options, use the sys.databases catalog view or DATABASEPROPERTYEX

設好資料庫選項之後,新設定會立即生效。After you set a database option, the new setting takes effect immediately.

您可以針對所有新建立的資料庫,變更任何一個資料庫選項的預設值。You can change the default values for any one of the database options for all newly created databases. 若要執行這項操作,請變更 model 資料庫中的適當資料庫選項。To do so, change the appropriate database option in the model database.

並非所有資料庫選項都會使用 WITH <termination> 子句,也並非所有資料庫選項都能夠結合其他選項來指定。Not all database options use the WITH <termination> clause or can be specified in combination with other options. 下表列出這些選項及其選項和終止狀態。The following table lists these options and their option and termination status.

選項類別目錄Options category 可以搭配其他選項指定Can be specified with other options 可以使用 WITH <termination> 子句Can use the WITH <termination> clause
<auto_option><auto_option> Yes No
<change_tracking_option><change_tracking_option> Yes Yes
<cursor_option><cursor_option> Yes No
<db_encryption_option><db_encryption_option> Yes No
<db_update_option><db_update_option> Yes Yes
<db_user_access_option><db_user_access_option> Yes Yes
<delayed_durability_option><delayed_durability_option> Yes Yes
<parameterization_option><parameterization_option> Yes Yes
ALLOW_SNAPSHOT_ISOLATIONALLOW_SNAPSHOT_ISOLATION No No
READ_COMMITTED_SNAPSHOTREAD_COMMITTED_SNAPSHOT No Yes
MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOTMEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT Yes Yes
DATE_CORRELATION_OPTIMIZATIONDATE_CORRELATION_OPTIMIZATION Yes Yes
<sql_option><sql_option> Yes No
<target_recovery_time_option><target_recovery_time_option> No Yes

範例Examples

A.A. 將資料庫設為 READ_ONLYSetting the database to READ_ONLY

將資料庫或檔案群組的狀態改成 READ_ONLY 或 READ_WRITE 時,需要資料庫的獨佔存取權。Changing the state of a database or file group to READ_ONLY or READ_WRITE requires exclusive access to the database. 下列範例會將資料庫設成 RESTRICTED_USER 模式來限制存取。The following example sets the database to RESTRICTED_USER mode to limit access. 之後,範例會將 AdventureWorks2012AdventureWorks2012 資料庫的狀態設成 READ_ONLY ,並將資料庫的存取權還給所有使用者。The example then sets the state of the AdventureWorks2012AdventureWorks2012 database to READ_ONLY and returns access to the database to all users.

USE master;
GO
ALTER DATABASE [database_name]
SET RESTRICTED_USER;
GO
ALTER DATABASE [database_name]
SET READ_ONLY
GO
ALTER DATABASE [database_name]
SET MULTI_USER;
GO

B.B. 啟用資料庫的快照集隔離Enabling snapshot isolation on a database

下列範例會啟用 AdventureWorks2012AdventureWorks2012 資料庫的快照集隔離架構選項。The following example enables the snapshot isolation framework option for the AdventureWorks2012AdventureWorks2012 database.

USE [database_name];
USE master;
GO
ALTER DATABASE [database_name]
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Check the state of the snapshot_isolation_framework
-- in the database.
SELECT name, snapshot_isolation_state,
    snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'[database_name]';
GO

結果集顯示啟用快照集隔離架構。The result set shows that the snapshot isolation framework is enabled.

NAMEname snapshot_isolation_statesnapshot_isolation_state descriptiondescription
[database_name][database_name] 11 ONON

C.C. 啟用、修改及停用變更追蹤Enabling, modifying, and disabling change tracking

下列範例會啟用 AdventureWorks2012AdventureWorks2012 資料庫的變更追蹤,並將保留週期設定為 2 天。The following example enables change tracking for the AdventureWorks2012AdventureWorks2012 database and sets the retention period to 2 days.

ALTER DATABASE [database_name]
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP = ON, CHANGE_RETENTION = 2 DAYS);

下列範例會示範如何將保留期間變更為 3 天。The following example shows how to change the retention period to 3 days.

ALTER DATABASE [database_name]
SET CHANGE_TRACKING (CHANGE_RETENTION = 3 DAYS);

下列範例會示範如何停用 AdventureWorks2012AdventureWorks2012 資料庫的變更追蹤。The following example shows how to disable change tracking for the AdventureWorks2012AdventureWorks2012 database.

ALTER DATABASE [database_name]
SET CHANGE_TRACKING = OFF;

D.D. 啟用查詢存放區Enabling the Query Store

下列範例會啟用查詢存放區並設定查詢存放區參數。The following example enables the Query Store and configures Query Store parameters.

ALTER DATABASE [database_name]
SET QUERY_STORE = ON
(
      OPERATION_MODE = READ_WRITE
    , CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 )
    , DATA_FLUSH_INTERVAL_SECONDS = 900
    , MAX_STORAGE_SIZE_MB = 1024
    , INTERVAL_LENGTH_MINUTES = 60
    );

另請參閱See Also

SQL ServerSQL Server SQL Database
單一資料庫/彈性集區
SQL Database
single database/elastic pool
* SQL Database
受控執行個體 *
 
* SQL Database
managed instance *
 
SQL 資料
倉儲
SQL Data
Warehouse

 

Azure SQL Database 受控執行個體Azure SQL Database managed instance

相容性層級為 SET 選項,但在 ALTER DATABASE 相容性層級中描述。Compatibility levels are SET options but are described in ALTER DATABASE Compatibility Level.

注意

目前工作階段的許多資料庫 SET 選項都可以使用 SET 陳述式來設定,而且通常是在應用程式連線時由其加以設定。Many database set options can be configured for the current session by using SET Statements and are often configured by applications when they connect. 工作階段層級 SET 選項會覆寫 ALTER DATABASE SET 值。Session-level set options override the ALTER DATABASE SET values. 下列章節所述的資料庫選項皆為未明確提供其他 SET 選項值,因而可針對工作階段進行設定的值。The database options described in the following sections are values that can be set for sessions that don't explicitly provide other set option values.

語法Syntax

ALTER DATABASE { database_name | Current }
SET
{
    <optionspec> [ ,...n ]
}
;

<optionspec> ::=
{
    <auto_option>
  | <change_tracking_option>
  | <cursor_option>
  | <db_encryption_option>
  | <delayed_durability_option>
  | <parameterization_option>
  | <query_store_options>
  | <snapshot_option>
  | <sql_option>
  | <target_recovery_time_option>
  | <termination>
  | <temporal_history_retention>
}
;
<auto_option> ::=
{
    AUTO_CREATE_STATISTICS { OFF | ON [ ( INCREMENTAL = { ON | OFF } ) ] }
  | AUTO_SHRINK { ON | OFF }
  | AUTO_UPDATE_STATISTICS { ON | OFF }
  | AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}

<automatic_tuning_option> ::=
{
    AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = { ON | OFF } )
}

<change_tracking_option> ::=
{
    CHANGE_TRACKING
    {
       = OFF
     | = ON [ ( <change_tracking_option_list > [,...n] ) ]
     | ( <change_tracking_option_list> [,...n] )
    }
}

<change_tracking_option_list> ::=
   {
       AUTO_CLEANUP = { ON | OFF }
     | CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }
   }

<cursor_option> ::=
{
    CURSOR_CLOSE_ON_COMMIT { ON | OFF }
}

<db_encryption_option> ::=
  ENCRYPTION { ON | OFF }

<delayed_durability_option> ::=DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

<parameterization_option> ::=
  PARAMETERIZATION { SIMPLE | FORCED }

<query_store_options> ::=
{
  QUERY_STORE
  {
    = OFF
    | = ON [ ( <query_store_option_list> [,... n] ) ]
    | ( < query_store_option_list> [,... n] )
    | CLEAR [ ALL ]
  }
}

<query_store_option_list> ::=
{
  OPERATION_MODE = { READ_WRITE | READ_ONLY }
  | CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = number )
  | DATA_FLUSH_INTERVAL_SECONDS = number
  | MAX_STORAGE_SIZE_MB = number
  | INTERVAL_LENGTH_MINUTES = number
  | SIZE_BASED_CLEANUP_MODE = [ AUTO | OFF ]
  | QUERY_CAPTURE_MODE = [ ALL | AUTO | NONE ]
  | MAX_PLANS_PER_QUERY = number
}

<snapshot_option> ::=
{
    ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
  | READ_COMMITTED_SNAPSHOT {ON | OFF }
  | MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT {ON | OFF }
}
<sql_option> ::=
{
    ANSI_NULL_DEFAULT { ON | OFF }
  | ANSI_NULLS { ON | OFF }
  | ANSI_PADDING { ON | OFF }
  | ANSI_WARNINGS { ON | OFF }
  | ARITHABORT { ON | OFF }
  | COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 }
  | CONCAT_NULL_YIELDS_NULL { ON | OFF }
  | NUMERIC_ROUNDABORT { ON | OFF }
  | QUOTED_IDENTIFIER { ON | OFF }
  | RECURSIVE_TRIGGERS { ON | OFF }
}

<temporal_history_retention>::= TEMPORAL_HISTORY_RETENTION { ON | OFF }

引數Arguments

database_name database_name
這是要修改之資料庫的名稱。Is the name of the database to be modified.

CURRENTCURRENT
CURRENT 會在目前資料庫中執行動作。CURRENT runs the action in the current database. 所有選項在所有內容中不支援 CURRENTCURRENT isn't supported for all options in all contexts. 如果 CURRENT 失敗,請提供資料庫名稱。If CURRENT fails, provide the database name.

<auto_option> ::=<auto_option> ::=

控制自動選項。Controls automatic options.

AUTO_CREATE_STATISTICS { ON | OFF }AUTO_CREATE_STATISTICS { ON | OFF }
ONON
查詢最佳化工具會視需要針對查詢述詞中的單一資料行建立統計資料,以便改善查詢計劃和查詢效能。Query Optimizer creates statistics on single columns in query predicates, as necessary, to improve query plans and query performance. 這些單一資料行統計資料是在查詢最佳化工具編譯查詢時所建立。These single-column statistics are created when Query Optimizer compiles queries. 它只會針對尚未成為現有統計資料物件之第一個資料行的資料行建立單一資料行統計資料。The single-column statistics are created only on columns that are not already the first column of an existing statistics object.

預設值是 ON。The default is ON. 我們建議您針對大部分資料庫使用預設設定。We recommend that you use the default setting for most databases.

OFFOFF
查詢最佳化工具不會在編譯查詢時針對查詢述詞中的單一資料行建立統計資料。Query Optimizer doesn't create statistics on single columns in query predicates when it's compiling queries. 將這個選項設定為 OFF 可能會導致次佳查詢計劃並降低查詢效能。Setting this option to OFF can cause suboptimal query plans and degraded query performance.

您可以檢查 sys.databases 目錄檢視中 is_auto_create_stats_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_auto_create_stats_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAutoCreateStatistics 屬性來判斷狀態。You can also determine the status by examining the IsAutoCreateStatistics property of the DATABASEPROPERTYEX function.

如需詳細資訊,請參閱統計資料中的<統計資料選項>一節。For more information, see the "Statistics options" section in Statistics.

INCREMENTAL = ON | OFF INCREMENTAL = ON | OFF
將 AUTO_CREATE_STATISTICS 設定為 ON,並將 INCREMENTAL 設定為 ON。Set AUTO_CREATE_STATISTICS to ON, and set INCREMENTAL to ON. 此設定會在每次支援累加統計資料時,以累加方式建立自動建立的統計資料。This setting creates automatically created stats as incremental whenever incremental stats are supported. 預設值是 OFF。The default value is OFF. 如需詳細資訊,請參閱 CREATE STATISTICSFor more information, see CREATE STATISTICS.

AUTO_SHRINK { ON | OFF }AUTO_SHRINK { ON | OFF }
ONON
資料庫檔案是定期壓縮的候選項。The database files are candidates for periodic shrinking.

資料檔案和記錄檔都可以自動壓縮。Both data file and log files can be automatically shrunk. 只有在您將資料庫設定為 SIMPLE 復原模式或備份記錄時,AUTO_SHRINK 才會縮減交易記錄的大小。AUTO_SHRINK reduces the size of the transaction log only if you set the database to SIMPLE recovery model or if you back up the log. 當設定為 OFF 時,便不會在定期檢查未用空間時,自動壓縮資料庫檔案。When set to OFF, the database files aren't automatically shrunk during periodic checks for unused space.

當超出 25% 的檔案包含未用空間時,AUTO_SHRINK 選項便會壓縮檔案。The AUTO_SHRINK option causes files to be shrunk when more than 25 percent of the file contains unused space. 此選項會使檔案壓縮成兩種大小之一。The option causes the file to shrink to one of two sizes. 它會壓縮成兩者中的較大者:It shrinks to whichever is larger:

  • 25% 的檔案是未使用空間的大小The size where 25 percent of the file is unused space
  • 檔案建立時的大小The size of the file when it was created

您無法壓縮唯讀資料庫。You can't shrink a read-only database.

OFFOFF
在定期檢查未用空間時,不自動壓縮資料庫檔案。The database files are not automatically shrunk during periodic checks for unused space.

您可以檢查 sys.databases 目錄檢視中 is_auto_shrink_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_auto_shrink_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAutoShrink 屬性來判斷狀態。You can also determine the status by examining the IsAutoShrink property of the DATABASEPROPERTYEX function.

注意

自主資料庫無法使用 AUTO_SHRINK 選項。The AUTO_SHRINK option isn't available in a Contained Database.

AUTO_UPDATE_STATISTICS { ON | OFF }AUTO_UPDATE_STATISTICS { ON | OFF }
ONON
指定當查詢使用統計資料且它們可能已過期時,查詢最佳化工具就會更新這些統計資料。Specifies that Query Optimizer updates statistics when they're used by a query and when they might be out-of-date. 當插入、更新、刪除或合併作業變更資料表或索引檢視表中的資料分佈之後,統計資料就會變成過期。Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. 查詢最佳化工具會計算自從上次更新統計資料以來資料修改的次數,並將修改次數與某個閾值比較,藉以判斷統計資料是否可能已經過期。Query Optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. 此臨界值是以資料表或索引檢視表中的資料列數目為基礎。The threshold is based on the number of rows in the table or indexed view.

查詢最佳化工具會在編譯查詢及執行快取查詢計劃之前,檢查是否有過期的統計資料。Query Optimizer checks for out-of-date statistics before it compiles a query and runs a cached query plan. 查詢最佳化工具會在查詢述詞中使用資料行、資料表和索引檢視表,來判斷哪些統計資料可能已過期。Query Optimizer uses the columns, tables, and indexed views in the query predicate to determine which statistics might be out-of-date. 查詢最佳化工具會在編譯查詢之前判斷這項資訊。Query Optimizer determines this information before it compiles a query. 在執行快取查詢計劃之前,Database EngineDatabase Engine 會確認查詢計劃是否參考最新的統計資料。Before running a cached query plan, the Database EngineDatabase Engine verifies that the query plan references up-to-date statistics.

AUTO_UPDATE_STATISTICS 選項會套用至針對索引所建立的統計資料、查詢述詞中的單一資料行,以及使用 CREATE STATISTICS 陳述式所建立的統計資料。The AUTO_UPDATE_STATISTICS option applies to statistics created for indexes, single-columns in query predicates, and statistics that are created by using the CREATE STATISTICS statement. 此外,這個選項也會套用至篩選的統計資料。This option also applies to filtered statistics.

預設值是 ON。The default is ON. 我們建議您針對大部分資料庫使用預設設定。We recommend that you use the default setting for most databases.

您可以使用 AUTO_UPDATE_STATISTICS_ASYNC 選項來指定要以同步或非同步方式更新統計資料。Use the AUTO_UPDATE_STATISTICS_ASYNC option to specify whether the statistics are updated synchronously or asynchronously.

OFFOFF
指定當查詢使用統計資料時,查詢最佳化工具不會更新這些統計資料。Specifies that Query Optimizer doesn't update statistics when they're used by a query. 當統計資料可能已過期時,查詢最佳化工具也不會更新這些統計資料。Query Optimizer also doesn't update statistics when they might be out-of-date. 將這個選項設定為 OFF 可能會導致次佳查詢計劃並降低查詢效能。Setting this option to OFF can cause suboptimal query plans and degraded query performance.

您可以檢查 sys.databases 目錄檢視中 is_auto_update_stats_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_auto_update_stats_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAutoUpdateStatistics 屬性來判斷狀態。You can also determine the status by examining the IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.

如需詳細資訊,請參閱統計資料中的<使用資料庫範圍統計資料選項>一節。For more information, see the section "Using the database-wide statistics options" in Statistics.

AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
ONON
指定 AUTO_UPDATE_STATISTICS 選項的統計資料更新是非同步的。Specifies that statistics updates for the AUTO_UPDATE_STATISTICS option are asynchronous. 查詢最佳化工具在編譯查詢之前,不會等候統計資料更新完成。Query Optimizer doesn't wait for statistics updates to complete before it compiles queries.

除非 AUTO_UPDATE_STATISTICS 設為 ON,否則將這個選項設為 ON 沒有作用。Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is set to ON.

預設會將 AUTO_UPDATE_STATISTICS_ASYNC 選項設為 OFF,且查詢最佳化工具會同步更新統計資料。By default, the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF, and Query Optimizer updates statistics synchronously.

OFFOFF
指定 AUTO_UPDATE_STATISTICS 選項的統計資料更新是同步的。Specifies that statistics updates for the AUTO_UPDATE_STATISTICS option are synchronous. 查詢最佳化工具在編譯查詢之前,會先等候統計資料更新完成。Query Optimizer waits for statistics updates to complete before it compiles queries.

除非 AUTO_UPDATE_STATISTICS 設為 ON,否則將這個選項設為 OFF 沒有作用。Setting this option to OFF has no effect unless AUTO_UPDATE_STATISTICS is set to ON.

您可以檢查 sys.databases 目錄檢視中 is_auto_update_stats_async_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_auto_update_stats_async_on column in the sys.databases catalog view.

如需描述使用同步或非同步統計資料更新的時機詳細資訊,請參閱統計資料中的<使用資料庫範圍統計資料選項>一節。For more information that describes when to use synchronous or asynchronous statistics updates, see the section "Using the database-wide statistics options" in Statistics.

<automatic_tuning_option> ::= <automatic_tuning_option> ::=
適用於SQL Server 2017 (14.x)SQL Server 2017 (14.x)Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x)

啟用或停用FORCE_LAST_GOOD_PLAN 自動調整選項。Enables or disables FORCE_LAST_GOOD_PLAN Automatic tuning option.

FORCE_LAST_GOOD_PLAN = { ON | OFF }FORCE_LAST_GOOD_PLAN = { ON | OFF }
ONON
Database EngineDatabase Engine 會對新查詢計劃將造成效能衰退的 Transact-SQLTransact-SQL 查詢,自動強制執行最後一個已知的良好計劃。The Database EngineDatabase Engine automatically forces the last known good plan on the Transact-SQLTransact-SQL queries where new query plan causes performance regressions. Database EngineDatabase Engine 會使用強制方案持續監視 Transact-SQLTransact-SQL 查詢的查詢效能。The Database EngineDatabase Engine continuously monitors query performance of the Transact-SQLTransact-SQL query with the forced plan. 如果效能有所提升,Database EngineDatabase Engine 會繼續使用最後一個已知的良好計劃。If there are performance gains, the Database EngineDatabase Engine will keep using last known good plan. 如果未偵測到效能提升,Database EngineDatabase Engine 會產生新的查詢計劃。If performance gains are not detected, the Database EngineDatabase Engine will produce a new query plan. 如果未啟用查詢存放區,或其不在「讀寫」 模式中,陳述式便會失敗。The statement will fail if the Query Store isn't enabled or if it's not in Read-Write mode.

OFFOFF
Database EngineDatabase Engine 會在 sys.dm_db_tuning_recommendations 檢視中報告查詢計劃變更所造成可能的查詢效能衰退。The Database EngineDatabase Engine reports potential query performance regressions caused by query plan changes in sys.dm_db_tuning_recommendations view. 不過,不會自動套用這些建議。However, these recommendations are not automatically applied. 使用者可以套用檢視中顯示的 Transact-SQLTransact-SQL 指令碼,來監視使用中建議並修正已識別的問題。Users can monitor active recommendations and fix identified problems by applying Transact-SQLTransact-SQL scripts that are shown in the view. 這是預設值。This is the default value.

<change_tracking_option> ::=<change_tracking_option> ::=

控制變更追蹤選項。Controls change tracking options. 您可以啟用變更追蹤、設定選項、變更選項,以及停用變更追蹤。You can enable change tracking, set options, change options, and disable change tracking. 如需範例,請參閱本文稍後的<範例>一節。For examples, see the "Examples" section later in this article.

ONON
啟用資料庫的變更追蹤。Enables change tracking for the database. 當您啟用變更追蹤時,也可以設定 AUTO CLEANUP 和 CHANGE RETENTION 選項。When you enable change tracking, you can also set the AUTO CLEANUP and CHANGE RETENTION options.

AUTO_CLEANUP = { ON | OFF }AUTO_CLEANUP = { ON | OFF }
ONON
在經過指定的保留週期後,將會自動移除變更追蹤資訊。Change tracking information is automatically removed after the specified retention period.

OFFOFF
不會從資料庫中移除變更追蹤資料。Change tracking data isn't removed from the database.

CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }
指定在資料庫中保存變更追蹤資訊的最小週期。Specifies the minimum period for keeping change tracking information in the database. 只有當 AUTO_CLEANUP 值為 ON 時,才會移除資料。Data is removed only when the AUTO_CLEANUP value is ON.

retention_period 是一個整數,它會指定保留週期的數值元件。retention_period is an integer that specifies the numerical component of the retention period.

預設保留期間為 2 天The default retention period is 2 days. 最小保留週期是 1 分鐘。The minimum retention period is 1 minute. 預設保留期類型為 DAYSThe default retention type is DAYS.

OFFOFF
停用資料庫的變更追蹤。Disables change tracking for the database. 在您停用資料庫的變更追蹤之前,請先在所有資料表上停用變更追蹤。Disable change tracking on all tables before you disable change tracking off the database.

<cursor_option> ::=<cursor_option> ::=

控制資料指標選項。Controls cursor options.

CURSOR_CLOSE_ON_COMMIT { ON | OFF }CURSOR_CLOSE_ON_COMMIT { ON | OFF }
ONON
當您認可或回復交易時,將會關閉任何開啟的資料指標。Any cursors open when you commit or roll back a transaction are closed.

OFFOFF
當認可交易時,資料指標維持開啟狀態;回復交易會關閉任何資料指標,但定義為 INSENSITIVE 或 STATIC 的資料指標除外。Cursors remain open when a transaction is committed; rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.

利用 SET 陳述式來設定的連接層級設定會覆寫 CURSOR_CLOSE_ON_COMMIT 的預設資料庫設定。Connection-level settings that are set by using the SET statement override the default database setting for CURSOR_CLOSE_ON_COMMIT. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將工作階段的 CURSOR_CLOSE_ON_COMMIT 設定為 OFF。ODBC and OLE DB clients issue a connection-level SET statement setting CURSOR_CLOSE_ON_COMMIT to OFF for the session, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET CURSOR_CLOSE_ON_COMMITFor more information, see SET CURSOR_CLOSE_ON_COMMIT.

您可以檢查 sys.databases 目錄檢視中的 is_cursor_close_on_commit_on 資料行,或 DATABASEPROPERTYEX 函式 IsCloseCursorsOnCommitEnabled 屬性來判斷這個選項的狀態。You can determine this option's status by examining the is_cursor_close_on_commit_on column in the sys.databases catalog view or the IsCloseCursorsOnCommitEnabled property of the DATABASEPROPERTYEX function. 只有在中斷連接時,才會隱含地取消配置資料指標。The cursor is implicitly deallocated only at disconnect. 如需詳細資訊,請參閱 DECLARE CURSORFor more information, see DECLARE CURSOR.

<db_encryption_option> ::=<db_encryption_option> ::=

控制資料庫加密狀態。Controls the database encryption state.

ENCRYPTION { ON | OFF }ENCRYPTION { ON | OFF }
設定資料庫要加密 (ON) 或是不要加密 (OFF)。Sets the database to be encrypted (ON) or not encrypted (OFF). 如需資料庫加密的詳細資訊,請參閱透明資料加密Azure SQL Database 的透明資料加密For more information about database encryption, see Transparent Data Encryption, and Transparent Data Encryption with Azure SQL Database.

在資料庫層級啟用加密時,所有的檔案群組都會加密。When encryption is enabled at the database level, all file groups will be encrypted. 任何新檔案群組都會繼承加密的屬性。Any new file groups will inherit the encrypted property. 如果資料庫中有任何檔案群組設定為 READ ONLY,則資料庫加密作業將會失敗。If any file groups in the database are set to READ ONLY, the database encryption operation will fail.

您可以使用 sys.dm_database_encryption_keys 動態管理檢視來查看資料庫的加密狀態。You can see the encryption state of the database by using the sys.dm_database_encryption_keys dynamic management view.

<db_update_option> ::=<db_update_option> ::=

控制是否允許更新資料庫。Controls whether updates are allowed on the database.

READ_ONLYREAD_ONLY
使用者可以從資料庫中讀取資料,但無法加以修改。Users can read data from the database but not modify it.

注意

如果要提高查詢的效能,請先更新統計資料,再將資料庫設為 READ_ONLY。To improve query performance, update statistics before setting a database to READ_ONLY. 如果資料庫設為 READ_ONLY 之後,還需要其他的統計資料,Database EngineDatabase Engine 將會在 tempdb 中建立統計資料。If additional statistics are needed after a database is set to READ_ONLY, the Database EngineDatabase Engine will create statistics in tempdb. 如需唯讀資料庫統計資料的詳細資訊,請參閱統計資料For more information about statistics for a read-only database, see Statistics.

READ_WRITEREAD_WRITE
資料庫可以執行讀寫作業。The database is available for read and write operations.

若要變更這個狀態,您必須具有資料庫的獨佔存取權。To change this state, you must have exclusive access to the database.

<db_user_access_option> ::=<db_user_access_option> ::=

控制使用者對資料庫的存取權。Controls user access to the database.

RESTRICTED_USERRESTRICTED_USER
只允許 db_owner 固定資料庫角色與 dbcreatorsysadmin 固定伺服器角色的成員連線到資料庫,但不限制其數目。Allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but doesn't limit their number. 在 ALTER DATABASE 陳述式的 termination 子句所指定的時間範圍中,會中斷資料庫的所有連接。All connections to the database are disconnected in the timeframe specified by the termination clause of the ALTER DATABASE statement. 在資料庫進入 RESTRICTED_USER 狀態之後,不合格使用者的連接嘗試都會遭到拒絕。After the database has transitioned to the RESTRICTED_USER state, connection attempts by unqualified users are refused. RESTRICTED_USER 無法使用 SQL Database 受控執行個體來修改。RESTRICTED_USER can't be modified with SQL Database managed instance.

MULTI_USERMULTI_USER
允許所有具備適當權限來連接資料庫的使用者。All users that have the appropriate permissions to connect to the database are allowed.

您可以檢查 sys.databases 目錄檢視中 user_access 資料行或 DATABASEPROPERTYEX 函式的 UserAccess 屬性來判斷這個選項的狀態。You can determine this option's status by examining the user_access column in the sys.databases catalog view or the UserAccess property of the DATABASEPROPERTYEX function.

<delayed_durability_option> ::=<delayed_durability_option> ::=

控制交易是否認可完全持久或延遲的持久。Controls whether transactions commit fully durable or delayed durable.

DISABLEDDISABLED
接在 SET DISABLED 後面的所有交易都是完全持久。All transactions following SET DISABLED are fully durable. 在不可部分完成的區塊或 Commit 陳述式中設定的任何持久性選項都會被忽略。Any durability options set in an atomic block or commit statement are ignored.

ALLOWEDALLOWED
接在 SET ALLOWED 後面的所有交易都是完全持久或延遲的持久,視不可部分完成的區塊或 Commit 陳述式中設定的持久性選項而定。All transactions following SET ALLOWED are either fully durable or delayed durable, depending upon the durability option set in the atomic block or commit statement.

FORCED 接在 SET FORCED 後面的所有交易都是延遲持久。FORCED All transactions following SET FORCED are delayed durable. 在不可部分完成的區塊或 Commit 陳述式中設定的任何持久性選項都會被忽略。Any durability options set in an atomic block or commit statement are ignored.

<PARAMETERIZATION_option> ::=<PARAMETERIZATION_option> ::=

控制參數化選項。Controls the parameterization option.

PARAMETERIZATION { SIMPLE | FORCED }PARAMETERIZATION { SIMPLE | FORCED }
SIMPLESIMPLE
根據資料庫的預設行為,將查詢參數化。Queries are parameterized based on the default behavior of the database.

FORCEDFORCED
SQL ServerSQL Server 會將資料庫中的所有查詢參數化。parameterizes all queries in the database.

您可以檢查 sys.databases 目錄檢視中 is_parameterization_forced 資料行來判斷此選項的目前設定。The current setting of this option can be determined by examining the is_parameterization_forced column in the sys.databases catalog view.

<query_store_options> ::=<query_store_options> ::=

ON | OFF | CLEAR [ ALL ]ON | OFF | CLEAR [ ALL ]
控制是否在此資料庫中啟用查詢存放區,且控制查詢存放區內容的移除。Controls whether the Query Store is enabled in this database, and also controls removing the contents of the Query Store.

ONON
啟用查詢存放區。Enables the Query Store.

OFFOFF
停用查詢存放區。Disables the Query Store. 這是預設值。This is the default value.

CLEARCLEAR
移除查詢存放區的內容。Remove the contents of the Query Store.

OPERATION_MODEOPERATION_MODE
描述查詢存放區的作業模式。Describes the operation mode of the Query Store. 有效值為 READ_ONLY 和 READ_WRITE。Valid values are READ_ONLY and READ_WRITE. 在 READ_WRITE 模式中,查詢存放區會收集並保存查詢計劃和執行階段執行統計資料資訊。In READ_WRITE mode, the Query Store collects and persists query plan and runtime execution statistics information. 在 READ_ONLY 模式中,可以從查詢存放區讀取資訊,但不會新增資訊。In READ_ONLY mode, information can be read from the Query Store, but new information isn't added. 如果查詢存放區所配置的最大空間已用盡,則查詢存放區會將作業模式變更為 READ_ONLY。If the maximum allocated space of the Query Store has been exhausted, the Query Store will change is operation mode to READ_ONLY.

CLEANUP_POLICYCLEANUP_POLICY
描述查詢存放區的資料保留原則。Describes the data retention policy of the Query Store. STALE_QUERY_THRESHOLD_DAYS 會決定在查詢存放區中保留查詢資訊的天數。STALE_QUERY_THRESHOLD_DAYS determines the number of days for which the information for a query is kept in the Query Store. STALE_QUERY_THRESHOLD_DAYS 的類型為 bigintSTALE_QUERY_THRESHOLD_DAYS is type bigint.

DATA_FLUSH_INTERVAL_SECONDSDATA_FLUSH_INTERVAL_SECONDS
決定將寫入查詢存放區之資料保存到磁碟的頻率。Determines the frequency at which data written to the Query Store is persisted to disk. 為了獲得最佳效能,查詢存放區所收集的資料會以非同步方式寫入磁碟。To optimize for performance, data collected by the Query Store is asynchronously written to the disk. 此非同步傳輸發生的頻率是使用 DATA_FLUSH_INTERVAL_SECONDS 引數所設定。The frequency at which this asynchronous transfer occurs is configured by using the DATA_FLUSH_INTERVAL_SECONDS argument. DATA_FLUSH_INTERVAL_SECONDS 的類型為 bigintDATA_FLUSH_INTERVAL_SECONDS is type bigint.

MAX_STORAGE_SIZE_MBMAX_STORAGE_SIZE_MB
決定配置給查詢存放區的空間。Determines the space allocated to the Query Store. MAX_STORAGE_SIZE_MB 的類型為 bigintMAX_STORAGE_SIZE_MB is type bigint.

INTERVAL_LENGTH_MINUTESINTERVAL_LENGTH_MINUTES
決定執行階段執行統計資料彙總至查詢存放區的時間間隔。Determines the time interval at which runtime execution statistics data is aggregated into the Query Store. 若要將空間使用量最佳化,在執行階段統計資料存放區中的執行階段執行統計資料會透過固定的時段彙總。To optimize for space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed time window. 這個固定的時段是使用 INTERVAL_LENGTH_MINUTES 引數所設定。This fixed time window is configured by using the INTERVAL_LENGTH_MINUTES argument. INTERVAL_LENGTH_MINUTES 的類型為 bigintINTERVAL_LENGTH_MINUTES is type bigint.

SIZE_BASED_CLEANUP_MODESIZE_BASED_CLEANUP_MODE
控制當總資料量接近大小上限時,是否將自動啟用清除。Controls whether cleanup will be automatically activated when the total amount of data gets close to maximum size.

OFFOFF
不會自動啟用以大小為依據的清除。Size-based cleanup won't be automatically activated.

AUTOAUTO
當磁碟上的大小達到 90% 的 max_storage_size_mb 時,就會自動啟用以大小為依據的清除。Size-based cleanup will be automatically activated when size on disk reaches 90% of max_storage_size_mb. 以大小為依據之清除會先移除成本最高和最舊的查詢。Size-based cleanup removes the least expensive and oldest queries first. 它會在達到 max_storage_size_mb 的大約 80% 處停止。It stops at approximately 80% of max_storage_size_mb. 這是預設組態值。This is the default configuration value.

SIZE_BASED_CLEANUP_MODE 的類型為 nvarcharSIZE_BASED_CLEANUP_MODE is type nvarchar.

QUERY_CAPTURE_MODEQUERY_CAPTURE_MODE
指定目前使用中的查詢擷取模式。Designates the currently active query capture mode.

ALLALL
擷取所有查詢。All queries are captured.

AUTOAUTO
根據執行計數和資源耗用量擷取相關的查詢。Capture relevant queries based on execution count and resource consumption. 這是 Azure SQL DatabaseAzure SQL Database 的預設組態值。This is the default configuration value for Azure SQL DatabaseAzure SQL Database.

NONE
停止擷取新的查詢。Stop capturing new queries. 查詢存放區將會繼續收集已擷取查詢的編譯和執行階段統計資料。The Query Store will continue to collect compile and runtime statistics for queries that were captured already. 因為您可能會錯過擷取重要查詢,請小心使用此設定。Use this configuration with caution since you may miss capturing important queries.

QUERY_CAPTURE_MODE 的類型為 nvarcharQUERY_CAPTURE_MODE is type nvarchar.

max_plans_per_queryMAX_PLANS_PER_QUERY
表示維護每個查詢計劃的大數目的整數。An integer representing the maximum number of plans maintained for each query. 預設值為 200。Default is 200.

<snapshot_option> ::=<snapshot_option> ::=

決定交易隔離等級。Determines the transaction isolation level.

ALLOW_SNAPSHOT_ISOLATION { ON | OFF }ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
ONON
在資料庫層級啟用快照集選項。Enables the Snapshot option at the database level. 啟用時,即使沒有交易使用快照隔離,DML 陳述式也會開始產生資料列版本。When it's enabled, DML statements start generating row versions even when no transaction uses Snapshot Isolation. 啟用此選項之後,交易可以指定 SNAPSHOT 交易隔離等級。After this option is enabled, transactions can specify the SNAPSHOT transaction isolation level. 當交易執行的隔離等級是 SNAPSHOT 時,所有陳述式都會見到在交易開頭便存在的資料快照集。When a transaction runs at the SNAPSHOT isolation level, all statements see a snapshot of data as it exists at the start of the transaction. 如果執行 SNAPSHOT 隔離等級的交易存取多個資料庫中的資料,此時所有資料庫中的 ALLOW_SNAPSHOT_ISOLATION 都必須設為 ON,或是每當 FROM 子句參考 ALLOW_SNAPSHOT_ISOLATION 是 OFF 的資料庫中的資料表時,交易中的每個陳述式都必須使用鎖定提示。If a transaction running at the SNAPSHOT isolation level accesses data in multiple databases, either ALLOW_SNAPSHOT_ISOLATION must be set to ON in all the databases, or each statement in the transaction must use locking hints on any reference in a FROM clause to a table in a database where ALLOW_SNAPSHOT_ISOLATION is OFF.

OFFOFF
在資料庫層級關閉快照集選項。Turns off the Snapshot option at the database level. 交易無法指定 SNAPSHOT 交易隔離等級。Transactions can't specify the SNAPSHOT transaction isolation level.

當您將 ALLOW_SNAPSHOT_ISOLATION 設為新狀態 (從 ON 設成 OFF,或從 OFF 設成 ON) 時,在認可資料庫中的所有現有交易之前,ALTER DATABASE 並不會將控制權傳回呼叫端。When you set ALLOW_SNAPSHOT_ISOLATION to a new state (from ON to OFF, or from OFF to ON), ALTER DATABASE doesn't return control to the caller until all existing transactions in the database are committed. 如果資料庫已在 ALTER DATABASE 陳述式所指定的狀態中,控制權會立即傳回呼叫端。If the database is already in the state specified in the ALTER DATABASE statement, control is returned to the caller immediately. 如果 ALTER DATABASE 陳述式並沒有很快傳回,請使用 sys.dm_tran_active_snapshot_database_transactions 來判斷是否有長期執行的交易。If the ALTER DATABASE statement doesn't return quickly, use sys.dm_tran_active_snapshot_database_transactions to determine whether there are long-running transactions. 如果取消了 ALTER DATABASE 陳述式,資料庫會保留在 ALTER DATABASE 啟動時的狀態中。If the ALTER DATABASE statement is canceled, the database remains in the state it was in when ALTER DATABASE was started. sys.databases 目錄檢視指出資料庫中快照集隔離交易的狀態。The sys.databases catalog view indicates the state of snapshot-isolation transactions in the database. 如果 snapshot_isolation_state_desc = IN_TRANSITION_TO_ON,ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF 會暫停六秒,然後重試作業。If snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF will pause six seconds and retry the operation.

如果資料庫是 OFFLINE,則您無法變更 ALLOW_SNAPSHOT_ISOLATION 的狀態。You can't change the state of ALLOW_SNAPSHOT_ISOLATION if the database is OFFLINE.

如果您在 READ_ONLY 資料庫中設定 ALLOW_SNAPSHOT_ISOLATION,資料庫後來又設為 READ_WRITE,會保留這個設定。If you set ALLOW_SNAPSHOT_ISOLATION in a READ_ONLY database, the setting will be kept if the database is later set to READ_WRITE.

您可以變更 master、model、msdb 和 tempdb 等資料庫的 ALLOW_SNAPSHOT_ISOLATION 設定。You can change the ALLOW_SNAPSHOT_ISOLATION settings for the master, model, msdb, and tempdb databases. 如果您變更 tempdb 的設定,每次停止和重新啟動 Database EngineDatabase Engine 執行個體時,會保留這個設定。The setting is kept every time the instance of the Database EngineDatabase Engine is stopped and restarted if you change the setting for tempdb. 如果您變更模型的設定,除了 tempdb 以外,任何新建資料庫都會以這個設定為預設值。If you change the setting for model, that setting becomes the default for any new databases that are created, except for tempdb.

依預設,master 和 msdb 資料庫的這個選項是 ON。The option is ON, by default, for the master and msdb databases.

您可以檢查 sys.databases 目錄檢視中 snapshot_isolation_state 資料行來判斷此選項的目前設定。The current setting of this option can be determined by examining the snapshot_isolation_state column in the sys.databases catalog view.

READ_COMMITTED_SNAPSHOT { ON | OFF }READ_COMMITTED_SNAPSHOT { ON | OFF }
ONON
啟用資料庫層級的 [讀取認可快照集] 選項。Enables the Read-Committed Snapshot option at the database level. 啟用時,即使沒有交易使用快照隔離,DML 陳述式也會開始產生資料列版本。When it's enabled, DML statements start generating row versions even when no transaction uses Snapshot Isolation. 啟用此選項之後,指定 READ COMMITTED 隔離等級的交易即會使用資料列版本設定,而不是鎖定。After this option is enabled, the transactions specifying the READ COMMITTED isolation level use row versioning instead of locking. 在 READ COMMITTED 隔離等級執行交易時,所有陳述式都會看到資料的快照,與陳述式開始時所存在的資料一樣。All statements see a snapshot of data as it exists at the start of the statement when a transaction runs at the READ COMMITTED isolation level.

OFFOFF
在資料庫層級關閉 [讀取認可快照集] 選項。Turns off the Read-Committed Snapshot option at the database level. 指定 READ COMMITTED 隔離等級的交易會使用鎖定。Transactions specifying the READ COMMITTED isolation level use locking.

若要設定 READ_COMMITTED_SNAPSHOT 為 ON 或 OFF,除了執行 ALTER DATABASE 命令的連線之外,不能有任何使用中資料庫連線。To set READ_COMMITTED_SNAPSHOT to ON or OFF, there must be no active connections to the database except for the connection running the ALTER DATABASE command. 不過,資料庫不一定要處於單一使用者模式。However, the database doesn't have to be in single-user mode. 當資料庫是 OFFLINE 時,您無法變更這個選項的狀態。You can't change the state of this option when the database is OFFLINE.

如果您在 READ_ONLY 資料庫中設定 READ_COMMITTED_SNAPSHOT,則當資料庫後來又設為 READ_WRITE 時,會保留這個設定。If you set READ_COMMITTED_SNAPSHOT in a READ_ONLY database, the setting will be kept when the database is later set to READ_WRITE.

master、tempdb 或 msdb 系統資料庫的 READ_COMMITTED_SNAPSHOT 不能設為 ON。READ_COMMITTED_SNAPSHOT can't be turned ON for the master, tempdb, or msdb system databases. 如果您變更 model 的設定,除了 tempdb 以外,這項設定會成為任何新建資料庫的預設值。If you change the setting for model, that setting becomes the default for any new databases created, except for tempdb.

您可以檢查 sys.databases 目錄檢視中 is_read_committed_snapshot_on 資料行來判斷此選項的目前設定。The current setting of this option can be determined by examining the is_read_committed_snapshot_on column in the sys.databases catalog view.

警告

使用 DURABILITY = SCHEMA_ONLY, 和 READ_COMMITTED_SNAPSHOT 建立的資料表,在使用 ALTER DATABASE 進行變更時, 資料表中的資料會遺失。When a table is created with DURABILITY = SCHEMA_ONLY, and READ_COMMITTED_SNAPSHOT is subsequently changed using ALTER DATABASE, data in the table will be lost.

MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT { ON | OFF }MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT { ON | OFF }
ONON
當交易隔離等級設定為 SNAPSHOT 以下的任何隔離等級時,記憶體最佳化資料表上所有解譯的 Transact-SQLTransact-SQL 作業都會在 SNAPSHOT 隔離下執行。When the transaction isolation level is set to any isolation level lower than SNAPSHOT, all interpreted Transact-SQLTransact-SQL operations on memory-optimized tables are run under SNAPSHOT isolation. SNAPSHOT 以下的隔離等級範例包括 READ COMMITTED 或 READ UNCOMMITTED。Examples of isolation levels lower than snapshot are READ COMMITTED or READ UNCOMMITTED. 不論是在工作階段層級明確設定交易隔離等級,或隱含使用預設值,都會執行這些作業。These operations run whether the transaction isolation level is set explicitly at the session level, or the default is used implicitly.

OFFOFF
不會為經記憶體最佳化資料表上解譯的 Transact-SQLTransact-SQL 作業提高交易隔離等級。Doesn't elevate the transaction isolation level for interpreted Transact-SQLTransact-SQL operations on memory-optimized tables.

如果資料庫是 OFFLINE,則您無法變更 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 的狀態。You can't change the state of MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT if the database is OFFLINE.

預設值是 OFF。The default value is OFF.

您可以檢查 sys.databases 目錄檢視中 is_memory_optimized_elevate_to_snapshot_on 資料行來判斷此選項的目前設定。The current setting of this option can be determined by examining the is_memory_optimized_elevate_to_snapshot_on column in the sys.databases catalog view.

<sql_option> ::=<sql_option> ::=

控制資料庫層級的 ANSI 合規性選項。Controls the ANSI compliance options at the database level.

ANSI_NULL_DEFAULT { ON | OFF }ANSI_NULL_DEFAULT { ON | OFF }
決定未在 CREATE TABLE 或 ALTER TABLE 陳述式中明確定義 Null 屬性之資料行或 CLR 使用者定義型別的預設值 (NULL 或 NOT NULL)。Determines the default value, NULL or NOT NULL, of a column or CLR user-defined type for which the nullability isn't explicitly defined in CREATE TABLE or ALTER TABLE statements. 條件約束所定義的資料行會遵循條件約束規則,不論這個設定可能為何。Columns that are defined with constraints follow constraint rules whatever this setting may be.

ONON
預設值是 NULL。The default value is NULL.

OFFOFF
預設值是 NOT NULL。The default value is NOT NULL.

利用 SET 陳述式來設定的連接層級設定會覆寫 ANSI_NULL_DEFAULT 的預設資料庫層級設定。Connection-level settings that are set by using the SET statement override the default database-level setting for ANSI_NULL_DEFAULT. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將工作階段的 ANSI_NULL_DEFAULT 設定為 ON。ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULL_DEFAULT to ON for the session, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET ANSI_NULL_DFLT_ONFor more information, see SET ANSI_NULL_DFLT_ON.

對於 ANSI 相容性而言,將資料庫選項 ANSI_NULL_DEFAULT 設為 ON 會將資料庫預設值改成 NULL。For ANSI compatibility, setting the database option ANSI_NULL_DEFAULT to ON changes the database default to NULL.

您可以檢查 sys.databases 目錄檢視中 is_ansi_null_default_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_ansi_null_default_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAnsiNullDefault 屬性來判斷狀態。You can also determine the status by examining the IsAnsiNullDefault property of the DATABASEPROPERTYEX function.

ANSI_NULLS { ON | OFF }ANSI_NULLS { ON | OFF }
ONON
所有對於 Null 值的比較,都會得出 UNKNOWN。All comparisons to a null value evaluate to UNKNOWN.

OFFOFF
比較非 UNICODE 值和 Null 值,如果兩個值都是 NULL,便會得出 TRUE。Comparisons of non-UNICODE values to a null value evaluate to TRUE if both values are NULL.

重要

在未來的 SQL ServerSQL Server 版本中,ANSI_NULLS 一律為 ON,而且明確將此選項設定為 OFF 的任何應用程式都會產生錯誤。In a future version of SQL ServerSQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will produce an error. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

利用 SET 陳述式來設定的連接層級設定會覆寫 ANSI_NULLS 的預設資料庫設定。Connection-level settings that are set by using the SET statement override the default database setting for ANSI_NULLS. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將工作階段的 ANSI_NULLS 設定為 ON。ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULLS to ON for the session, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET ANSI_NULLSFor more information, see SET ANSI_NULLS.

重要

當您建立或變更計算資料行索引或索引檢視表時,SET ANSI_NULLS 也必須設為 ON。SET ANSI_NULLS also must be set to ON when you create or make changes to indexes on computed columns or indexed views.

您可以檢查 sys.databases 目錄檢視中 is_ansi_nulls_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_ansi_nulls_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAnsiNullsEnabled 屬性來判斷狀態。You can also determine the status by examining the IsAnsiNullsEnabled property of the DATABASEPROPERTYEX function.

ANSI_PADDING { ON | OFF }ANSI_PADDING { ON | OFF }
ONON
轉換字串前先填補至相同的長度。Strings are padded to the same length before conversion. 也會填補到相同的長度,再插入 varcharnvarchar 資料類型。Also padded to the same length before inserting to a varchar or nvarchar data type.

OFFOFF
varcharnvarchar 資料行會插入字元值的尾端空格。Inserts trailing blanks in character values into varchar or nvarchar columns. 已插入 varbinary 資料行的二進位值尾端零也會保留。Also leaves trailing zeros in binary values that are inserted into varbinary columns. 值不會填補到資料行的長度。Values aren't padded to the length of the column.

當指定 OFF 時,這個設定只會影響新資料行的定義。When OFF is specified, this setting affects only the definition of new columns.

重要

在未來的 SQL ServerSQL Server 版本中,ANSI_PADDING 一律為 ON,而且明確將此選項設定為 OFF 的應用程式將會產生錯誤。In a future version of SQL ServerSQL Server, ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature. 我們建議您一律將 ANSI_PADDING 設為 ON。We recommend that you always set ANSI_PADDING to ON. 當您建立或操作計算資料行索引或索引檢視表時,ANSI_PADDING 也必須是 ON。ANSI_PADDING must be ON when you create or manipulate indexes on computed columns or indexed views.

當 ANSI_PADDING 設定為 ON 時,允許 Null 的 char(n)binary(n) 資料行會填補到資料行長度。char(n) and binary(n) columns that allow for nulls are padded to the column length when ANSI_PADDING is set to ON. 當 ANSI_PADDING 為 OFF 時,則會修剪尾端空格和尾端零。Trailing blanks and zeros are trimmed when ANSI_PADDING is OFF. 不允許 Null 的 char(n)binary(n) 資料行一律會填補到資料行的長度。char(n) and binary(n) columns that don't allow nulls are always padded to the length of the column.

利用 SET 陳述式來設定的連接層級設定會覆寫 ANSI_PADDING 的預設資料庫層級設定。Connection-level settings that are set by using the SET statement override the default database-level setting for ANSI_PADDING. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將工作階段的 ANSI_PADDING 設定為 ON。ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_PADDING to ON for the session, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET ANSI_PADDINGFor more information, see SET ANSI_PADDING.

您可以檢查 sys.databases 目錄檢視中 is_ansi_padding_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_ansi_padding_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAnsiPaddingEnabled 屬性來判斷狀態。You can also determine the status by examining the IsAnsiPaddingEnabled property of the DATABASEPROPERTYEX function.

ANSI_WARNINGS { ON | OFF }ANSI_WARNINGS { ON | OFF }
ONON
如果發生「除以零」之類的狀況,便會發出錯誤或警告。Errors or warnings are issued when conditions such as divide-by-zero occur. 當彙總函式中出現 Null 值時,也會發出錯誤或警告。Errors and warnings are also issued when null values appear in aggregate functions.

OFFOFF
當發生除以零之類的情況時,不會產生警告,但會傳回 NULL 值。No warnings are raised and null values are returned when conditions such as divide-by-zero occur.

重要

當您建立或變更計算資料行索引或索引檢視表時,SET ANSI_WARNINGS 必須設為 ON。SET ANSI_WARNINGS must be set to ON when you create or make changes to indexes on computed columns or indexed views.

利用 SET 陳述式來設定的連接層級設定會覆寫 ANSI_WARNINGS 的預設資料庫設定。Connection-level settings that are set by using the SET statement override the default database setting for ANSI_WARNINGS. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將工作階段的 ANSI_WARNINGS 設定為 ON。ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_WARNINGS to ON for the session, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET ANSI_WARNINGSFor more information, see SET ANSI_WARNINGS.

您可以檢查 sys.databases 目錄檢視中 is_ansi_warnings_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_ansi_warnings_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAnsiWarningsEnabled 屬性來判斷狀態。You can also determine the status by examining the IsAnsiWarningsEnabled property of the DATABASEPROPERTYEX function.

ARITHABORT { ON | OFF }ARITHABORT { ON | OFF }
ONON
在查詢執行期間,當發生溢位或除以零的錯誤時,查詢會結束。A query is ended when an overflow or divide-by-zero error occurs during query execution.

OFFOFF
當發生這些錯誤之一時,畫面上會顯示警告訊息。A warning message is displayed when one of these errors occurs. 即使顯示警告,查詢、批次或交易還是會繼續處理,如同未發生任何錯誤一樣。The query, batch, or transaction continues to process as if no error occurred even if a warning is displayed.

重要

當您建立或變更計算資料行索引或索引檢視表時,SET ARITHABORT 必須設為 ON。SET ARITHABORT must be set to ON when you create or make changes to indexes on computed columns or indexed views.

您可以檢查 sys.databases 目錄檢視中 is_arithabort_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_arithabort_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsArithmeticAbortEnabled 屬性來判斷狀態。You can also determine the status by examining the IsArithmeticAbortEnabled property of the DATABASEPROPERTYEX function.

COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 }COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 }
如需詳細資訊,請參閱 ALTER DATABASE 相容性層級For more information, see ALTER DATABASE Compatibility Level.

CONCAT_NULL_YIELDS_NULL { ON | OFF }CONCAT_NULL_YIELDS_NULL { ON | OFF }
ONON
當任何一個運算元是 NULL 時,串連作業的結果都會是 NULL。The result of a concatenation operation is NULL when either operand is NULL. 例如,串連字元字串 "This is" 和 NULL 會得出 NULL 值,而不是 "This is" 值。For example, concatenating the character string "This is" and NULL causes the value NULL, instead of the value "This is".

OFFOFF
將 Null 值當作空白字元字串來處理。The null value is treated as an empty character string.

重要

當您建立或變更計算資料行索引或索引檢視表時,CONCAT_NULL_YIELDS_NULL 也必須設為 ON。CONCAT_NULL_YIELDS_NULL must be set to ON when you create or make changes to indexes on computed columns or indexed views.

重要

在未來的 SQL ServerSQL Server 版本中,CONCAT_NULL_YIELDS_NULL 一律為 ON,而且明確將此選項設定為 OFF 的應用程式將會產生錯誤。In a future version of SQL ServerSQL Server, CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will produce an error. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

利用 SET 陳述式來設定的連接層級設定會覆寫 CONCAT_NULL_YIELDS_NULL 的預設資料庫設定。Connection-level settings that are set by using the SET statement override the default database setting for CONCAT_NULL_YIELDS_NULL. 根據預設,當連接到 SQL ServerSQL Server 執行個體時,ODBC 和 OLE DB 用戶端會發出連接層級的 SET 陳述式,將工作階段的 CONCAT_NULL_YIELDS_NULL 設為 ON。By default, ODBC and OLE DB clients issue a connection-level SET statement setting CONCAT_NULL_YIELDS_NULL to ON for the session when connecting to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET CONCAT_NULL_YIELDS_NULLFor more information, see SET CONCAT_NULL_YIELDS_NULL.

您可以檢查 sys.databases 目錄檢視中 is_concat_null_yields_null_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_concat_null_yields_null_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsNullConcat 屬性來判斷狀態。You can also determine the status by examining the IsNullConcat property of the DATABASEPROPERTYEX function.

QUOTED_IDENTIFIER { ON | OFF }QUOTED_IDENTIFIER { ON | OFF }
ONON
可以使用雙引號來含括分隔識別碼。Double quotation marks can be used to enclose delimited identifiers.

用雙引號來分隔的所有字串都會解譯為物件識別碼。All strings delimited by double quotation marks are interpreted as object identifiers. 附加引號的識別碼不需要遵循 Transact-SQLTransact-SQL 的識別碼規則。Quoted identifiers don't have to follow the Transact-SQLTransact-SQL rules for identifiers. 它們可以是關鍵字,也可以包括 Transact-SQLTransact-SQL 識別碼不允許的字元。They can be keywords and can include characters not allowed in Transact-SQLTransact-SQL identifiers. 如果單引號 (') 是文字字串的一部分,您可以用雙引號 (") 來表示它。If a single quotation mark (') is part of the literal string, it can be represented by double quotation marks (").

OFFOFF
識別碼不能放在引號中,且必須遵循所有 Transact-SQLTransact-SQL 識別碼規則。Identifiers can't be in quotation marks and must follow all Transact-SQLTransact-SQL rules for identifiers. 文字可以用單引號或雙引號來分隔。Literals can be delimited by either single or double quotation marks.

SQL ServerSQL Server 也允許用方括號 ([ ]) 來分隔識別碼。also allows for identifiers to be delimited by square brackets ([ ]). 不論 QUOTED_IDENTIFIER 設定為何,用方括弧括住的識別項一律可以使用。Bracketed identifiers can always be used, whatever the QUOTED_IDENTIFIER setting is. 如需詳細資訊,請參閱< Database Identifiers>。For more information, see Database Identifiers.

當建立資料表時,一律會在資料表的中繼資料中,將 QUOTED IDENTIFIER 選項儲存成 ON。When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the metadata of the table. 即使建立資料表時,將選項設定為 OFF,也會儲存此選項。The option is stored even if the option is set to OFF when the table is created.

利用 SET 陳述式來設定的連接層級設定會覆寫 QUOTED_IDENTIFIER 的預設資料庫設定。Connection-level settings that are set by using the SET statement override the default database setting for QUOTED_IDENTIFIER. 根據預設,ODBC 和 OLE DB 用戶端會發出連線層級的 SET 陳述式,將 QUOTED_IDENTIFIER 設定為 ON。ODBC and OLE DB clients issue a connection-level SET statement setting QUOTED_IDENTIFIER to ON, by default. 當您連線到 SQL ServerSQL Server 執行個體時,用戶端會執行此陳述式。The clients run the statement when you connect to an instance of SQL ServerSQL Server. 如需詳細資訊,請參閱 SET QUOTED_IDENTIFIERFor more information, see SET QUOTED_IDENTIFIER.

您可以檢查 sys.databases 目錄檢視中 is_quoted_identifier_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_quoted_identifier_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsQuotedIdentifiersEnabled 屬性來判斷狀態。You can also determine the status by examining the IsQuotedIdentifiersEnabled property of the DATABASEPROPERTYEX function.

NUMERIC_ROUNDABORT { ON | OFF }NUMERIC_ROUNDABORT { ON | OFF }
ONON
當運算式中遺失有效位數時,系統會產生錯誤。An error is generated when loss of precision occurs in an expression.

OFFOFF
遺失有效位數並不會產生錯誤訊息,且結果會四捨五入到儲存結果的資料行或變數有效位數。Loss of precision doesn't generate an error message, and the result is rounded to the precision of the column or variable storing the result.

重要

當您建立或變更計算資料行索引或索引檢視表時,NUMERIC_ROUNDABORT 必須設為 OFF。NUMERIC_ROUNDABORT must be set to OFF when you create or make changes to indexes on computed columns or indexed views.

您可以檢查 sys.databases 目錄檢視中 is_numeric_roundabort_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_numeric_roundabort_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsNumericRoundAbortEnabled 屬性來判斷狀態。You can also determine the status by examining the IsNumericRoundAbortEnabled property of the DATABASEPROPERTYEX function.

RECURSIVE_TRIGGERS { ON | OFF }RECURSIVE_TRIGGERS { ON | OFF }
ONON
允許遞迴引發 AFTER 觸發程序。Recursive firing of AFTER triggers is allowed.

OFFOFF
您可以檢查 sys.databases 目錄檢視中 is_recursive_triggers_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_recursive_triggers_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsRecursiveTriggersEnabled 屬性來判斷狀態。You can also determine the status by examining the IsRecursiveTriggersEnabled property of the DATABASEPROPERTYEX function.

注意

當 RECURSIVE_TRIGGERS 設為 OFF 時,只防止直接遞迴。Only direct recursion is prevented when RECURSIVE_TRIGGERS is set to OFF. 若要停用間接遞迴,您也必須將巢狀觸發程序伺服器選項設為 0。To disable indirect recursion, you must also set the nested triggers server option to 0.

您可以檢查 sys.databases 目錄檢視中 is_recursive_triggers_on 資料行或 DATABASEPROPERTYEX 函式 IsRecursiveTriggersEnabled 屬性來判斷這個選項的狀態。You can determine this option's status by examining the is_recursive_triggers_on column in the sys.databases catalog view or the IsRecursiveTriggersEnabled property of the DATABASEPROPERTYEX function.

<target_recovery_time_option> ::=<target_recovery_time_option> ::=

為每個資料庫指定間接檢查點的頻率。Specifies the frequency of indirect checkpoints on a per-database basis. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,新資料庫的預設值為 1 分鐘,這表示資料庫將會使用間接檢查點。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) the default value for new databases is 1 minute, which indicates database will use indirect checkpoints. 舊版的預設值為 0,這表示資料庫將使用自動檢查點,其頻率取決於伺服器執行個體的復原間隔設定。For older versions the default is 0, which indicates that the database will use automatic checkpoints, whose frequency depends on the recovery interval setting of the server instance. 對於大多數的系統,MicrosoftMicrosoft 建議使用 1 分鐘。MicrosoftMicrosoft recommends 1 minute for most systems.

TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }TARGET_RECOVERY_TIME =target_recovery_time { SECONDS | MINUTES }
target_recovery_time target_recovery_time
指定萬一發生損毀時,復原指定之資料庫的時間上限。Specifies the maximum bound on the time to recover the specified database in the event of a crash.

SECONDSSECONDS
指出 target_recovery_time 應以秒數表示。Indicates that target_recovery_time is expressed as the number of seconds.

MINUTESMINUTES
指出 target_recovery_time 應以分鐘數表示。Indicates that target_recovery_time is expressed as the number of minutes.

如需間接檢查點的詳細資訊,請參閱資料庫檢查點For more information about indirect checkpoints, see Database Checkpoints.

ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATEROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
指定在指定的秒數之後回復,或是立即回復。Specifies whether to roll back after the specified number of seconds or immediately.

NO_WAITNO_WAIT
指定如果要求的資料庫狀態或選項變更無法立即完成,要求將會失敗。Specifies that the request will fail if the requested database state or option change can't complete immediately. 立即完成表示不等候交易自行認可或回復。Completing immediately means not waiting for transactions to commit or roll back on their own.

設定選項Setting options

若要擷取資料庫選項的目前設定,請使用 sys.databases 目錄檢視或 DATABASEPROPERTYEXTo retrieve current settings for database options, use the sys.databases catalog view or DATABASEPROPERTYEX

設好資料庫選項之後,新設定會立即生效。After you set a database option, the new setting takes effect immediately.

您可以針對所有新建立的資料庫,變更任何一個資料庫選項的預設值。You can change the default values for any one of the database options for all newly created databases. 若要執行這項操作,請變更 model 資料庫中的適當資料庫選項。To do so, change the appropriate database option in the model database.

範例Examples

A.A. 將資料庫設為 READ_ONLYSetting the database to READ_ONLY

將資料庫或檔案群組的狀態改成 READ_ONLY 或 READ_WRITE 時,需要資料庫的獨佔存取權。Changing the state of a database or file group to READ_ONLY or READ_WRITE requires exclusive access to the database. 下列範例會將資料庫設成 RESTRICTED_USER 模式來限制存取。The following example sets the database to RESTRICTED_USER mode to restricted access. 之後,範例會將 AdventureWorks2012AdventureWorks2012 資料庫的狀態設成 READ_ONLY ,並將資料庫的存取權還給所有使用者。The example then sets the state of the AdventureWorks2012AdventureWorks2012 database to READ_ONLY and returns access to the database to all users.

USE master;
GO
ALTER DATABASE [database_name]
SET RESTRICTED_USER;
GO
ALTER DATABASE [database_name]
SET READ_ONLY
GO
ALTER DATABASE [database_name]
SET MULTI_USER;
GO

B.B. 啟用資料庫的快照集隔離Enabling snapshot isolation on a database

下列範例會啟用 AdventureWorks2012AdventureWorks2012 資料庫的快照集隔離架構選項。The following example enables the snapshot isolation framework option for the AdventureWorks2012AdventureWorks2012 database.

USE [database_name];
USE master;
GO
ALTER DATABASE [database_name]
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Check the state of the snapshot_isolation_framework
-- in the database.
SELECT name, snapshot_isolation_state,
    snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'[database_name]';
GO

結果集顯示啟用快照集隔離架構。The result set shows that the snapshot isolation framework is enabled.

NAMEname snapshot_isolation_statesnapshot_isolation_state descriptiondescription
[database_name][database_name] 11 ONON

C.C. 啟用、修改及停用變更追蹤Enabling, modifying, and disabling change tracking

下列範例會啟用 AdventureWorks2012AdventureWorks2012 資料庫的變更追蹤,並將保留週期設定為 2 天。The following example enables change tracking for the AdventureWorks2012AdventureWorks2012 database and sets the retention period to 2 days.

ALTER DATABASE [database_name]
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP = ON, CHANGE_RETENTION = 2 DAYS);

下列範例會示範如何將保留週期變更為 3 天。The following example shows how to change the retention period to 3 days.

ALTER DATABASE [database_name]
SET CHANGE_TRACKING (CHANGE_RETENTION = 3 DAYS);

下列範例會示範如何停用 AdventureWorks2012AdventureWorks2012 資料庫的變更追蹤。The following example shows how to disable change tracking for the AdventureWorks2012AdventureWorks2012 database.

ALTER DATABASE [database_name]
SET CHANGE_TRACKING = OFF;

D.D. 啟用查詢存放區Enabling the Query Store

下列範例會啟用查詢存放區並設定查詢存放區參數。The following example enables the Query Store and configures Query Store parameters.

ALTER DATABASE [database_name]
SET QUERY_STORE = ON
  (  
      OPERATION_MODE = READ_WRITE
    , CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 )
    , DATA_FLUSH_INTERVAL_SECONDS = 900
    , MAX_STORAGE_SIZE_MB = 1024
    , INTERVAL_LENGTH_MINUTES = 60
    );

另請參閱See Also

SQL ServerSQL Server SQL Database
單一資料庫/彈性集區
SQL Database
single database/elastic pool
SQL Database
受控執行個體
SQL Database
managed instance
* SQL 資料
倉儲 *
 
* SQL Data
Warehouse *
 

 

Azure SQL 資料倉儲Azure SQL Data Warehouse

語法Syntax

ALTER DATABASE { database_name }
SET
{
    <optionspec> [ ,...n ]
}
;

<option_spec>::=
{
    <auto_option>
  | <db_encryption_option>
  | <query_store_options>
  | <result_set_caching>
  | <snapshot_option>
}
;

<auto_option> ::=
{
    AUTO_CREATE_STATISTICS { OFF | ON }
}

<db_encryption_option> ::=
{
    ENCRYPTION { ON | OFF }
}

<query_store_option> ::=
{
    QUERY_STORE { OFF |  ON }
}

<result_set_caching_option> ::=
{
    RESULT_SET_CACHING {ON | OFF}
}

<snapshot_option> ::=
{
    READ_COMMITTED_SNAPSHOT {ON | OFF }
}

引數Arguments

database_name database_name
這是要修改之資料庫的名稱。Is the name of the database to be modified.

<auto_option> ::=<auto_option> ::=

控制自動選項。Controls automatic options.

AUTO_CREATE_STATISTICS { ON | OFF }AUTO_CREATE_STATISTICS { ON | OFF }

ONON
查詢最佳化工具會視需要針對查詢述詞中的單一資料行建立統計資料,以便改善查詢計劃和查詢效能。Query Optimizer creates statistics on single columns in query predicates, as necessary, to improve query plans and query performance. 這些單一資料行統計資料是在查詢最佳化工具編譯查詢時所建立。These single-column statistics are created when Query Optimizer compiles queries. 它只會針對尚未成為現有統計資料物件之第一個資料行的資料行建立單一資料行統計資料。The single-column statistics are created only on columns that are not already the first column of an existing statistics object.

預設值是 ON。The default is ON. 我們建議您針對大部分資料庫使用預設設定。We recommend that you use the default setting for most databases.

OFFOFF
查詢最佳化工具不會在編譯查詢時針對查詢述詞中的單一資料行建立統計資料。Query Optimizer doesn't create statistics on single columns in query predicates when it's compiling queries. 將這個選項設定為 OFF 可能會導致次佳查詢計劃並降低查詢效能。Setting this option to OFF can cause suboptimal query plans and degraded query performance.

您可以檢查 sys.databases 目錄檢視中 is_auto_create_stats_on 資料行來判斷這個選項的狀態。You can determine this option's status by examining the is_auto_create_stats_on column in the sys.databases catalog view. 您也可以檢查 DATABASEPROPERTYEX 函式的 IsAutoCreateStatistics 屬性來判斷狀態。You can also determine the status by examining the IsAutoCreateStatistics property of the DATABASEPROPERTYEX function.

如需詳細資訊,請參閱<統計資料>中的<使用資料庫範圍統計資料選項>一節。For more information, see the section "Using the database-wide statistics options" in Statistics.

<db_encryption_option> ::=<db_encryption_option> ::=

控制資料庫加密狀態。Controls the database encryption state.

ENCRYPTION { ON | OFF }ENCRYPTION { ON | OFF }

ONON
將資料庫設為加密。Sets the database to be encrypted.

OFFOFF
將資料庫設為不加密。Sets the database to not be encrypted.

如需資料庫加密的詳細資訊,請參閱<透明資料加密>和<Azure SQL Database 的透明資料加密>。For more information about database encryption, see Transparent Data Encryption, and Transparent Data Encryption with Azure SQL Database.

在資料庫層級啟用加密時,所有的檔案群組都會加密。When encryption is enabled at the database level, all file groups will be encrypted. 任何新檔案群組都會繼承加密的屬性。Any new file groups will inherit the encrypted property. 如果資料庫中有任何檔案群組設定為 READ ONLY,則資料庫加密作業將會失敗。If any file groups in the database are set to READ ONLY, the database encryption operation will fail.

您可以使用 sys.dm_database_encryption_keys 動態管理檢視來查看資料庫的加密狀態,以及加密掃描的狀態。You can see the encryption state of the database as well as the state of the encryption scan by using the sys.dm_database_encryption_keys dynamic management view.

<query_store_option> ::=<query_store_option> ::=

控制此資料倉儲中是否已啟用查詢存放區。Controls whether the Query Store is enabled in this data warehouse.

QUERY_STORE { ON | OFF }QUERY_STORE { ON | OFF }

ONON
啟用查詢存放區。Enables the Query Store.

OFFOFF

停用查詢存放區。Disables the Query Store. OFF 是預設值。OFF is the default value.

注意

針對 SQL 資料倉儲SQL Data Warehouse,您必須從使用者資料庫中執行 ALTER DATABASE SET QUERY_STOREFor SQL 資料倉儲SQL Data Warehouse, you must execute ALTER DATABASE SET QUERY_STORE from the user database. 不支援從另一個資料倉儲執行個體執行陳述式。Executing the statement from another data warehouse instance isn't supported.

<result_set_caching_option> ::= <result_set_caching_option> ::=
適用於:Azure SQL 資料倉儲Applies to: Azure SQL Data Warehouse

控制是否要在資料庫中快取查詢結果。Controls whether query result is cached in the database.

RESULT_SET_CACHING {ON | OFF}RESULT_SET_CACHING {ON | OFF}

ONON
指定從此資料庫傳回的查詢結果集將快取於 Azure SQL 資料倉儲儲存體中。Specifies that query result sets returned from this database will be cached in Azure SQL Data Warehouse storage.

OFFOFF
指定從此資料庫傳回的查詢結果集將不會快取於 Azure SQL 資料倉儲儲存體中。Specifies that query result sets returned from this database will not be cached in Azure SQL Data warehouse storage.

RemarksRemarks

此命令必須在連線到 master 資料庫時執行。This command must be run while connected to the master database. 對此資料庫設定的變更會立即生效。Change to this database setting takes effect immediately. 儲存體費用會以快取查詢結果集的數目計算。Storage costs are incurred by caching query result sets. 停用資料庫的結果快取後,會立即從 Azure SQL 資料倉儲儲存體中刪除先前保存的結果快取。After disabling result caching for a database, previously persisted result cache will immediately be deleted from Azure SQL Data Warehouse storage.

執行此命令來檢查資料庫的結果集快取設定。Run this command to check a database's result set caching configuration. 如果結果集快取已開啟,is_result_set_caching_on 將傳回 1。If result set caching is turned ON, is_result_set_caching_on will return 1.


SELECT name, is_result_set_caching_on FROM sys.databases 
WHERE name = <'Your_Database_Name'>

執行此命令來檢查所執行查詢的結果快取是否有命中。Run this command to check if a query was executed with a result cache hit or miss. 如果快取命中,result_cache_hit 將傳回 1。If there is a cache hit, the result_cache_hit will return 1.


SELECT request_id, command, result_cache_hit FROM sys.dm_pdw_exec_requests 
WHERE request_id = <'Your_Query_Request_ID'>

重要

建立結果集快取以及從快取擷取資料的作業會在資料倉儲執行個體的控制節點上進行。The operations to create result set cache and retrieve data from the cache happen on the control node of a data warehouse instance. 當結果集快取開啟時,執行傳回大型結果集的查詢 (例如,> 1 百萬個資料列) 可能會導致控制節點上的 CPU 使用率過高,並使執行個體的整體查詢回應變慢。When result set caching is turned ON, running queries that return large result set (for example, >1 million rows) can cause high CPU usage on the control node and slow down the overall query response on the instance. 這些查詢通常會在資料探索或 ETL 作業期間使用。Those queries are commonly used during data exploration or ETL operations. 若要避免對控制節點造成壓力並導致效能問題,使用者應該先關閉資料庫的結果集快取,再執行這些類型的查詢。To avoid stressing the control node and cause performance issue, users should turn OFF result set caching on the database before running those types of queries.

如需有關使用結果集快取進行效能調整的詳細資訊,請參閱效能調整指引For details on performance tuning with result set caching, check Performance tuning guidance.

權限Permissions

若要設定 RESULT_SET_CACHING 選項,使用者需要伺服器層級主體登入 (由佈建程序所建立) 或為 dbmanager 資料庫角色的成員。To set the RESULT_SET_CACHING option, a user needs server-level principal login (the one created by the provisioning process) or be a member of the dbmanager database role.

<snapshot_option> ::= <snapshot_option> ::=
適用於:Azure SQL 資料倉儲Applies to: Azure SQL Data Warehouse

控制資料庫的交易隔離等級。Controls the transaction isolation level of a database.

READ_COMMITTED_SNAPSHOT { ON | OFF }READ_COMMITTED_SNAPSHOT { ON | OFF }

ONON
啟用資料庫層級的 READ_COMMITTED_SNAPSHOT 選項。Enables the READ_COMMITTED_SNAPSHOT option at the database level.

OFFOFF
在資料庫層級關閉 READ_COMMITTED_SNAPSHOT 選項。Turn off the READ_COMMITTED_SNAPSHOT option at the database level.

RemarksRemarks

此命令必須在連線到 master 資料庫時執行。This command must be run while connected to the master database. 開啟或關閉使用者資料庫的 READ_COMMITTED_SNAPSHOT,將會終止此資料庫的所有開放連線。Turning READ_COMMITTED_SNAPSHOT ON or OFF for a user database will kill all open connections to this database. 建議您在資料庫維護期間或等到資料庫沒有使用中的連線再進行此變更,除了執行 ALTER DATABSE 命令的連線以外。You may want to make this change during database maintenance window or wait until there's no active connection to the database except for the connection running the ALTER DATABSE command. 資料庫不一定要處於單一使用者模式。The database does not have to be in single-user mode. 不支援變更工作階段層級的 READ_COMMITTED_SNAPSHOT 設定。Changing READ_COMMITTED_SNAPSHOT setting at session level isn't supported. 若要確認資料庫的這項設定,請檢查 sys.databases 中的 is_read_committed_snapshot_on 資料行。To verify this setting for a database, check is_read_committed_snapshot_on column in sys.databases.

在已啟用 READ_COMMITTED_SNAPSHOT 的資料庫中,如果有多個資料版本存在,查詢可能會遭遇較低的效能。In a database with READ_COMMITTED_SNAPSHOT enabled, queries may experience slower performance due to the scan of versions if multiple data versions are present. 長時間開啟的交易也會導致資料庫大小增加。Long-open transactions can also cause an increase in the size of the database. 如果封鎖版本清除的這些交易進行資料變更,就會發生此問題。This issue occurs if there are data changes by these transactions that block version cleanup.

權限Permissions

若要設定 READ_COMMITTED_SNAPSHOT 選項,使用者需要資料庫的 ALTER 權限。To set the READ_COMMITTED_SNAPSHOT option, a user needs ALTER permission on the database.

範例Examples

檢查資料庫的統計資料設定Check statistics setting for a database

SELECT name, is_auto_create_stats_on FROM sys.databases

啟用資料庫的查詢存放區Enable query store for a database

ALTER DATABASE [database_name]
SET QUERY_STORE = ON;

啟用資料庫的結果集快取Enable result set caching for a database

ALTER DATABASE [database_name]
SET RESULT_SET_CACHING ON;

檢查資料庫的結果集快取設定Check result set caching setting for a database

SELECT name, is_result_set_caching_on
FROM sys.databases;

啟用資料庫的 Read_Committed_Snapshot 選項Enable the Read_Committed_Snapshot option for a database

ALTER DATABASE MyDatabase  
SET READ_COMMITTED_SNAPSHOT ON

另請參閱See also