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 数据库
单一数据库/弹性池
SQL Database
single database/elastic pool
SQL 数据库
托管实例
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 ServerSQL 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 Engine 下次重新启动时,数据库在用户尝试使用数据库之前不会重新打开。If so, the database doesn't reopen until a user tries to use the database the next time the 数据库引擎Database 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.

备注

AUTO_CLOSE 选项在包含的数据库或 SQL 数据库SQL Database 中不可用。The AUTO_CLOSE option isn't available in a contained database or on SQL 数据库SQL 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 ServerSQL 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 } ON AUTO_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.

当文件中超过百分之二十五的部分包含未使用的空间时,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 Engine 确认该查询计划引用最新的统计信息。Before running a cached query plan, the 数据库引擎Database 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 Engine 在新查询计划导致性能回归的 Transact-SQLTransact-SQL 查询中自动强制执行上一个已知完好的计划。The 数据库引擎Database Engine automatically forces the last known good plan on the Transact-SQLTransact-SQL queries where new query plan causes performance regressions. 数据库引擎Database Engine通过该强制计划持续监视 Transact-SQLTransact-SQL 查询的查询性能。The 数据库引擎Database Engine continuously monitors query performance of the Transact-SQLTransact-SQL query with the forced plan.

如果性能有所提升,数据库引擎Database Engine将继续使用上一个已知完好的计划。If there are performance gains, the 数据库引擎Database Engine will keep using last known good plan. 如果未检测到性能提升,数据库引擎Database Engine将生成新的查询计划。If performance gains are not detected, the 数据库引擎Database 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 Engine报告由 sys.dm_db_tuning_recommendations 视图中的查询计划更改引起的潜在查询性能回归。The 数据库引擎Database 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 数据库SQL DatabaseApplies to: SQL ServerSQL Server and SQL 数据库SQL 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. 默认保留类型为 DAYS 。The 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 ServerSQL 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. 如果数据库已启用复制、更改数据捕获或更改跟踪功能,则将数据库包含关系设置为 partial 将失败。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. 有关包含的数据库的详细信息,请参阅 Contained DatabasesFor 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 数据库实现透明数据加密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,禁用日志记录,并且仅限 sysadmin 固定服务器角色的成员访问。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 Engine将在 tempdb 中创建统计信息。If additional statistics are needed after a database is set to READ_ONLY, the 数据库引擎Database 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 数据库SQL Database 联合数据库上,将禁用 SET { READ_ONLY | READ_WRITE }。On SQL 数据库SQL 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 语句的终止子句所指定的时间范围,断开所有数据库连接。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 ServerSQL 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 ServerSQL 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 ServerSQL 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. 可以通过提供本地 ID (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 ServerSQL 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 ServerSQL 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 ServerSQL 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 ServerSQL 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 = <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 ServerSQL 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)

控制数据库能否使用混合区为表或索引的前 8 页创建初始页面。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 ServerSQL 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_MODE”,则也会触发强制实施 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 的类型为 bigint 。INTERVAL_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
当磁盘上的大小达到 MAX_STORAGE_SIZE_MB 的 90% 时,将自动激活基于大小的清除 。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.

备注

当查询捕获模式设置为“全部”、“自动”或“自定义”时,始终捕获游标、存储过程中的查询和本机编译的查询。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. ALL 是 SQL ServerSQL ServerSQL 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 的类型为 int 。MAX_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 小时到 7 天。The default is 1 day, and it can be set from 1 hour to seven days. number 的类型为 int 。number 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 的类型为 int 。EXECUTION_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 的类型为 int 。TOTAL_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 ms 的 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 的类型为 int 。TOTAL_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_model 和 recovery_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 Engine 可以检测不完整页。Incomplete pages can be detected by the 数据库引擎Database Engine.

OFFOFF
数据库引擎Database Engine 不能检测不完整页。Incomplete pages can't be detected by the 数据库引擎Database 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. 在读取过程中,即使页头中存在 CHECKSUM 或 TORN_PAGE_DETECTION 值,SQL ServerSQL Server 也不会验证校验和或页撕裂。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:

  • 默认值为 CHECKSUM 。The 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 ServerSQL 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.

若要为表启用 Stretch Database,需要 db_owner 权限。Requires db_owner permission to enable Stretch Database for a table. 若要为数据库启用 Stretch Database,需要 db_ownerCONTROL 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. 消息传递已启动,is_broker_enabled 标志在 sys.databases 目录视图中设置为 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 代理,这样 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. 消息传递已停止,is_broker_enabled 标志在 sys.databases 目录视图中设置为 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 Engine实例时会保留该设置。The setting is kept every time the instance of the 数据库引擎Database Engine is stopped and restarted if you change the setting for tempdb. 如果为 model 更改该设置,则该设置将成为除 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 创建表,随后使用 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 }
适用于SQL ServerSQL ServerSQL 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. 低于快照的隔离级别示例有 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
未定义列的默认值不为 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. 在插入到 varchar 或 nvarchar 数据类型之前,也填充到同一长度。Also padded to the same length before inserting to a varchar or nvarchar data type.

OFFOFF
将字符值中的尾随空格插入 varchar 或 nvarchar 列中。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 IdentifiersFor 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. 若要禁用间接递归触发,还必须将 nested triggers 服务器选项设置为 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 ServerSQL 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 的类型为 int 。target_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. 如果终止子句被忽略,则当数据库中存在任何锁时,ALTER DATABASE 语句将无限期等待。If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely if there's any lock on the database. 只能指定一条终止子句,而且该子句应跟在 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 的类型为 int 。number 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 ServerSQL 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 数据库
单一数据库/弹性池*
 
* SQL Database
single database/elastic pool *
 
SQL 数据库
托管实例
SQL Database
managed instance
SQL 数据
数据仓库
SQL Data
Warehouse

 

Azure SQL 数据库单一数据库/弹性池Azure SQL Database single database/elastic pool

兼容性级别是 SET 选项,但在 ALTER DATABASE 兼容性级别中进行了说明。Compatibility levels are SET options but are described in ALTER DATABASE Compatibility Level.

备注

可以使用 SET Statements 来为当前会话配置很多数据库 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.

当文件中超过百分之二十五的部分包含未使用的空间时,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 Engine 确认该查询计划引用最新的统计信息。Before running a cached query plan, the 数据库引擎Database 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. 如果想要在父服务器上自定义自动优化配置,并让该服务器上的所有数据库继承这些自定义设置,则这特别有用。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 Engine 在新查询计划导致性能回归的 Transact-SQLTransact-SQL 查询中自动强制执行上一个已知完好的计划。The 数据库引擎Database Engine automatically forces the last known good plan on the Transact-SQLTransact-SQL queries where new query plan causes performance regressions. 数据库引擎Database Engine通过该强制计划持续监视 Transact-SQLTransact-SQL 查询的查询性能。The 数据库引擎Database Engine continuously monitors query performance of the Transact-SQLTransact-SQL query with the forced plan. 如果性能有所提升,数据库引擎Database Engine将继续使用上一个已知完好的计划。If there are performance gains, the 数据库引擎Database Engine will keep using last known good plan. 如果未检测到性能提升,数据库引擎Database Engine将生成新的查询计划。If performance gains are not detected, the 数据库引擎Database 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 Engine报告由 sys.dm_db_tuning_recommendations 视图中的查询计划更改引起的潜在查询性能回归。The 数据库引擎Database 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. 默认保留类型为 DAYS 。The 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 数据库实现透明数据加密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 Engine将在 tempdb 中创建统计信息。If additional statistics are needed after a database is set to READ_ONLY, the 数据库引擎Database 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 数据库SQL Database 联合数据库上,将禁用 SET { READ_ONLY | READ_WRITE }。On SQL 数据库SQL 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 语句的终止子句所指定的时间范围内,所有数据库连接都将被断开。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. 不能使用 SQL 数据库托管实例修改 RESTRICTED_USER 。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 ]
控制查询存储是否在此数据库中启用,同时控制是否删除查询存储的内容。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 的类型为 bigint 。INTERVAL_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
AUTO 当磁盘上的大小达到 max_storage_size_mb 的 90% 时,将自动激活基于大小的清除 。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 Engine实例时会保留该设置。The setting is kept every time the instance of the 数据库引擎Database Engine is stopped and restarted if you change the setting for tempdb. 如果为 model 更改该设置,则该设置将成为除 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. 低于快照的隔离级别示例有 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. 在插入到 varchar 或 nvarchar 数据类型之前,也填充到同一长度。Also padded to the same length before inserting to a varchar or nvarchar data type.

OFFOFF
将字符值中的尾随空格插入 varchar 或 nvarchar 列中。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 IdentifiersFor 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. 若要禁用间接递归触发,还必须将 nested triggers 服务器选项设置为 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. 如果终止子句被忽略,则当数据库中存在任何锁时,ALTER DATABASE 语句将无限期等待。If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely if there's any lock on the database. 只能指定一条终止子句,而且该子句应跟在 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 数据库
单一数据库/弹性池
SQL Database
single database/elastic pool
* SQL 数据库
托管实例*
 
* SQL Database
managed instance *
 
SQL 数据
数据仓库
SQL Data
Warehouse

 

Azure SQL 数据库托管实例Azure SQL Database managed instance

兼容性级别是 SET 选项,但在 ALTER DATABASE 兼容性级别中进行了说明。Compatibility levels are SET options but are described in ALTER DATABASE Compatibility Level.

备注

可以使用 SET Statements 来为当前会话配置很多数据库 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.

当文件中超过百分之二十五的部分包含未使用的空间时,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 Engine 确认该查询计划引用最新的统计信息。Before running a cached query plan, the 数据库引擎Database 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 Engine 在新查询计划导致性能回归的 Transact-SQLTransact-SQL 查询中自动强制执行上一个已知完好的计划。The 数据库引擎Database Engine automatically forces the last known good plan on the Transact-SQLTransact-SQL queries where new query plan causes performance regressions. 数据库引擎Database Engine通过该强制计划持续监视 Transact-SQLTransact-SQL 查询的查询性能。The 数据库引擎Database Engine continuously monitors query performance of the Transact-SQLTransact-SQL query with the forced plan. 如果性能有所提升,数据库引擎Database Engine将继续使用上一个已知完好的计划。If there are performance gains, the 数据库引擎Database Engine will keep using last known good plan. 如果未检测到性能提升,数据库引擎Database Engine将生成新的查询计划。If performance gains are not detected, the 数据库引擎Database 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 Engine报告由 sys.dm_db_tuning_recommendations 视图中的查询计划更改引起的潜在查询性能回归。The 数据库引擎Database 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. 默认保留类型为 DAYS 。The 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 数据库实现透明数据加密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 Engine将在 tempdb 中创建统计信息。If additional statistics are needed after a database is set to READ_ONLY, the 数据库引擎Database 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 语句的终止子句所指定的时间范围内,所有数据库连接都将被断开。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. 不能使用 SQL 数据库托管实例修改 RESTRICTED_USER 。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 ]
控制查询存储是否在此数据库中启用,同时控制是否删除查询存储的内容。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 的类型为 bigint 。INTERVAL_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
AUTO 当磁盘上的大小达到 max_storage_size_mb 的 90% 时,将自动激活基于大小的清除 。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 Engine实例时会保留该设置。The setting is kept every time the instance of the 数据库引擎Database Engine is stopped and restarted if you change the setting for tempdb. 如果为 model 更改该设置,则该设置将成为除 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
在数据库级别启用 Read-Committed Snapshot 选项。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
在数据库级别禁用 Read-Committed Snapshot 选项。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 创建表,随后使用 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. 低于快照的隔离级别示例有 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. 在插入到 varchar 或 nvarchar 数据类型之前,也填充到同一长度。Also padded to the same length before inserting to a varchar or nvarchar data type.

OFFOFF
将字符值中的尾随空格插入 varchar 或 nvarchar 列中。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 IdentifiersFor 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. 若要禁用间接递归触发,还必须将 nested triggers 服务器选项设置为 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 数据库
单一数据库/弹性池
SQL Database
single database/elastic pool
SQL 数据库
托管实例
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 数据库实现透明数据加密”。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