CREATE DATABASECREATE DATABASE

创建新数据库。Creates a new database.

单击以下选项卡之一,了解所使用的特定 SQL 版本的语法、参数、备注、权限和示例。Click one of the following tabs for the syntax, arguments, remarks, permissions, and examples for a particular SQL version with which you are working.

有关语法约定的详细信息,请参阅 Transact-SQL 语法约定For more information about the syntax conventions, see Transact-SQL Syntax Conventions.

选择一个产品Select a product

在下面的行中,选择你感兴趣的产品名称,系统将只显示该产品的信息。In the following row, select the product name you're interested in, and only that product’s information is displayed.

* SQL Server *  * SQL Server *  

 

SQL ServerSQL Server

概述Overview

在 SQL Server 中,此语句创建新数据库和使用的文件及其文件组。In SQL Server, this statement creates a new database and the files used and their filegroups. 它还可用于创建数据库快照,或附加数据库文件以从其他数据库的分离文件创建数据库。It can also be used to create a database snapshot, or attach database files to create a database from the detached files of another database.

语法Syntax

创建数据库Create a database

CREATE DATABASE database_name
[ CONTAINMENT = { NONE | PARTIAL } ]
[ ON
      [ PRIMARY ] <filespec> [ ,...n ]
      [ , <filegroup> [ ,...n ] ]
      [ LOG ON <filespec> [ ,...n ] ]
]
[ COLLATE collation_name ]
[ WITH <option> [,...n ] ]
[;]

<option> ::=
{
      FILESTREAM ( <filestream_option> [,...n ] )
    | 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 = <two_digit_year_cutoff>
    | DB_CHAINING { OFF | ON }
    | TRUSTWORTHY { OFF | ON }
    | PERSISTENT_LOG_BUFFER=ON ( DIRECTORY_NAME='<Filepath to folder on DAX formatted volume>' )
}

<filestream_option> ::=
{
      NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }
    | DIRECTORY_NAME = 'directory_name'
}

<filespec> ::=
{
(
    NAME = logical_file_name ,
    FILENAME = { 'os_file_name' | 'filestream_path' }
    [ , SIZE = size [ KB | MB | GB | TB ] ]
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]
)
}

<filegroup> ::=
{
FILEGROUP filegroup name [ [ CONTAINS FILESTREAM ] [ DEFAULT ] | CONTAINS MEMORY_OPTIMIZED_DATA ]
    <filespec> [ ,...n ]
}

附加数据库Attach a database

CREATE DATABASE database_name
    ON <filespec> [ ,...n ]
    FOR { { ATTACH [ WITH <attach_database_option> [ , ...n ] ] }
        | ATTACH_REBUILD_LOG }
[;]

<attach_database_option> ::=
{
      <service_broker_option>
    | RESTRICTED_USER
    | FILESTREAM ( DIRECTORY_NAME = { 'directory_name' | NULL } )
}

<service_broker_option> ::=
{
    ENABLE_BROKER
  | NEW_BROKER
  | ERROR_BROKER_CONVERSATIONS
}

创建数据库快照Create a database snapshot

CREATE DATABASE database_snapshot_name
    ON
    (
        NAME = logical_file_name,
        FILENAME = 'os_file_name'
    ) [ ,...n ]
    AS SNAPSHOT OF
[;]

参数Arguments

database_name 是新数据库的名称。database_name Is the name of the new database. 数据库名称在 SQL ServerSQL Server 的实例中必须唯一,并且必须符合标识符规则。Database names must be unique within an instance of SQL ServerSQL Server and comply with the rules for identifiers.

除非没有为日志文件指定逻辑名称,否则 database_name 最多可以包含 128 个字符。database_name can be a maximum of 128 characters, unless a logical name is not specified for the log file. 如果未指定逻辑日志文件名称,SQL ServerSQL Server 会通过向 database_name 追加后缀来为日志生成 logical_file_name 和 os_file_name 。If a logical log file name is not specified, SQL ServerSQL Server generates the logical_file_name and the os_file_name for the log by appending a suffix to database_name. 这会将 database_name 限制为 123 个字符,从而使生成的逻辑文件名称不超过 128 个字符。This limits database_name to 123 characters so that the generated logical file name is no more than 128 characters.

如果未指定数据文件名称,SQL ServerSQL Server 会使用 database_name 同时作为 logical_file_name 和 os_file_name 。If data file name is not specified, SQL ServerSQL Server uses database_name as both the logical_file_name and as the os_file_name. 默认路径从注册表中获得。The default path is obtained from the registry. 可以使用 Management StudioManagement Studio 中的“服务器属性”(“数据库设置”页)更改默认路径。The default path can be changed by using the Server Properties (Database Settings Page) in Management StudioManagement Studio. 更改默认路径要求重新启动 SQL ServerSQL ServerChanging the default path requires restarting SQL ServerSQL Server.

CONTAINMENT = { NONE | PARTIAL }CONTAINMENT = { NONE | PARTIAL }

适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later

指定数据库的包含状态。Specifies the containment status of the database. NONE = 非包含数据库。NONE = non-contained database. PARTIAL = 部分包含的数据库。PARTIAL = partially contained database.

ON 指定显式定义用来存储数据库数据部分的磁盘文件(数据文件)。ON Specifies that the disk files used to store the data sections of the database, data files, are explicitly defined. 当后面是以逗号分隔的、用以定义主文件组的数据文件的 <filespec> 项列表时,需要使用 ON。ON is required when followed by a comma-separated list of <filespec> items that define the data files for the primary filegroup. 主文件组的文件列表可后跟以逗号分隔的、用以定义用户文件组及其文件的 <filegroup> 项列表(可选)。The list of files in the primary filegroup can be followed by an optional, comma-separated list of <filegroup> items that define user filegroups and their files.

PRIMARY 指定关联的 <filespec> 列表定义主文件。PRIMARY Specifies that the associated <filespec> list defines the primary file. 在主文件组的 <filespec> 项中指定的第一个文件将成为主文件。The first file specified in the <filespec> entry in the primary filegroup becomes the primary file. 一个数据库只能有一个主文件。A database can have only one primary file. 有关详细信息,请参阅 数据库文件和文件组For more information, see Database Files and Filegroups.

如果没有指定 PRIMARY,那么 CREATE DATABASE 语句中列出的第一个文件将成为主文件。If PRIMARY is not specified, the first file listed in the CREATE DATABASE statement becomes the primary file.

LOG ON 指定显式定义用来存储数据库日志的磁盘文件(日志文件)。LOG ON Specifies that the disk files used to store the database log, log files, are explicitly defined. LOG ON 后跟以逗号分隔的用以定义日志文件的 <filespec> 项列表。LOG ON is followed by a comma-separated list of <filespec> items that define the log files. 如果没有指定 LOG ON,将自动创建一个日志文件,其大小为该数据库的所有数据文件大小总和的 25% 或 512 KB,取两者之中的较大者。If LOG ON is not specified, one log file is automatically created, which has a size that is 25 percent of the sum of the sizes of all the data files for the database, or 512 KB, whichever is larger. 此文件放置于默认的日志文件位置。This file is placed in the default log-file location. 有关此位置的信息,请参阅查看或更改数据文件和日志文件的默认位置 - SSMSFor information about this location, see View or Change the Default Locations for Data and Log Files - SSMS.

不能对数据库快照指定 LOG ON。LOG ON cannot be specified on a database snapshot.

COLLATE collation_name 指定数据库的默认排序规则。COLLATE collation_name Specifies the default collation for the database. 排序规则名称既可以是 Windows 排序规则名称,也可以是 SQL 排序规则名称。Collation name can be either a Windows collation name or a SQL collation name. 如果没有指定排序规则,则将 SQL ServerSQL Server 实例的默认排序规则分配为数据库的排序规则。If not specified, the database is assigned the default collation of the instance of SQL ServerSQL Server. 不能对数据库快照指定排序规则名称。A collation name cannot be specified on a database snapshot.

不能使用 FOR ATTACH 或 FOR ATTACH_REBUILD_LOG 子句指定排序规则名称。A collation name cannot be specified with the FOR ATTACH or FOR ATTACH_REBUILD_LOG clauses. 有关如何更改附加数据库的排序规则的信息,请访问此 Microsoft 网站For information about how to change the collation of an attached database, visit this Microsoft Web site.

有关 Windows 和 SQL 排序规则名称的详细信息,请参阅 COLLATEFor more information about the Windows and SQL collation names, see COLLATE.

备注

包含数据库的排序方式不同于非包含数据库。Contained databases are collated differently than non-contained databases. 有关详细信息,请参阅包含的数据库排序规则Please see Contained Database Collations for more information.

WITH <option> <filestream_option>WITH <option> <filestream_option>

NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL } 适用于:SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本 。NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL } Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

指定对数据库的非事务性 FILESTREAM 访问的级别。Specifies the level of non-transactional FILESTREAM access to the database.

Value 说明Description
OFFOFF 禁用非事务性访问。Non-transactional access is disabled.
READONLYREADONLY 可以通过非事务性进程读取此数据库中的 FILESTREAM 数据。FILESTREAM data in this database can be read by non-transactional processes.
FULLFULL 启用对 FILESTREAM FileTable 的完全非事务性访问。Full non-transactional access to FILESTREAM FileTables is enabled.

DIRECTORY_NAME = <directory_name> 适用于:SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本DIRECTORY_NAME = <directory_name> Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later

与 Windows 兼容的目录名称。A windows-compatible directory name. 此名称应在 SQL ServerSQL Server 实例的所有 Database_Directory 名称中唯一。This name should be unique among all the Database_Directory names in the SQL ServerSQL Server instance. 无论 SQL ServerSQL Server 排序规则设置如何,唯一性比较都不区分大小写。Uniqueness comparison is case-insensitive, regardless of SQL ServerSQL Server collation settings. 在此数据库中创建 FileTable 之前,应设置此选项。This option should be set before creating a FileTable in this database.

仅在将 CONTAINMENT 设置为 PARTIAL 之后,才允许使用以下选项。The following options are allowable only when CONTAINMENT has been set to PARTIAL. 如果将 CONTAINMENT 设置为 NONE,将发生错误。If CONTAINMENT is set to NONE, errors will occur.

  • DEFAULT_FULLTEXT_LANGUAGE = <lcid> | <language name> | <language alias>DEFAULT_FULLTEXT_LANGUAGE = <lcid> | <language name> | <language alias>

    适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later

    有关此选项的完整说明,请参阅配置“默认全文语言”服务器配置选项See Configure the default full-text language Server Configuration Option for a full description of this option.

  • DEFAULT_LANGUAGE = <lcid> | <language name> | <language alias>DEFAULT_LANGUAGE = <lcid> | <language name> | <language alias>

    适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later

    有关此选项的完整说明,请参阅配置“默认语言”服务器配置选项See Configure the default language Server Configuration Option for a full description of this option.

  • NESTED_TRIGGERS = { OFF | ON}NESTED_TRIGGERS = { OFF | ON}

    适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later

    有关此选项的完整说明,请参阅配置“嵌套触发器”服务器配置选项See Configure the nested triggers Server Configuration Option for a full description of this option.

  • TRANSFORM_NOISE_WORDS = { OFF | ON}TRANSFORM_NOISE_WORDS = { OFF | ON}

    适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later

    有关此选项的完整说明,请参阅“转换干扰词”服务器配置选项See transform noise words Server Configuration Optionfor a full description of this option.

  • TWO_DIGIT_YEAR_CUTOFF = { 2049 | <any year between 1753 and 9999> }TWO_DIGIT_YEAR_CUTOFF = { 2049 | <any year between 1753 and 9999> }

    表示一年的四位。Four digits representing a year. 2049 为默认值。2049 is the default value. 有关此选项的完整说明,请参阅配置 two digit year cutoff 服务器配置选项See Configure the two digit year cutoff Server Configuration Option for a full description of this option.

  • DB_CHAINING { OFF | ON }DB_CHAINING { OFF | ON }

    当指定为 ON 时,数据库可以为跨数据库所有权链的源或目标。When ON is specified, the database can be the source or target of a cross-database ownership chain.

    当为 OFF 时,数据库不能参与跨数据库所有权链接。When OFF, the database cannot participate in cross-database ownership chaining. 默认为 OFF。The default is OFF.

    重要

    如果 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.

    若要设置此选项,要求具有 sysadmin 固定服务器角色的成员身份。To set this option, requires membership in the sysadmin fixed server role. 不能针对下列系统数据库设置 DB_CHAINING 选项:master、model 和 tempdb。The DB_CHAINING option cannot be set on these system databases: master, model, tempdb.

  • TRUSTWORTHY { OFF | ON }TRUSTWORTHY { OFF | ON }

    当指定 ON 时,使用模拟上下文的数据库模块(例如,视图、用户定义函数或存储过程)可以访问数据库以外的资源。When ON is specified, database modules (for example, views, user-defined functions, or stored procedures) that use an impersonation context can access resources outside the database.

    当为 OFF 时,模拟上下文中的数据库模块不能访问数据库以外的资源。When OFF, database modules in an impersonation context cannot access resources outside the database. 默认为 OFF。The default is OFF.

    只要附加数据库,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 cannot 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.

  • PERSISTENT_LOG_BUFFER=ON ( DIRECTORY_NAME='' )PERSISTENT_LOG_BUFFER=ON ( DIRECTORY_NAME='' )

    指定此选项后,将在位于由存储类内存(NVDIMM-N 永久性内存)支持的磁盘设备的卷上创建事务日志缓冲区 - 也称为持久性日志缓冲区。When this option is specified, the transaction log buffer is created on a volume that is located on a disk device backed by Storage Class Memory (NVDIMM-N nonvolatile storage) - also known as a persistent log buffer. 有关详细信息,请参阅使用存储类内存的事务提交延迟加速For more information, see Transaction Commit latency acceleration using Storage Class Memory. 适用于:SQL Server 2017 (14.x)SQL Server 2017 (14.x) 及更高版本。Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x) and newer.

FOR ATTACH [ WITH < attach_database_option > ] 指定通过附加一组现有的操作系统文件来创建数据库。FOR ATTACH [ WITH < attach_database_option > ] Specifies that the database is created by attaching an existing set of operating system files. 必须有一个指定主文件的 <filespec> 项。There must be a <filespec> entry that specifies the primary file. 至于其他 <filespec> 项,只需要指定与第一次创建数据库或上一次附加数据库时路径不同的文件的那些项即可。The only other <filespec> entries required are those for any files that have a different path from when the database was first created or last attached. 必须有一个 <filespec> 项指定这些文件。A <filespec> entry must be specified for these files.

FOR ATTACH 具有以下要求:FOR ATTACH requires the following:

  • 所有数据文件(MDF 和 NDF)都必须可用。All data files (MDF and NDF) must be available.
  • 如果存在多个日志文件,这些文件都必须可用。If multiple log files exist, they must all be available.

如果一个可读/写数据库具有一个当前不可用的日志文件,并且进行附加操作前在没有使用用户或打开的事务的情况下关闭了该数据库,那么 FOR ATTACH 会自动重新生成日志文件并更新主文件。If a read/write database has a single log file that is currently unavailable, and if the database was shut down with no users or open transactions before the attach operation, FOR ATTACH automatically rebuilds the log file and updates the primary file. 相比之下,对于只读数据库,由于主文件不能更新,将不能重新生成日志。In contrast, for a read-only database, the log cannot be rebuilt because the primary file cannot be updated. 因此,如果附加一个日志不可用的只读数据库,则必须提供日志文件,或提供 FOR ATTACH 子句中的文件。Therefore, when you attach a read-only database with a log that is unavailable, you must provide the log files, or the files in the FOR ATTACH clause.

备注

无法在早期版本的 SQL Server 中附加由较新版本的 SQL ServerSQL Server 创建的数据库。A database created by a more recent version of SQL ServerSQL Server cannot be attached in earlier versions.

SQL ServerSQL Server 中,作为待附加数据库的组成部分的所有全文文件也将随之一起附加。In SQL ServerSQL Server, any full-text files that are part of the database that is being attached will be attached with the database. 若要指定全文目录的新路径,请指定不带全文操作系统文件名的新位置。To specify a new path of the full-text catalog, specify the new location without the full-text operating system file name. 有关详细信息,请参阅“示例”部分。For more information, see the Examples section.

将包含 FILESTREAM 选项“目录名称”的数据库附加到 SQL ServerSQL Server 实例中将提示 SQL ServerSQL Server 验证 Database_Directory 名称是否唯一。Attaching a database that contains a FILESTREAM option of "Directory name", into a SQL ServerSQL Server instance will prompt SQL ServerSQL Server to verify that the Database_Directory name is unique. 如果该名称不唯一,附加操作将失败,并显示错误“FILESTREAM Database_Directory name <name> 在此 SQL Server 实例中不唯一”。If it is not, the attach operation fails with the error, "FILESTREAM Database_Directory name <name> is not unique in this SQL Server instance". 为避免此错误,应将可选参数 directory_name 传递给此操作。To avoid this error, the optional parameter, directory_name, should be passed in to this operation.

不能对数据库快照指定 FOR ATTACH。FOR ATTACH cannot be specified on a database snapshot.

对于 ATTACH,可以指定 RESTRICTED_USER 选项。FOR ATTACH can specify the RESTRICTED_USER option. RESTRICTED_USER 只允许 db_owner 固定数据库角色成员以及 dbcreator 和 sysadmin 固定服务器角色成员连接到数据库,不过对连接数没有限制。RESTRICTED_USER allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but does not limit their number. 无资格用户的尝试将被拒绝。Attempts by unqualified users are refused.

如果数据库使用 Service BrokerService Broker,请在 FOR ATTACH 子句中使用 WITH <service_broker_option>:If the database uses Service BrokerService Broker, use the WITH <service_broker_option> in your FOR ATTACH clause:

<service_broker_option> 控制 Service BrokerService Broker 消息传递和数据库的 Service BrokerService Broker 标识符。<service_broker_option> Controls Service BrokerService Broker message delivery and the Service BrokerService Broker identifier for the database. 仅当使用 FOR ATTACH 子句时,才能指定 Service BrokerService Broker 选项。Service BrokerService Broker options can only be specified when the FOR ATTACH clause is used.

ENABLE_BROKER 指定对指定的数据库启用 Service BrokerService BrokerENABLE_BROKER Specifies that Service BrokerService Broker is enabled for the specified database. 也就是说,启动了消息传递,并且在 sys.databases 目录视图中将 is_broker_enabled 设置为 true。That is, message delivery is started, and is_broker_enabled is set to true in the sys.databases catalog view. 数据库保留现有的 Service BrokerService Broker 标识符。The database retains the existing Service BrokerService Broker identifier.

NEW_BROKER 在 sys.databases 和还原数据库中都创建一个新的 service_broker_guid 值,并通过清除结束所有会话端点。NEW_BROKER Creates a new service_broker_guid value in both sys.databases and the restored database and ends all conversation endpoints with clean up. Broker 已启用,但未向远程会话端点发送消息。The broker is enabled, but no message is sent to the remote conversation endpoints. 必须使用新标识符重新创建任何引用旧 Service BrokerService Broker 标识符的路由。Any route that references the old Service BrokerService Broker identifier must be re-created with the new identifier.

ERROR_BROKER_CONVERSATIONS 结束所有会话,并产生一个错误指出数据库已附加或还原。ERROR_BROKER_CONVERSATIONS Ends all conversations with an error stating that the database is attached or restored. Broker 一直处于禁用状态直到此操作完成,然后再将其启用。The broker is disabled until this operation is completed and then enabled. 数据库保留现有的 Service BrokerService Broker 标识符。The database retains the existing Service BrokerService Broker identifier.

当您附加已复制的数据库而不是分离的数据库时,请注意以下事项:When you attach a replicated database that was copied instead of being detached, consider the following:

  • 如果将数据库附加到与原始数据库相同的服务器实例和版本,则不需要执行其他步骤。If you attach the database to the same server instance and version as the original database, no additional steps are required.
  • 如果将数据库附加到同一个服务器实例,但是版本已升级,则必须执行 sp_vupgrade_replication 才能在附加操作完成后升级复制。If you attach the database to the same server instance but with an upgraded version, you must execute sp_vupgrade_replication to upgrade replication after the attach operation is complete.
  • 如果将数据库附加到不同的服务器实例,而不考虑版本,则必须执行 sp_removedbreplication 才能在附加操作完成后删除复制。If you attach the database to a different server instance, regardless of version, you must execute sp_removedbreplication to remove replication after the attach operation is complete.

备注

附加操作使用 vardecimal 存储格式进行,但必须将 SQL Server 数据库引擎SQL Server Database Engine至少升级到 SQL Server 2005 (9.x)SQL Server 2005 (9.x) SP2。Attach works with the vardecimal storage format, but the SQL Server 数据库引擎SQL Server Database Engine must be upgraded to at least SQL Server 2005 (9.x)SQL Server 2005 (9.x) SP2. 无法将使用 Vardecimal 存储格式的数据库附加到早期版本的 SQL ServerSQL ServerYou cannot attach a database using vardecimal storage format to an earlier version of SQL ServerSQL Server. 有关 vardecimal 存储格式的详细信息,请参阅数据压缩For more information about the vardecimal storage format, see Data Compression.

当数据库第一次附加或还原到新的 SQL ServerSQL Server实例时,数据库主密钥(由服务主密钥加密)的副本尚未存储在服务器中。When a database is first attached or restored to a new instance of SQL ServerSQL Server, a copy of the database master key (encrypted by the service master key) is not yet stored in the server. 必须使用 OPEN MASTER KEY 语句解密数据库主密钥 (DMK)。You must use the OPEN MASTER KEY statement to decrypt the database master key (DMK). 一旦 DMK 解密后,通过使用 ALTER MASTER KEY REGENERATE 语句向服务器提供 DMK(使用服务主密钥 (SMK) 加密)的副本,即可拥有将来启用自动解密的选项。Once the DMK has been decrypted, you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY REGENERATE statement to provision the server with a copy of the DMK, encrypted with the service master key (SMK). 当数据库已从较早版本升级后,应重新生成 DMK 以使用更新的 AES 算法。When a database has been upgraded from an earlier version, the DMK should be regenerated to use the newer AES algorithm. 有关重新生成 DMK 的详细信息,请参阅 ALTER MASTER KEYFor more information about regenerating the DMK, see ALTER MASTER KEY. 重新生成 DMK 密钥以升级到 AES 所需的时间取决于 DMK 保护的对象数。The time required to regenerate the DMK key to upgrade to AES depends upon the number of objects protected by the DMK. 重新生成 DMK 密钥以升级到 AES 只在必需时执行一次,不影响将来作为密钥循环策略的一部分而重新生成的过程。Regenerating the DMK key to upgrade to AES is only necessary once, and has no impact on future regenerations as part of a key rotation strategy. 有关如何使用附加来升级数据库的信息,请参阅使用分离和附加来升级数据库For information about how to upgrade a database by using attach, see Upgrade a Database Using Detach and Attach.

重要

建议您不要附加未知或不可信源中的数据库。We recommend that you do not attach databases from unknown or untrusted sources. 此类数据库可能包含恶意代码,这些代码可能会执行非预期的 Transact-SQLTransact-SQL 代码,或者通过修改架构或物理数据库结构导致错误。Such databases could contain malicious code that might execute unintended Transact-SQLTransact-SQL code or cause errors by modifying the schema or the physical database structure. 使用来自未知源或不可信源的数据库前,请在非生产服务器上针对数据库运行 DBCC CHECKDB,然后检查数据库中的代码,例如存储过程或其他用户定义代码。Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server, and also examine the code, such as stored procedures or other user-defined code, in the database.

备注

在附加数据库时,TRUSTWORTHYDB_CHAINING 选项没有影响。The TRUSTWORTHY and DB_CHAINING options have no affect when attaching a database.

FOR ATTACH_REBUILD_LOG 指定通过附加一组现有的操作系统文件来创建数据库。FOR ATTACH_REBUILD_LOG Specifies that the database is created by attaching an existing set of operating system files. 该选项只限于读/写数据库。This option is limited to read/write databases. 必须有一个指定主文件的 <filespec> 项。There must be a <filespec> entry specifying the primary file. 如果缺少一个或多个事务日志文件,将重新生成日志文件。If one or more transaction log files are missing, the log file is rebuilt. ATTACH_REBUILD_LOG 自动创建一个新的 1 MB 的日志文件。The ATTACH_REBUILD_LOG automatically creates a new, 1 MB log file. 此文件放置于默认的日志文件位置。This file is placed in the default log-file location. 有关此位置的信息,请参阅查看或更改数据文件和日志文件的默认位置 - SSMSFor information about this location, see View or Change the Default Locations for Data and Log Files - SSMS.

备注

如果日志文件可用,数据库引擎Database Engine将使用这些文件,而不会重新生成日志文件。If the log files are available, the 数据库引擎Database Engine uses those files instead of rebuilding the log files.

FOR ATTACH_REBUILD_LOG 具有以下要求:FOR ATTACH_REBUILD_LOG requires the following:

  • 完全关闭数据库。A clean shutdown of the database.
  • 所有数据文件(MDF 和 NDF)都必须可用。All data files (MDF and NDF) must be available.

重要

该操作会中断日志备份链。This operation breaks the log backup chain. 建议在完成该操作后执行完整数据库备份。We recommend that a full database backup be performed after the operation is completed. 有关详细信息,请参阅 BACKUPFor more information, see BACKUP.

通常,FOR ATTACH_REBUILD_LOG 用于将具有大型日志的可读/写数据库复制到另一台服务器,在这台服务器上,数据库副本频繁使用,或仅用于读操作,因而所需的日志空间少于原始数据库。Typically, FOR ATTACH_REBUILD_LOG is used when you copy a read/write database with a large log to another server where the copy will be used mostly, or only, for read operations, and therefore requires less log space than the original database.

不能对数据库快照指定 FOR ATTACH_REBUILD_LOG。FOR ATTACH_REBUILD_LOG cannot be specified on a database snapshot.

有关附加数据库和分离数据库的详细信息,请参阅数据库分离和附加For more information about attaching and detaching databases, see Database Detach and Attach.

<filespec> 控制文件属性。<filespec> Controls the file properties.

NAME logical_file_name 指定文件的逻辑名称。NAME logical_file_name Specifies the logical name for the file. 指定 FILENAME 时,需要使用 NAME,除非指定 FOR ATTACH 子句之一。NAME is required when FILENAME is specified, except when specifying one of the FOR ATTACH clauses. 无法将 FILESTREAM 文件组命名为 PRIMARY。A FILESTREAM filegroup cannot be named PRIMARY.

logical_file_name 在 SQL ServerSQL Server 中引用文件时所用的逻辑名称。logical_file_name Is the logical name used in SQL ServerSQL Server when referencing the file. Logical_file_name 在数据库中必须唯一,并且必须符合标识符规则。Logical_file_name must be unique in the database and comply with the rules for identifiers. 名称可以是字符或 Unicode 常量,也可以是常规标识符或分隔标识符。The name can be a character or Unicode constant, or a regular or delimited identifier.

FILENAME { ' os_file_name ' | ' filestream_path ' } 指定操作系统(物理)文件名。FILENAME { 'os_file_name' | 'filestream_path' } Specifies the operating system (physical) file name.

' os_file_name ' 是创建文件时由操作系统使用的路径和文件名。' os_file_name ' Is the path and file name used by the operating system when you create the file. 文件必须驻留在下列一种设备中:安装 SQL ServerSQL Server 的本地服务器、存储区域网络 [SAN] 或基于 iSCSI 的网络。The file must reside on one of the following devices: the local server on which SQL ServerSQL Server is installed, a Storage Area Network [SAN], or an iSCSI-based network. 执行 CREATE DATABASE 语句前,指定路径必须存在。The specified path must exist before executing the CREATE DATABASE statement. 有关详细信息,请参阅“备注”部分的“数据库文件和文件组”。For more information, see "Database Files and Filegroups" in the Remarks section.

如果为该文件指定了 UNC 路径,则可以设置 SIZE、MAXSIZE 和 FILEGROWTH 参数。SIZE, MAXSIZE, and FILEGROWTH parameters can be set when a UNC path is specified for the file.

如果文件位于原始分区上,则 os_file_name 必须仅指定现有原始分区的驱动器号。If the file is on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. 每个原始分区上只能创建一个数据文件。Only one data file can be created on each raw partition.

不应将数据文件放在压缩文件系统中,除非这些文件是只读的辅助文件或数据库是只读的。Data files should not be put on compressed file systems unless the files are read-only secondary files, or the database is read-only. 日志文件一定不要放在压缩文件系统中。Log files should never be put on compressed file systems.

' filestream_path ' 对于 FILESTREAM 文件组,FILENAME 指向将存储 FILESTREAM 数据的路径。' filestream_path ' For a FILESTREAM filegroup, FILENAME refers to a path where FILESTREAM data will be stored. 在最后一个文件夹之前的路径必须存在,但不能存在最后一个文件夹。The path up to the last folder must exist, and the last folder must not exist. 例如,如果指定路径 C:\MyFiles\MyFilestreamData、C:\MyFiles 必须存在才能运行 ALTER DATABASE,但 MyFilestreamData 文件夹不能存在。For example, if you specify the path C:\MyFiles\MyFilestreamData, C:\MyFiles must exist before you run ALTER DATABASE, but the MyFilestreamData folder must not exist.

必须在同一语句中创建文件组和文件 (<filespec>)。The filegroup and file (<filespec>) must be created in the same statement.

SIZE 和 FILEGROWTH 属性不适用于 FILESTREAM 文件组。The SIZE and FILEGROWTH properties do not apply to a FILESTREAM filegroup.

SIZE size 指定文件的大小。SIZE size Specifies the size of the file.

os_file_name 指定为 UNC 路径时,不能指定 SIZE。SIZE cannot be specified when the os_file_name is specified as a UNC path. SIZE 不适用于 FILESTREAM 文件组。SIZE does not apply to a FILESTREAM filegroup.

size 文件的初始大小。size Is the initial size of the file.

如果没有为主文件提供 size数据库引擎Database Engine会使用 model 数据库中主文件的大小。When size is not supplied for the primary file, the 数据库引擎Database Engine uses the size of the primary file in the model database. model 数据库中主文件的默认大小为 8 MB(从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始)或 1 MB(对于较早版本)。The default size of model is 8 MB (beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x)) or 1 MB (for earlier versions). 如果指定了辅助数据文件或日志文件,但未指定该文件的 size数据库引擎Database Engine会以 8 MB(从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始)或 1 MB(对于较早版本)作为该文件的大小。When a secondary data file or log file is specified, but size is not specified for the file, the 数据库引擎Database Engine makes the file 8 MB (beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x)) or 1 MB (for earlier versions). 为主文件指定的大小至少应与 model 数据库的主文件大小相同。The size specified for the primary file must be at least as large as the primary file of the model database.

可以使用千字节 (KB)、兆字节 (MB)、千兆字节 (GB) 或兆兆字节 (TB) 后缀。The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. 默认值为 MB。The default is MB. 指定一个整数,不包含小数位。Specify a whole number; do not include a decimal. Size 是一个整数值。Size is an integer value. 对于大于 2147483647 的值,使用更大的单位。For values greater than 2147483647, use larger units.

MAXSIZE max_size 指定文件可增大到的最大大小。MAXSIZE max_size Specifies the maximum size to which the file can grow. os_file_name 指定为 UNC 路径时,不能指定 MAXSIZE。MAXSIZE cannot be specified when the os_file_name is specified as a UNC path.

max_size 最大的文件大小。max_size Is the maximum file size. 可以使用 KB、MB、GB 和 TB 后缀。The KB, MB, GB, and TB suffixes can be used. 默认值为 MB。The default is MB. 指定一个整数,不包含小数位。Specify a whole number; do not include a decimal. 如果未指定 max_size,文件将一直增长到磁盘变满为止。If max_size is not specified, the file grows until the disk is full. Max_size 是一个整数值。Max_size is an integer value. 对于大于 2147483647 的值,使用更大的单位。For values greater than 2147483647, use larger units.

UNLIMITED 指定文件将增长到磁盘充满。UNLIMITED Specifies that the file grows until the disk is full. SQL ServerSQL Server 中,指定为不限制增长的日志文件的最大大小为 2 TB,而数据文件的最大大小为 16 TB。In SQL ServerSQL Server, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB.

备注

为 FILESTREAM 容器指定此选项时,没有最大大小。There is no maximum size when this option is specified for a FILESTREAM container. 它将继续增大,直到磁盘已满。It continues to grow until the disk is full.

FILEGROWTH growth_increment 指定文件的自动增量。FILEGROWTH growth_increment Specifies the automatic growth increment of the file. 文件的 FILEGROWTH 设置不能超过 MAXSIZE 设置。The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting. os_file_name 指定为 UNC 路径时,不能指定 FILEGROWTH。FILEGROWTH cannot be specified when the os_file_name is specified as a UNC path. FILEGROWTH 不适用于 FILESTREAM 文件组。FILEGROWTH does not apply to a FILESTREAM filegroup.

growth_increment 每次需要新空间时为文件添加的空间量。growth_increment Is the amount of space added to the file every time new space is required.

该值可以 MB、KB、GB、TB 或百分比 (%) 为单位指定。The value can be specified in MB, KB, GB, TB, or percent (%). 如果未在数量后面指定 MB、KB 或 %,则默认值为 MB。If a number is specified without an MB, KB, or % suffix, the default is MB. 如果指定 %,则增量大小为发生增长时文件大小的指定百分比。When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. 指定的大小舍入为最接近的 64 KB 的倍数,最小值为 64 KB。The size specified is rounded to the nearest 64 KB, and the minimum value is 64 KB.

值为 0 时表明自动增长被设置为关闭,不允许增加空间。A value of 0 indicates that automatic growth is off and no additional space is allowed.

如果未指定 FILEGROWTH,则默认值为:If FILEGROWTH is not specified, the default values are:

版本Version 默认值Default values
SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始Beginning SQL Server 2016 (13.x)SQL Server 2016 (13.x) 数据 64 MB。Data 64 MB. 日志文件 64 MB。Log files 64 MB.
SQL Server 2005 (9.x)SQL Server 2005 (9.x) 开始Beginning SQL Server 2005 (9.x)SQL Server 2005 (9.x) 数据 1 MB。Data 1 MB. 日志文件 10%。Log files 10%.
SQL Server 2005 (9.x)SQL Server 2005 (9.x) 之前Prior to SQL Server 2005 (9.x)SQL Server 2005 (9.x) 数据 10%。Data 10%. 日志文件 10%。Log files 10%.

<filegroup> 控制文件组属性。<filegroup> Controls the filegroup properties. 不能对数据库快照指定文件组。Filegroup cannot be specified on a database snapshot.

FILEGROUP filegroup_name 文件组的逻辑名称。FILEGROUP filegroup_name Is the logical name of the filegroup.

filegroup_name filegroup_name 在数据库中必须唯一,并且不能是系统提供的名称 PRIMARY 和 PRIMARY_LOG。filegroup_name filegroup_name must be unique in the database and cannot be the system-provided names PRIMARY and PRIMARY_LOG. 名称可以是字符或 Unicode 常量,也可以是常规标识符或分隔标识符。The name can be a character or Unicode constant, or a regular or delimited identifier. 名称必须符合标识符规则。The name must comply with the rules for identifiers.

CONTAINS FILESTREAM 指定文件组在文件系统中存储 FILESTREAM 二进制大型对象 (BLOB)。CONTAINS FILESTREAM Specifies that the filegroup stores FILESTREAM binary large objects (BLOBs) in the file system.

CONTAINS MEMORY_OPTIMIZED_DATACONTAINS MEMORY_OPTIMIZED_DATA

适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later

指定文件组在文件系统中存储内存优化数据。Specifies that the filegroup stores memory_optimized data in the file system. 有关详细信息,请参阅内存中 OLTP - 内存中优化For more information, see In-Memory OLTP - In-Memory Optimization. 每个数据库只能有一个 MEMORY_OPTIMIZED_DATA 文件组。Only one MEMORY_OPTIMIZED_DATA filegroup is allowed per database. 有关通过创建文件组来存储内存优化数据的代码示例,请参阅创建内存优化表和本机编译的存储过程For code samples that create a filegroup to store memory-optimized data, see Creating a Memory-Optimized Table and a Natively Compiled Stored Procedure.

DEFAULT 指定命名文件组为数据库中的默认文件组。DEFAULT Specifies the named filegroup is the default filegroup in the database.

database_snapshot_name 新数据库快照的名称。database_snapshot_name Is the name of the new database snapshot. 数据库快照名称必须在 SQL ServerSQL Server 的实例中唯一,并且必须符合标识符规则。Database snapshot names must be unique within an instance of SQL ServerSQL Server and comply with the rules for identifiers. database_snapshot_name 最多可以包含 128 个字符。database_snapshot_name can be a maximum of 128 characters.

ON ( NAME =logical_file_name, FILENAME ='os_file_name') [ ,... n ] 若要创建数据库快照,请在源数据库中指定文件列表。ON ( NAME =logical_file_name, FILENAME ='os_file_name') [ ,... n ] For creating a database snapshot, specifies a list of files in the source database. 若要使快照工作,必须分别指定所有数据文件。For the snapshot to work, all the data files must be specified individually. 但是,日志文件不允许用于数据库快照。However, log files are not allowed for database snapshots. 数据库快照不支持 FILESTREAM 文件组。FILESTREAM filegroups are not supported by database snapshots. 如果在 CREATE DATABASE ON 子句中包含了 FILESTREAM 数据文件,该语句将失败,并且会引发错误。If a FILESTREAM data file is included in a CREATE DATABASE ON clause, the statement will fail and an error will be raised.

有关 NAME 和 FILENAME 以及其值的说明,请参阅等效的 <filespec> 值的说明。For descriptions of NAME and FILENAME and their values see the descriptions of the equivalent <filespec> values.

备注

创建数据库快照时,不允许使用其他 <filespec> 选项和关键字 PRIMARY。When you create a database snapshot, the other <filespec> options and the keyword PRIMARY are disallowed.

AS SNAPSHOT OF source_database_name 指定要创建的数据库为 source_database_name 指定的源数据库的数据库快照。AS SNAPSHOT OF source_database_name Specifies that the database being created is a database snapshot of the source database specified by source_database_name. 快照和源数据库必须位于同一实例中。The snapshot and source database must be on the same instance.

有关详细信息,请参阅“备注”部分的数据库快照For more information, see Database Snapshots in the Remarks section.

备注Remarks

创建、修改或删除用户数据库后,应备份 master 数据库The master database should be backed up whenever a user database is created, modified, or dropped.

CREATE DATABASE 语句必须在自动提交模式(默认事务管理模式)下运行,且不允许用于显式或隐式事务中。The CREATE DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.

使用一条 CREATE DATABASE 语句即可创建数据库以及存储该数据库的文件。You can use one CREATE DATABASE statement to create a database and the files that store the database. SQL ServerSQL Server 通过使用以下步骤实现 CREATE DATABASE 语句:implements the CREATE DATABASE statement by using the following steps:

  1. SQL ServerSQL Server 使用 model 数据库的副本初始化该数据库及其元数据。The SQL ServerSQL Server uses a copy of the model database to initialize the database and its metadata.
  2. 为数据库分配 Service Broker GUID。A service broker GUID is assigned to the database.
  3. 然后,数据库引擎Database Engine使用空页填充数据库的剩余部分,包含记录数据库中空间使用情况的内部数据页除外。The 数据库引擎Database Engine then fills the rest of the database with empty pages, except for pages that have internal data that records how the space is used in the database.

在一个 SQL ServerSQL Server的实例中最多可以指定 32,767 个数据库。A maximum of 32,767 databases can be specified on an instance of SQL ServerSQL Server.

每个数据库都有一个所有者,它可以在数据库中执行特殊操作。Each database has an owner that can perform special activities in the database. 所有者是创建数据库的用户。The owner is the user that creates the database. 数据库所有者可使用 ALTER AUTHORIZATION 方法进行更改。The database owner can be changed by using ALTER AUTHORIZATION.

某些数据库功能依赖文件系统中存在的功能来实现数据库的完整功能。Some database features depend on features or capabilities present in the file system for full functionality of a database. 依赖于文件系统功能集的功能的一些示例包括:Some examples of features that depend on file system feature set include:

  • DBCC CHECKDBDBCC CHECKDB
  • FileStreamFileStream
  • 使用 VSS 和文件快照的联机备份Online backups using VSS and file snapshots
  • 数据库快照创建Database snapshot creation
  • 内存优化数据文件组Memory Optimized Data filegroup

数据库文件和文件组Database Files and Filegroups

每个数据库至少有两个文件(一个主文件和一个事务日志文件)和一个文件组 。Every database has at least two files, a primary file and a transaction log file, and at least one filegroup. 可以为每个数据库指定最多 32,767 个文件和 32,767 个文件组。A maximum of 32,767 files and 32,767 filegroups can be specified for each database.

在创建数据库时,请根据数据库中预期的最大数据量,创建尽可能大的数据文件。When you create a database, make the data files as large as possible based on the maximum amount of data you expect in the database.

建议使用存储区域网络 (SAN)、基于 iSCSI 的网络或本地附加的磁盘来存储 SQL ServerSQL Server 数据库文件,因为该配置使 SQL ServerSQL Server 的性能和可靠性得到了优化。We recommend that you use a Storage Area Network (SAN), iSCSI-based network, or locally attached disk for the storage of your SQL ServerSQL Server database files, because this configuration optimizes SQL ServerSQL Server performance and reliability.

数据库快照Database Snapshots

可以使用 CREATE DATABASE 语句创建源数据库的只读静态视图(数据库快照) 。You can use the CREATE DATABASE statement to create a read-only, static view, a database snapshot of the source database. 当创建快照时,源数据库已存在,所以数据库快照在事务上与源数据库一致。A database snapshot is transactionally consistent with the source database as it existed at the time when the snapshot was created. 源数据库可以具有多个快照。A source database can have multiple snapshots.

备注

创建数据库快照时,CREATE DATABASE 语句不能引用日志文件、脱机文件、还原文件和不存在的文件。When you create a database snapshot, the CREATE DATABASE statement cannot reference log files, offline files, restoring files, and defunct files.

如果创建数据库快照失败,快照便成为可疑快照,必须将其删除。If creating a database snapshot fails, the snapshot becomes suspect and must be deleted. 有关详细信息,请参阅 DROP DATABASEFor more information, see DROP DATABASE.

每个快照都将一直存在,直到使用 DROP DATABASE 将其删除为止。Each snapshot persists until it is deleted by using DROP DATABASE.

有关详细信息,请参阅数据库快照For more information, see Database Snapshots.

数据库选项Database Options

创建数据库时,总会自动设置几个数据库选项。Several database options are automatically set whenever you create a database. 有关这些选项的列表,请参阅 ALTER DATABASE SET 选项For a list of these options, see ALTER DATABASE SET Options.

model 数据库和创建新数据库The model Database and Creating New Databases

model 数据库中的所有用户定义对象都会复制到所有新创建的数据库中。All user-defined objects in the model database are copied to all newly created databases. 可以向 model 数据库中添加任何对象(例如表、视图、存储过程、数据类型等),以将这些对象包括到所有新建数据库中。You can add any objects, such as tables, views, stored procedures, data types, and so on, to the model database to be included in all newly created databases.

指定 CREATE DATABASE <database_name> 语句而不带其他大小参数时,主数据文件与模型数据库中的主文件具有相同的大小。When a CREATE DATABASE <database_name> statement is specified without additional size parameters, the primary data file is made the same size as the primary file in the model database.

除非指定了 FOR ATTACH,否则每个新数据库都从模型数据库继承数据库选项设置。Unless FOR ATTACH is specified, each new database inherits the database option settings from the model database. 例如,在 model 数据库和任何新建数据库中,数据库选项 auto shrink 都设置为 trueFor example, the database option auto shrink is set to true in model and in any new databases you create. 如果更改了 model 数据库中的选项,则这些新选项设置也将用于您所创建的所有新数据库中。If you change the options in the model database, these new option settings are used in any new databases you create. 在 model 数据库中的更改操作不会影响现有数据库。Changing operations in the model database does not affect existing databases. 如果在 CREATE DATABASE 语句中指定了 FOR ATTACH,则新数据库将继承原始数据库的数据库选项设置。If FOR ATTACH is specified on the CREATE DATABASE statement, the new database inherits the database option settings of the original database.

查看数据库信息Viewing Database Information

可以使用目录视图、系统函数和系统存储过程返回有关数据库、文件和文件组的信息。You can use catalog views, system functions, and system stored procedures to return information about databases, files, and filegroups. 有关详细信息,请参阅系统视图For more information, see System Views.

权限Permissions

需要 CREATE DATABASECREATE ANY DATABASEALTER ANY DATABASE 权限。Requires CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission.

为了控制对运行 SQL ServerSQL Server实例的计算机上的磁盘使用,通常只有少数登录帐户才有创建数据库的权限。To maintain control over disk use on an instance of SQL ServerSQL Server, permission to create databases is typically limited to a few login accounts.

以下示例向数据库用户 Fay 提供创建数据库的权限。The following example provides the permission to create a database to the database user Fay.

USE master;
GO
GRANT CREATE DATABASE TO [Fay];
GO

对数据文件和日志文件的权限Permissions on Data and Log Files

SQL ServerSQL Server 中,会对每个数据库的数据文件和日志文件设置特定的权限。In SQL ServerSQL Server, certain permissions are set on the data and log files of each database. 每当对数据库执行下列操作时,便会设置下列权限:The following permissions are set whenever the following operations are applied to a database:

  • 附加Attached
  • 备份Backed up
  • 创建Created
  • 分离Detached
  • 修改以添加新文件Modified to add a new file
  • 还原Restored

如果这些文件位于具有打开权限的目录中,那么以上权限可以防止文件被意外篡改。The permissions prevent the files from being accidentally tampered with if they reside in a directory that has open permissions.

备注

MicrosoftMicrosoft SQL Server 2005 Express EditionSQL Server 2005 Express Edition 不设置数据文件和日志文件权限。SQL Server 2005 Express EditionSQL Server 2005 Express Edition does not set data and log file permissions.

示例Examples

A.A. 创建未指定文件的数据库Creating a database without specifying files

以下示例创建名为 mytest 的数据库,并创建相应的主文件和事务日志文件。The following example creates the database mytest and creates a corresponding primary and transaction log file. 因为语句没有 <filespec> 项,所以主数据库文件的大小为 model 数据库主文件的大小。Because the statement has no <filespec> items, the primary database file is the size of the model database primary file. 事务日志设置为下面这些值中较大的一个:512KB 或主数据文件大小的 25%。The transaction log is set to the larger of these values: 512KB or 25% the size of the primary data file. 因为没有指定 MAXSIZE,文件可以增大到填满所有可用的磁盘空间为止。Because MAXSIZE is not specified, the files can grow to fill all available disk space. 此示例演示如何在创建 mytest 数据库之前删除名为 mytest 的数据库(如果它存在)。This example also demonstrates how to drop the database named mytest if it exists, before creating the mytest database.

USE master;
GO
IF DB_ID (N'mytest') IS NOT NULL
DROP DATABASE mytest;
GO
CREATE DATABASE mytest;
GO
-- Verify the database files and sizes
SELECT name, size, size*1.0/128 AS [Size in MBs]
FROM sys.master_files
WHERE name = N'mytest';
GO

B.B. 创建指定数据和事务日志文件的数据库Creating a database that specifies the data and transaction log files

以下示例会创建数据库 SalesThe following example creates the database Sales. 由于未使用关键字 PRIMARY,因此第一个文件 (Sales_dat) 将成为主文件。Because the keyword PRIMARY is not used, the first file (Sales_dat) becomes the primary file. 因为在 Sales_dat 文件的 SIZE 参数中没有指定 MB 或 KB,将使用 MB 并按 MB 分配。Because neither MB nor KB is specified in the SIZE parameter for the Sales_dat file, it uses MB and is allocated in megabytes. Sales_log 文件以 MB 为单位进行分配,因为 SIZE 参数中显式声明了 MB 后缀。The Sales_log file is allocated in megabytes because the MB suffix is explicitly stated in the SIZE parameter.

USE master;
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\saledat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\salelog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;
GO

C.C. 通过指定多个数据和事务日志文件创建数据库Creating a database by specifying multiple data and transaction log files

以下示例创建数据库 Archive,该数据库具有三个 100-MB 数据文件和两个 100-MB 事务日志文件。The following example creates the database Archive that has three 100-MB data files and two 100-MB transaction log files. 主文件是列表中的第一个文件,并使用 PRIMARY 关键字显式指定。The primary file is the first file in the list and is explicitly specified with the PRIMARY keyword. 事务日志文件在 LOG ON 关键字后指定。The transaction log files are specified following the LOG ON keywords. 请注意用于 FILENAME 选项中各文件的扩展名:.mdf 用于主数据文件,.ndf 用于辅助数据文件,.ldf 用于事务日志文件。Note the extensions used for the files in the FILENAME option: .mdf is used for primary data files, .ndf is used for the secondary data files, and .ldf is used for transaction log files. 此示例将数据库放置于 D: 驱动器上,而非 master 数据库中。This example places the database on the D: drive instead of with the master database.

USE master;
GO
CREATE DATABASE Archive
ON
PRIMARY
    (NAME = Arch1,
    FILENAME = 'D:\SalesData\archdat1.mdf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Arch2,
    FILENAME = 'D:\SalesData\archdat2.ndf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Arch3,
    FILENAME = 'D:\SalesData\archdat3.ndf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20)
LOG ON
  (NAME = Archlog1,
    FILENAME = 'D:\SalesData\archlog1.ldf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
  (NAME = Archlog2,
    FILENAME = 'D:\SalesData\archlog2.ldf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20) ;
GO

D.D. 创建具有文件组的数据库Creating a database that has filegroups

以下示例创建数据库 Sales,该数据库具有以下文件组:The following example creates the database Sales that has the following filegroups:

  • 包含文件 Spri1_datSpri2_dat 的主文件组。The primary filegroup with the files Spri1_dat and Spri2_dat. 将这些文件的 FILEGROWTH 增量指定为 15%The FILEGROWTH increments for these files are specified as 15%.
  • 名为 SalesGroup1 的文件组,其中包含文件 SGrp1Fi1SGrp1Fi2A filegroup named SalesGroup1 with the files SGrp1Fi1 and SGrp1Fi2.
  • 名为 SalesGroup2 的文件组,其中包含文件 SGrp2Fi1SGrp2Fi2A filegroup named SalesGroup2 with the files SGrp2Fi1 and SGrp2Fi2.

此示例将数据和日志文件放置于不同的磁盘上,以便提高性能。This example places the data and log files on different disks to improve performance.

USE master;
GO
CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
    FILENAME = 'D:\SalesData\SPri1dat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 15% ),
( NAME = SPri2_dat,
    FILENAME = 'D:\SalesData\SPri2dt.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
    FILENAME = 'D:\SalesData\SG1Fi1dt.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
    FILENAME = 'D:\SalesData\SG1Fi2dt.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
    FILENAME = 'D:\SalesData\SG2Fi1dt.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
    FILENAME = 'D:\SalesData\SG2Fi2dt.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
    FILENAME = 'E:\SalesLog\salelog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;
GO

E.E. 附加数据库Attaching a database

以下示例分离在示例 D 中创建的数据库 Archive,然后使用 FOR ATTACH 子句附加该数据库。The following example detaches the database Archive created in example D, and then attaches it by using the FOR ATTACH clause. Archive 定义为具有多个数据和日志文件。Archive was defined to have multiple data and log files. 但是,由于文件的位置自创建后没有发生更改,所以只需在 FOR ATTACH 子句中指定主文件。However, because the location of the files has not changed since they were created, only the primary file has to be specified in the FOR ATTACH clause. SQL Server 2005 (9.x)SQL Server 2005 (9.x) 开始,要附加的数据库中包含的所有全文文件也将随数据库一起附加。Beginning with SQL Server 2005 (9.x)SQL Server 2005 (9.x), any full-text files that are part of the database that is being attached will be attached with the database.

USE master;
GO
sp_detach_db Archive;
GO
CREATE DATABASE Archive
  ON (FILENAME = 'D:\SalesData\archdat1.mdf')
  FOR ATTACH ;
GO

F.F. 创建数据库快照Creating a database snapshot

以下示例会创建数据库快照 sales_snapshot0600The following example creates the database snapshot sales_snapshot0600. 由于数据库快照是只读的,所以不能指定日志文件。Because a database snapshot is read-only, a log file cannot be specified. 为了符合语法要求,指定了源数据库中的每个文件,但没有指定文件组。In conformance with the syntax, every file in the source database is specified, and filegroups are not specified.

该示例的源数据库是在示例 D 中创建的 Sales 数据库。The source database for this example is the Sales database created in example D.

USE master;
GO
CREATE DATABASE sales_snapshot0600 ON
    ( NAME = SPri1_dat, FILENAME = 'D:\SalesData\SPri1dat_0600.ss'),
    ( NAME = SPri2_dat, FILENAME = 'D:\SalesData\SPri2dt_0600.ss'),
    ( NAME = SGrp1Fi1_dat, FILENAME = 'D:\SalesData\SG1Fi1dt_0600.ss'),
    ( NAME = SGrp1Fi2_dat, FILENAME = 'D:\SalesData\SG1Fi2dt_0600.ss'),
    ( NAME = SGrp2Fi1_dat, FILENAME = 'D:\SalesData\SG2Fi1dt_0600.ss'),
    ( NAME = SGrp2Fi2_dat, FILENAME = 'D:\SalesData\SG2Fi2dt_0600.ss')
AS SNAPSHOT OF Sales ;
GO

G.G. 创建数据库并指定排序规则名称和选项Creating a database and specifying a collation name and options

以下示例会创建数据库 MyOptionsTestThe following example creates the database MyOptionsTest. 指定了排序规则名称,并将 TRUSTYWORTHYDB_CHAINING 选项设置为 ONA collation name is specified and the TRUSTYWORTHY and DB_CHAINING options are set to ON.

USE master;
GO
IF DB_ID (N'MyOptionsTest') IS NOT NULL
DROP DATABASE MyOptionsTest;
GO
CREATE DATABASE MyOptionsTest
COLLATE French_CI_AI
WITH TRUSTWORTHY ON, DB_CHAINING ON;
GO
--Verifying collation and option settings.
SELECT name, collation_name, is_trustworthy_on, is_db_chaining_on
FROM sys.databases
WHERE name = N'MyOptionsTest';
GO

H.H. 附加已移动的全文目录Attaching a full-text catalog that has been moved

以下示例同时附加全文目录 AdvWksFtCat 以及 AdventureWorks2012 数据和日志文件。The following example attaches the full-text catalog AdvWksFtCat along with the AdventureWorks2012 data and log files. 在该示例中,将全文目录从其默认位置移动到新位置 c:\myFTCatalogsIn this example, the full-text catalog is moved from its default location to a new location c:\myFTCatalogs. 数据和日志文件保留在其默认位置。The data and log files remain in their default locations.

USE master;
GO
--Detach the AdventureWorks2012 database
sp_detach_db AdventureWorks2012;
GO
-- Physically move the full text catalog to the new location.
--Attach the AdventureWorks2012 database and specify the new location of the full-text catalog.
CREATE DATABASE AdventureWorks2012 ON
    (FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_data.mdf'),
    (FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_log.ldf'),
    (FILENAME = 'c:\myFTCatalogs\AdvWksFtCat')
FOR ATTACH;
GO

I.I. 创建指定一个行文件组和两个 FILESTREAM 文件组的数据库Creating a database that specifies a row filegroup and two FILESTREAM filegroups

下面的示例将创建数据库 FileStreamDBThe following example creates the FileStreamDB database. 该数据库在创建之时包含一个行文件组和两个 FILESTREAM 文件组。The database is created with one row filegroup and two FILESTREAM filegroups. 每个文件组都包含一个文件:Each filegroup contains one file:

  • FileStreamDB_data 包含行数据。FileStreamDB_data contains row data. 它包含一个文件,即带有默认路径的 FileStreamDB_data.mdfIt contains one file, FileStreamDB_data.mdf with the default path.
  • FileStreamPhotos 包含 FILESTREAM 数据。FileStreamPhotos contains FILESTREAM data. 它包含两个 FILESTREAM 数据容器,FSPhotos(位于 C:\MyFSfolder\Photos)和 FSPhotos2(位于 D:\MyFSfolder\Photos)。It contains two FILESTREAM data containers, FSPhotos, located at C:\MyFSfolder\Photos and FSPhotos2, located at D:\MyFSfolder\Photos. 它被标记为默认 FILESTREAM 文件组。It is marked as the default FILESTREAM filegroup.
  • FileStreamResumes 包含 FILESTREAM 数据。FileStreamResumes contains FILESTREAM data. 它包含一个位于C:\MyFSfolder\Resumes中的 FILESTREAM 数据容器, FSResumesIt contains one FILESTREAM data container, FSResumes, located at C:\MyFSfolder\Resumes.
USE master;
GO
-- Get the SQL Server data path.
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
      FROM master.sys.master_files
      WHERE database_id = 1 AND file_id = 1);

 -- Execute the CREATE DATABASE statement.
EXECUTE ('CREATE DATABASE FileStreamDB
ON PRIMARY
    (
    NAME = FileStreamDB_data
    ,FILENAME = ''' + @data_path + 'FileStreamDB_data.mdf''
    ,SIZE = 10MB
    ,MAXSIZE = 50MB
    ,FILEGROWTH = 15%
    ),
FILEGROUP FileStreamPhotos CONTAINS FILESTREAM DEFAULT
    (
    NAME = FSPhotos
    ,FILENAME = ''C:\MyFSfolder\Photos''
-- SIZE and FILEGROWTH should not be specified here.
-- If they are specified an error will be raised.
, MAXSIZE = 5000 MB
    ),
    (
      NAME = FSPhotos2
      , FILENAME = ''D:\MyFSfolder\Photos''
      , MAXSIZE = 10000 MB
     ),
FILEGROUP FileStreamResumes CONTAINS FILESTREAM
    (
    NAME = FileStreamResumes
    ,FILENAME = ''C:\MyFSfolder\Resumes''
    )
LOG ON
    (
    NAME = FileStream_log
    ,FILENAME = ''' + @data_path + 'FileStreamDB_log.ldf''
    ,SIZE = 5MB
    ,MAXSIZE = 25MB
    ,FILEGROWTH = 5MB
    )'
);
GO

J.J. 创建一个数据库,该数据库具有含多个文件的 FILESTREAM 文件组Creating a database that has a FILESTREAM filegroup with multiple files

下面的示例将创建数据库 BlobStore1The following example creates the BlobStore1 database. 该数据库在创建之时包含一个行文件组和一个 FILESTREAM 文件组 FSThe database is created with one row filegroup and one FILESTREAM filegroup, FS. 该 FILESTREAM 文件组包含两个文件:FS1FS2The FILESTREAM filegroup contains two files, FS1 and FS2. 然后,通过将第三个文件 FS3 添加到 FILESTREAM 文件组来改变该数据库。Then the database is altered by adding a third file, FS3, to the FILESTREAM filegroup.

USE master;
GO

CREATE DATABASE [BlobStore1]
CONTAINMENT = NONE
ON PRIMARY
(
    NAME = N'BlobStore1',
    FILENAME = N'C:\BlobStore\BlobStore1.mdf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1MB
),
FILEGROUP [FS] CONTAINS FILESTREAM DEFAULT
(  
    NAME = N'FS1',
    FILENAME = N'C:\BlobStore\FS1',
    MAXSIZE = UNLIMITED
),
(
    NAME = N'FS2',
    FILENAME = N'C:\BlobStore\FS2',
    MAXSIZE = 100MB
)
LOG ON
(
    NAME = N'BlobStore1_log',
    FILENAME = N'C:\BlobStore\BlobStore1_log.ldf',
    SIZE = 100MB,
    MAXSIZE = 1GB,
    FILEGROWTH = 1MB
);
GO

ALTER DATABASE [BlobStore1]
ADD FILE
(
    NAME = N'FS3',
    FILENAME = N'C:\BlobStore\FS3',
    MAXSIZE = 100MB
)
TO FILEGROUP [FS];
GO

另请参阅See Also

* SQL 数据库 ** SQL Database *

 

SQL 数据库SQL Database

概述Overview

Azure SQL 数据库Azure SQL Database 中,此语句可与 Azure SQL 服务器一起使用,从而创建单一数据库或在弹性池中创建数据库。In Azure SQL 数据库Azure SQL Database, this statement can be used with an Azure SQL server to create a single database or a database in an elastic pool. 使用此语句,可以指定数据库名称、排序规则、最大大小、版本、服务目标以及新数据库的弹性池(如果适用)。With this statement, you specify the database name, collation, maximum size, edition, service objective, and, if applicable, the elastic pool for the new database. 它还可用于在弹性池中创建数据库。It can also be used to create the database in an elastic pool. 此外,它还可用于在其他 SQL 数据库服务器上创建数据库的副本。Additionally, it can be used to create a copy of the database on another SQL Database server.

语法Syntax

创建数据库Create a database

CREATE DATABASE database_name [ COLLATE collation_name ]
{
  (<edition_options> [, ...n])
}
[ WITH <with_options> [,..n]]
[;]

<with_options> ::=
{
  CATALOG_COLLATION = { DATABASE_DEFAULT | SQL_Latin1_General_CP1_CI_AS }
  | BACKUP_STORAGE_REDUNDANCY = { 'LOCAL' | 'ZONE' | 'GEO' }
}

<edition_options> ::=
{

  MAXSIZE = { 100 MB | 250 MB | 500 MB | 1 ... 1024 ... 4096 GB }
  | ( EDITION = { 'Basic' | 'Standard' | 'Premium' | 'GeneralPurpose' | 'BusinessCritical' | 'Hyperscale' }
  | SERVICE_OBJECTIVE =
    { 'Basic' | 'S0' | 'S1' | 'S2' | 'S3' | 'S4'| 'S6'| 'S7'| 'S9'| 'S12'
      | 'P1' | 'P2' | 'P4'| 'P6' | 'P11' | 'P15'
      | 'GP_Gen4_1' | 'GP_Gen4_2' | 'GP_Gen4_3' | 'GP_Gen4_4' | 'GP_Gen4_5' | 'GP_Gen4_6'
      | 'GP_Gen4_7' | 'GP_Gen4_8' | 'GP_Gen4_9' | 'GP_Gen4_10' | 'GP_Gen4_16' | 'GP_Gen4_24'
      | 'GP_Gen5_2' | 'GP_Gen5_4' | 'GP_Gen5_6' | 'GP_Gen5_8' | 'GP_Gen5_10' | 'GP_Gen5_12' | 'GP_Gen5_14'
      | 'GP_Gen5_16' | 'GP_Gen5_18' | 'GP_Gen5_20' | 'GP_Gen5_24' | 'GP_Gen5_32' | 'GP_Gen5_40' | 'GP_Gen5_80'
      | 'GP_Fsv2_8' | 'GP_Fsv2_10' | 'GP_Fsv2_12' | 'GP_Fsv2_14' | 'GP_Fsv2_16' | 'GP_Fsv2_18'
      | 'GP_Fsv2_20' | 'GP_Fsv2_24' | 'GP_Fsv2_32' | 'GP_Fsv2_36' | 'GP_Fsv2_72'
      | 'GP_S_Gen5_1' | 'GP_S_Gen5_2' | 'GP_S_Gen5_4' | 'GP_S_Gen5_6' | 'GP_S_Gen5_8'
      | 'GP_S_Gen5_10' | 'GP_S_Gen5_12' | 'GP_S_Gen5_14' | 'GP_S_Gen5_16'
      | 'GP_S_Gen5_18' | 'GP_S_Gen5_20' | 'GP_S_Gen5_24' | 'GP_S_Gen5_32' | 'GP_S_Gen5_40'
      | 'BC_Gen4_1' | 'BC_Gen4_2' | 'BC_Gen4_3' | 'BC_Gen4_4' | 'BC_Gen4_5' | 'BC_Gen4_6'
      | 'BC_Gen4_7' | 'BC_Gen4_8' | 'BC_Gen4_9' | 'BC_Gen4_10' | 'BC_Gen4_16' | 'BC_Gen4_24'
      | 'BC_Gen5_2' | 'BC_Gen5_4' | 'BC_Gen5_6' | 'BC_Gen5_8' | 'BC_Gen5_10' | 'BC_Gen5_12' | 'BC_Gen5_14'
      | 'BC_Gen5_16' | 'BC_Gen5_18' | 'BC_Gen5_20' | 'BC_Gen5_24' | 'BC_Gen5_32' | 'BC_Gen5_40' | 'BC_Gen5_80'
      | 'BC_M_8' | 'BC_M_10' | 'BC_M_12' | 'BC_M_14' | 'BC_M_16' | 'BC_M_18'
      | 'BC_M_20' | 'BC_M_24' | 'BC_M_32' | 'BC_M_64' | 'BC_M_128'
      | 'HS_GEN4_1' | 'HS_GEN4_2' | 'HS_GEN4_4' | 'HS_GEN4_8' | 'HS_GEN4_16' | 'HS_GEN4_24'
      | 'HS_GEN5_2' | 'HS_GEN5_4' | 'HS_GEN5_8' | 'HS_GEN5_16' | 'HS_GEN5_24' | 'HS_GEN5_32' | 'HS_GEN5_48' | 'HS_GEN5_80'
      | { ELASTIC_POOL(name = <elastic_pool_name>) } })
}

复制数据库Copy a database

CREATE DATABASE database_name
    AS COPY OF [source_server_name.] source_database_name
    [ ( SERVICE_OBJECTIVE =
      { 'Basic' |'S0' | 'S1' | 'S2' | 'S3'| 'S4'| 'S6'| 'S7'| 'S9'| 'S12'
      | 'P1' | 'P2' | 'P4'| 'P6' | 'P11' | 'P15'
      | 'GP_Gen4_1' | 'GP_Gen4_2' | 'GP_Gen4_3' | 'GP_Gen4_4' | 'GP_Gen4_5' | 'GP_Gen4_6'
      | 'GP_Gen4_7' | 'GP_Gen4_8' | 'GP_Gen4_9' | 'GP_Gen4_10' | 'GP_Gen4_16' | 'GP_Gen4_24'
      | 'GP_Gen5_2' | 'GP_Gen5_4' | 'GP_Gen5_6' | 'GP_Gen5_8' | 'GP_Gen5_10' | 'GP_Gen5_12' | 'GP_Gen5_14'
      | 'GP_Gen5_16' | 'GP_Gen5_18' | 'GP_Gen5_20' | 'GP_Gen5_24' | 'GP_Gen5_32' | 'GP_Gen5_40' | 'GP_Gen5_80'
      | 'GP_Fsv2_8' | 'GP_Fsv2_10' | 'GP_Fsv2_12' | 'GP_Fsv2_14' | 'GP_Fsv2_16' | 'GP_Fsv2_18'
      | 'GP_Fsv2_20' | 'GP_Fsv2_24' | 'GP_Fsv2_32' | 'GP_Fsv2_36' | 'GP_Fsv2_72'
      | 'GP_S_Gen5_1' | 'GP_S_Gen5_2' | 'GP_S_Gen5_4' | 'GP_S_Gen5_6' | 'GP_S_Gen5_8'
      | 'GP_S_Gen5_10' | 'GP_S_Gen5_12' | 'GP_S_Gen5_14' | 'GP_S_Gen5_16'
      | 'GP_S_Gen5_18' | 'GP_S_Gen5_20' | 'GP_S_Gen5_24' | 'GP_S_Gen5_32' | 'GP_S_Gen5_40'
      | 'BC_Gen4_1' | 'BC_Gen4_2' | 'BC_Gen4_3' | 'BC_Gen4_4' | 'BC_Gen4_5' | 'BC_Gen4_6'
      | 'BC_Gen4_7' | 'BC_Gen4_8' | 'BC_Gen4_9' | 'BC_Gen4_10' | 'BC_Gen4_16' | 'BC_Gen4_24'
      | 'BC_Gen5_2' | 'BC_Gen5_4' | 'BC_Gen5_6' | 'BC_Gen5_8' | 'BC_Gen5_10' | 'BC_Gen5_12' | 'BC_Gen5_14'
      | 'BC_Gen5_16' | 'BC_Gen5_18' | 'BC_Gen5_20' | 'BC_Gen5_24' | 'BC_Gen5_32' | 'BC_Gen5_40' | 'BC_Gen5_80'
      | 'BC_M_8' | 'BC_M_10' | 'BC_M_12' | 'BC_M_14' | 'BC_M_16' | 'BC_M_18'
      | 'BC_M_20' | 'BC_M_24' | 'BC_M_32' | 'BC_M_64' | 'BC_M_128'
      | 'HS_GEN4_1' | 'HS_GEN4_2' | 'HS_GEN4_4' | 'HS_GEN4_8' | 'HS_GEN4_16' | 'HS_GEN4_24'
      | 'HS_GEN5_2' | 'HS_GEN5_4' | 'HS_GEN5_8' | 'HS_GEN5_16' | 'HS_GEN5_24' | 'HS_GEN5_32' | 'HS_GEN5_48' | 'HS_GEN5_80'
      | { ELASTIC_POOL(name = <elastic_pool_name>) } })
   ]
[;]

参数Arguments

database_name 新数据库的名称。database_name The name of the new database. 此名称在 SQL ServerSQL Server 上必须唯一,并且必须符合 SQL ServerSQL Server 的标识符规则。This name must be unique on the SQL ServerSQL Server and comply with the SQL ServerSQL Server rules for identifiers. 有关详细信息,请参阅标识符For more information, see Identifiers.

Collation_name 指定数据库的默认排序规则。Collation_name Specifies the default collation for the database. 排序规则名称既可以是 Windows 排序规则名称,也可以是 SQL 排序规则名称。Collation name can be either a Windows collation name or a SQL collation name. 如果未指定,则会为数据库分配默认排序规则(即 SQL_Latin1_General_CP1_CI_AS)。If not specified, the database is assigned the default collation, which is SQL_Latin1_General_CP1_CI_AS.

有关 Windows 和 SQL 排序规则名称的详细信息,请参阅 COLLATE (Transact-SQL)For more information about the Windows and SQL collation names, COLLATE (Transact-SQL).

CATALOG_COLLATION 指定元数据目录的默认排序规则。CATALOG_COLLATION Specifies the default collation for the metadata catalog. DATABASE_DEFAULT 指定用于系统视图和系统表的元数据目录按数据库的默认排序规则进行整理。DATABASE_DEFAULT specifies that the metadata catalog used for system views and system tables be collated to match the default collation for the database. 这是在 SQL Server 中所发现的行为。This is the behavior found in SQL Server.

SQL_Latin1_General_CP1_CI_AS 指定用于系统视图和表的元数据目录按固定的 SQL_Latin1_General_CP1_CI_AS 排序规则进行整理。SQL_Latin1_General_CP1_CI_AS specifies that the metadata catalog used for system views and tables be collated to a fixed SQL_Latin1_General_CP1_CI_AS collation. 如果未指定,这将是 Azure SQL 数据库上的默认设置。This is the default setting on Azure SQL Database if unspecified.

BACKUP_STORAGE_REDUNDANCY 指定如何复制数据库的时间点还原备份和长期保留备份。BACKUP_STORAGE_REDUNDANCY Specifies how the point-in-time restore and long-term retention backups for a database are replicated. 仅当使用“异地”备份存储冗余创建数据库时,“异地还原”或“从区域中断恢复”功能才可用。Geo restore or ability to recover from regional outage is only available when database is created with 'GEO' backup storage redundancy. 除非显式指定,否则通过 T-SQL 创建的数据库将使用异地冗余备份存储。Unless explicitly specified, databases created with T-SQL use geo-redundant backup storage.

重要

Azure SQL 数据库的 BACKUP_STORAGE_REDUNDANCY 选项为公共预览版,且仅在东南亚 Azure 区域提供。BACKUP_STORAGE_REDUNDANCY option for Azure SQL Database is available in public preview in Southeast Asia Azure region only.

EDITION 指定数据库的服务层。EDITION Specifies the service tier of the database.

单一数据库和共用数据库。Single and pooled databases. 可用值有:'Basic'、'Standard'、'Premium'、'GeneralPurpose'、'BusinessCritical' 和 'Hyperscale'。The available values are: 'Basic', 'Standard', 'Premium', 'GeneralPurpose', 'BusinessCritical', and 'Hyperscale'.

MAXSIZE 指定数据库的最大大小。MAXSIZE Specifies the maximum size of the database. MAXSIZE 必须对指定 EDITION(服务层)有效。下面是服务层支持的 MAXSIZE 值和默认值 (D)。MAXSIZE must be valid for the specified EDITION (service tier) Following are the supported MAXSIZE values and defaults (D) for the service tiers.

备注

MAXSIZE 参数不适用于超大规模服务层中的单一数据库。The MAXSIZE argument does not apply to single databases in the Hyperscale service tier. 超大规模层数据库根据需要而增长,最大 100 TB。Hyperscale tier databases grow as needed, up to 100 TB. SQL 数据库服务会自动添加存储空间,而无需设置最大大小。The SQL Database service adds storage automatically - you do not need to set a maximum size.

SQL 数据库服务器上适用于单一数据库和共用数据库的 DTU 模型DTU model for single and pooled databases on a SQL Database server

MAXSIZEMAXSIZE 基本Basic S0-S2S0-S2 S3-S12S3-S12 P1-P6P1-P6 P11-P15P11-P15
100 MB100 MB
250 MB250 MB
500 MB500 MB
1 GB1 GB
2 GB2 GB √ (D)√ (D)
5 GB5 GB 空值N/A
10 GB10 GB 空值N/A
20 GB20 GB 空值N/A
30 GB30 GB 空值N/A
40 GB40 GB 空值N/A
50 GB50 GB 空值N/A
100 GB100 GB 空值N/A
150 GB150 GB 空值N/A
200 GB200 GB 空值N/A
250 GB250 GB 空值N/A √ (D)√ (D) √ (D)√ (D)
300 GB300 GB 空值N/A 空值N/A
400 GB400 GB 空值N/A 空值N/A
500 GB500 GB 空值N/A 空值N/A √ (D)√ (D)
750 GB750 GB 空值N/A 空值N/A
1024 GB1024 GB 空值N/A 空值N/A √ (D)√ (D)
从 1024 GB 到最大 4096 GB,增量为 256 GB*From 1024 GB up to 4096 GB in increments of 256 GB* 空值N/A 空值N/A 空值N/A 空值N/A

* P11 和 P15 允许 MAXSIZE 达到 4 TB,默认大小为 1024 GB。* P11 and P15 allow MAXSIZE up to 4 TB with 1024 GB being the default size. P11 和 P15 可以使用最大 4 TB 的内含存储,且无需额外费用。P11 and P15 can use up to 4 TB of included storage at no additional charge. 在高级层中,目前在以下区域提供大于 1 TB 的 MAXSIZE:美国东部 2、美国西部、US Gov 弗吉尼亚州、西欧、德国中部、东南亚、日本东部、澳大利亚东部、加拿大中部和加拿大东部。In the Premium tier, MAXSIZE greater than 1 TB is currently available in the following regions: US East2, West US, US Gov Virginia, West Europe, Germany Central, South East Asia, Japan East, Australia East, Canada Central, and Canada East. 有关 DTU 模型资源限制的其他详细信息,请参阅 DTU 资源限制For additional details regarding resource limitations for the DTU model, see DTU resource limits.

DTU 模型的 MAXSIZE 值(如果指定)必须为上表中所示的指定服务层的有效值。The MAXSIZE value for the DTU model, if specified, has to be a valid value shown in the table above for the service tier specified.

vCore 模型vCore model

常规用途 - 预配的计算 - Gen4 (第 1 部分)General purpose - provisioned compute - Gen4 (part 1)

MAXSIZEMAXSIZE GP_Gen4_1GP_Gen4_1 GP_Gen4_2GP_Gen4_2 GP_Gen4_3GP_Gen4_3 GP_Gen4_4GP_Gen4_4 GP_Gen4_5GP_Gen4_5 GP_Gen4_6GP_Gen4_6
最大数据大小 (GB)Max data size (GB) 10241024 10241024 10241024 15361536 15361536 15361536

常规用途 - 预配的计算 - Gen4(第 2 部分)General purpose - provisioned compute - Gen4 (part 2)

MAXSIZEMAXSIZE GP_Gen4_7GP_Gen4_7 GP_Gen4_8GP_Gen4_8 GP_Gen4_9GP_Gen4_9 GP_Gen4_10GP_Gen4_10 GP_Gen4_16GP_Gen4_16 GP_Gen4_24GP_Gen4_24
最大数据大小 (GB)Max data size (GB) 15361536 30723072 30723072 30723072 40964096 40964096

常规用途 - 预配的计算 - Gen5(第 1 部分)General purpose - provisioned compute - Gen5 (part 1)

MAXSIZEMAXSIZE GP_Gen5_2GP_Gen5_2 GP_Gen5_4GP_Gen5_4 GP_Gen5_6GP_Gen5_6 GP_Gen5_8GP_Gen5_8 GP_Gen5_10GP_Gen5_10 GP_Gen5_12GP_Gen5_12 GP_Gen5_14GP_Gen5_14
最大数据大小 (GB)Max data size (GB) 10241024 10241024 10241024 15361536 15361536 15361536 15361536

常规用途 - 预配的计算 - Gen5(第 2 部分)General purpose - provisioned compute - Gen5 (part 2)

MAXSIZEMAXSIZE GP_Gen5_16GP_Gen5_16 GP_Gen5_18GP_Gen5_18 GP_Gen5_20GP_Gen5_20 GP_Gen5_24GP_Gen5_24 GP_Gen5_32GP_Gen5_32 GP_Gen5_40GP_Gen5_40 GP_Gen5_80GP_Gen5_80
最大数据大小 (GB)Max data size (GB) 30723072 30723072 30723072 40964096 40964096 40964096 40964096

常规用途 - 预配的计算 - Fsv2 系列(第 1 部分)General purpose - provisioned compute - Fsv2-series (part 1)

MAXSIZEMAXSIZE GP_Fsv2_8GP_Fsv2_8 GP_Fsv2_10GP_Fsv2_10 GP_Fsv2_12GP_Fsv2_12 GP_Fsv2_14GP_Fsv2_14 GP_Fsv2_16GP_Fsv2_16 GP_Fsv2_18GP_Fsv2_18
最大数据大小 (GB)Max data size (GB) 10241024 10241024 10241024 10241024 15361536 15361536

常规用途 - 预配的计算 - Fsv2 系列(第 2 部分)General purpose - provisioned compute - Fsv2-series (part 2)

MAXSIZEMAXSIZE GP_Fsv2_20GP_Fsv2_20 GP_Fsv2_24GP_Fsv2_24 GP_Fsv2_32GP_Fsv2_32 GP_Fsv2_36GP_Fsv2_36 GP_Fsv2_72GP_Fsv2_72
最大数据大小 (GB)Max data size (GB) 15361536 15361536 30723072 30723072 40964096

常规用途 - 无服务器计算 - Gen5(第 1 部分)General purpose - serverless compute - Gen5 (part 1)

MAXSIZEMAXSIZE GP_S_Gen5_1GP_S_Gen5_1 GP_S_Gen5_2GP_S_Gen5_2 GP_S_Gen5_4GP_S_Gen5_4 GP_S_Gen5_6GP_S_Gen5_6 GP_S_Gen5_8GP_S_Gen5_8
最大 vCore 数Max vCores 11 22 44 66 88

常规用途 - 无服务器计算 - Gen5(第 2 部分)General purpose - serverless compute - Gen5 (part 2)

MAXSIZEMAXSIZE GP_S_Gen5_10GP_S_Gen5_10 GP_S_Gen5_12GP_S_Gen5_12 GP_S_Gen5_14GP_S_Gen5_14 GP_S_Gen5_16GP_S_Gen5_16
最大 vCore 数Max vCores 1010 1212 1414 1616

常规用途 - 无服务器计算 - Gen5(第 3 部分)General purpose - serverless compute - Gen5 (part 3)

MAXSIZEMAXSIZE GP_S_Gen5_18GP_S_Gen5_18 GP_S_Gen5_20GP_S_Gen5_20 GP_S_Gen5_24GP_S_Gen5_24 GP_S_Gen5_32GP_S_Gen5_32 GP_S_Gen5_40GP_S_Gen5_40
最大 vCore 数Max vCores 1818 2020 2424 3232 4040

业务关键 - 预配的计算 - Gen4(第 1 部分)Business critical - provisioned compute - Gen4 (part 1)

计算大小(服务目标)Compute size (service objective) BC_Gen4_1BC_Gen4_1 BC_Gen4_2BC_Gen4_2 BC_Gen4_3BC_Gen4_3 BC_Gen4_4BC_Gen4_4 BC_Gen4_5BC_Gen4_5 BC_Gen4_6BC_Gen4_6
最大数据大小 (GB)Max data size (GB) 10241024 10241024 10241024 10241024 10241024 10241024

业务关键 - 预配的计算 - Gen4(第 2 部分)Business critical - provisioned compute - Gen4 (part 2)

计算大小(服务目标)Compute size (service objective) BC_Gen4_7BC_Gen4_7 BC_Gen4_8BC_Gen4_8 BC_Gen4_9BC_Gen4_9 BC_Gen4_10BC_Gen4_10 BC_Gen4_16BC_Gen4_16 BC_Gen4_24BC_Gen4_24
最大数据大小 (GB)Max data size (GB) 10241024 10241024 10241024 10241024 10241024 10241024

业务关键 - 预配的计算 - Gen5(第 1 部分)Business critical - provisioned compute - Gen5 (part 1)

MAXSIZEMAXSIZE BC_Gen5_2BC_Gen5_2 BC_Gen5_4BC_Gen5_4 BC_Gen5_6BC_Gen5_6 BC_Gen5_8BC_Gen5_8 BC_Gen5_10BC_Gen5_10 BC_Gen5_12BC_Gen5_12 BC_Gen5_14BC_Gen5_14
最大数据大小 (GB)Max data size (GB) 10241024 10241024 10241024 15361536 15361536 15361536 15361536

业务关键 - 预配的计算 - Gen5(第 2 部分)Business critical - provisioned compute - Gen5 (part 2)

MAXSIZEMAXSIZE BC_Gen5_16BC_Gen5_16 BC_Gen5_18BC_Gen5_18 BC_Gen5_20BC_Gen5_20 BC_Gen5_24BC_Gen5_24 BC_Gen5_32BC_Gen5_32 BC_Gen5_40BC_Gen5_40 BC_Gen5_80BC_Gen5_80
最大数据大小 (GB)Max data size (GB) 30723072 30723072 30723072 40964096 40964096 40964096 40964096

业务关键 - 预配的计算 - M 系列(第 1 部分)Business critical - provisioned compute - M-series (part 1)

MAXSIZEMAXSIZE BC_M_8BC_M_8 BC_M_10BC_M_10 BC_M_12BC_M_12 BC_M_14BC_M_14 BC_M_16BC_M_16 BC_M_18BC_M_18
最大数据大小 (GB)Max data size (GB) 512512 640640 768768 896896 10241024 11521152

业务关键 - 预配的计算 - M 系列(第 2 部分)Business critical - provisioned compute - M-series (part 2)

MAXSIZEMAXSIZE BC_M_20BC_M_20 BC_M_24BC_M_24 BC_M_32BC_M_32 BC_M_64BC_M_64 BC_M_128BC_M_128
最大数据大小 (GB)Max data size (GB) 12801280 15361536 20482048 40964096 40964096

如果在使用 vCore 模型时未设置 MAXSIZE 值,默认为 32GB。If no MAXSIZE value is set when using the vCore model, the default is 32 GB. 有关 vCore 模型资源限制的其他详细信息,请参阅 vCore 资源限制For additional details regarding resource limitations for vCore model, see vCore resource limits.

以下规则适用于 MAXSIZE 和 EDITION 参数:The following rules apply to MAXSIZE and EDITION arguments:

  • 如果指定了 EDITION 但未指定 MAXSIZE,则使用版本的默认值。If EDITION is specified but MAXSIZE is not specified, the default value for the edition is used. 例如,如果 EDITION 设置为 Standard 并且未指定 MAXSIZE,则 MAXSIZE 将自动设置为 250 MB。For example, if the EDITION is set to Standard, and the MAXSIZE is not specified, then the MAXSIZE is automatically set to 250 MB.
  • 如果 MAXSIZE 和 EDITION 均未指定,则 EDITION 设置为 GeneralPurpose,MAXSIZE 设置为 32 GB。If neither MAXSIZE nor EDITION is specified, the EDITION is set to GeneralPurpose, and MAXSIZE is set to 32 GB.

SERVICE_OBJECTIVESERVICE_OBJECTIVE

  • 针对单一数据库和入池数据库For single and pooled databases

    • 指定计算大小(服务目标)。Specifies the compute size (service objective). 服务目标的可用值包括:S0S1S2S3S4S6S7S9S12P1P2P4P6P11P15GP_GEN4_1GP_GEN4_2GP_GEN4_3GP_GEN4_4GP_GEN4_5GP_GEN4_6GP_GEN4_7GP_GEN4_8GP_GEN4_7GP_GEN4_8GP_GEN4_9GP_GEN4_10GP_GEN4_16GP_GEN4_24BC_GEN4_1BC_GEN4_2BC_GEN4_3BC_GEN4_4BC_GEN4_5BC_GEN4_6BC_GEN4_7BC_GEN4_8BC_GEN4_9BC_GEN4_10BC_GEN4_16BC_GEN4_24GP_Gen5_2GP_Gen5_4GP_Gen5_6GP_Gen5_8GP_Gen5_10GP_Gen5_12GP_Gen5_14GP_Gen5_16GP_Gen5_18GP_Gen5_20GP_Gen5_24GP_Gen5_32GP_Gen5_40GP_Gen5_80GP_Fsv2_8GP_Fsv2_10GP_Fsv2_12GP_Fsv2_14GP_Fsv2_16GP_Fsv2_18GP_Fsv2_20GP_Fsv2_24GP_Fsv2_32GP_Fsv2_36GP_Fsv2_72BC_Gen5_2BC_Gen5_4BC_Gen5_6BC_Gen5_8BC_Gen5_10BC_Gen5_12BC_Gen5_14BC_Gen5_16BC_Gen5_18BC_Gen5_20BC_Gen5_24BC_Gen5_32BC_Gen5_40BC_Gen5_80BC_M_8BC_M_10BC_M_12BC_M_14BC_M_16BC_M_18BC_M_20BC_M_24BC_M_32BC_M_64BC_M_128Available values for service objective are: S0, S1, S2, S3, S4, S6, S7, S9, S12, P1, P2, P4, P6, P11, P15, GP_GEN4_1, GP_GEN4_2, GP_GEN4_3, GP_GEN4_4, GP_GEN4_5, GP_GEN4_6, GP_GEN4_7, GP_GEN4_8, GP_GEN4_7, GP_GEN4_8, GP_GEN4_9, GP_GEN4_10, GP_GEN4_16, GP_GEN4_24, BC_GEN4_1, BC_GEN4_2, BC_GEN4_3, BC_GEN4_4, BC_GEN4_5, BC_GEN4_6, BC_GEN4_7, BC_GEN4_8, BC_GEN4_9, BC_GEN4_10, BC_GEN4_16, BC_GEN4_24, GP_Gen5_2, GP_Gen5_4, GP_Gen5_6, GP_Gen5_8, GP_Gen5_10, GP_Gen5_12, GP_Gen5_14, GP_Gen5_16, GP_Gen5_18, GP_Gen5_20, GP_Gen5_24, GP_Gen5_32, GP_Gen5_40, GP_Gen5_80, GP_Fsv2_8, GP_Fsv2_10, GP_Fsv2_12, GP_Fsv2_14, GP_Fsv2_16, GP_Fsv2_18, GP_Fsv2_20, GP_Fsv2_24, GP_Fsv2_32, GP_Fsv2_36, GP_Fsv2_72, BC_Gen5_2, BC_Gen5_4, BC_Gen5_6, BC_Gen5_8, BC_Gen5_10, BC_Gen5_12, BC_Gen5_14, BC_Gen5_16, BC_Gen5_18, BC_Gen5_20, BC_Gen5_24, BC_Gen5_32,BC_Gen5_40, BC_Gen5_80, BC_M_8, BC_M_10, BC_M_12, BC_M_14, BC_M_16, BC_M_18, BC_M_20, BC_M_24, BC_M_32, BC_M_64, BC_M_128.
  • 对于无服务器数据库For serverless databases

    • 指定计算大小(服务目标)。Specifies the compute size (service objective). 服务目标的可用值包括:GP_S_Gen5_1GP_S_Gen5_2GP_S_Gen5_4GP_S_Gen5_6GP_S_Gen5_8GP_S_Gen5_10GP_S_Gen5_12GP_S_Gen5_14GP_S_Gen5_16GP_S_Gen5_18GP_S_Gen5_20GP_S_Gen5_24GP_S_Gen5_32GP_S_Gen5_40Available values for service objective are: GP_S_Gen5_1, GP_S_Gen5_2, GP_S_Gen5_4, GP_S_Gen5_6, GP_S_Gen5_8, GP_S_Gen5_10, GP_S_Gen5_12, GP_S_Gen5_14, GP_S_Gen5_16, GP_S_Gen5_18, GP_S_Gen5_20, GP_S_Gen5_24, GP_S_Gen5_32, GP_S_Gen5_40.
  • 针对超大规模服务层中的单一数据库For single databases in the Hyperscale service tier

    • 指定计算大小(服务目标)。Specifies the compute size (service objective). 服务目标的可用值包括:HS_GEN4_1 HS_GEN4_2 HS_GEN4_4 HS_GEN4_8 HS_GEN4_16HS_GEN4_24HS_Gen5_2HS_Gen5_4HS_Gen5_8HS_Gen5_16HS_Gen5_24HS_Gen5_32HS_Gen5_48HS_Gen5_80Available values for service objective are: HS_GEN4_1 HS_GEN4_2 HS_GEN4_4 HS_GEN4_8 HS_GEN4_16, HS_GEN4_24, HS_Gen5_2, HS_Gen5_4, HS_Gen5_8, HS_Gen5_16, HS_Gen5_24, HS_Gen5_32, HS_Gen5_48, HS_Gen5_80.

有关服务目标说明以及大小、版本和服务目标组合的详细信息,请参阅 Azure SQL 数据库服务层For service objective descriptions and more information about the size, editions, and the service objectives combinations, see Azure SQL Database Service Tiers. 如果 EDITION 不支持指定的 SERVICE_OBJECTIVE,你会收到一个错误。If the specified SERVICE_OBJECTIVE is not supported by the EDITION, you receive an error. 若要将 SERVICE_OBJECTIVE 值从一层更改为另一层(例如从 S1 更改为 P1),还必须更改 EDITION 值。To change the SERVICE_OBJECTIVE value from one tier to another (for example from S1 to P1), you must also change the EDITION value. 有关服务目标说明以及大小、版本和服务目标组合的详细信息,请参阅 Azure SQL 数据库服务层和性能级别DTU 资源限制vCore 资源限制For service objective descriptions and more information about the size, editions, and the service objectives combinations, see Azure SQL Database Service Tiers and Performance Levels, DTU resource limits and vCore resource limits. 删除了对 PRS 服务目标的支持。Support for PRS service objectives have been removed. 如有问题,请使用此电子邮件别名:premium-rs@microsoft.com。For questions, use this e-mail alias: premium-rs@microsoft.com.

ELASTIC_POOL (name = <elastic_pool_name>) 适用于:仅限单一数据库和池化数据库。ELASTIC_POOL (name = <elastic_pool_name>) Applies to: Single and pooled databases only. 不适用于超大规模服务层中的数据库。Does not apply to databases in the Hyperscale service tier. 要在弹性数据库池中创建新数据库,请将数据库的 SERVICE_OBJECTIVE 设置为 ELASTIC_POOL,并提供池的名称。To create a new database in an elastic database pool, set the SERVICE_OBJECTIVE of the database to ELASTIC_POOL and provide the name of the pool. 有关详细信息,请参阅弹性池有助于管理和缩放多个 Azure SQL 数据库For more information, see Create and manage a SQL Database elastic pool.

AS COPY OF [source_server_name.]source_database_name 适用于: 仅限单一数据库和池化数据库。AS COPY OF [source_server_name.]source_database_name Applies to: Single and pooled databases only. 将数据库复制到同一台或其他 SQL 数据库SQL Database 服务器上。For copying a database to the same or a different SQL 数据库SQL Database server.

source_server_name 源数据库所在的 SQL 数据库SQL Database 服务器的名称。source_server_name The name of the SQL 数据库SQL Database server where the source database is located. 当源数据库和目标数据库位于同一台 SQL 数据库SQL Database 服务器上时,此参数是可选的。This parameter is optional when the source database and the destination database are to be located on the same SQL 数据库SQL Database server.

备注

AS COPY OF 参数不支持完全限定的唯一域名。The AS COPY OF argument does not support the fully qualified unique domain names. 换言之,如果您的服务器的完全限定域名是 serverName.database.windows.net,则在数据库复制期间仅使用 serverNameIn other words, if your server's fully qualified domain name is serverName.database.windows.net, use only serverName during database copy.

source_database_namesource_database_name

要复制的数据库的名称。The name of the database that is to be copied.

备注Remarks

Azure SQL 数据库Azure SQL Database 中的数据库具有多个在创建数据库时设置的默认设置。Databases in Azure SQL 数据库Azure SQL Database have several default settings that are set when the database is created. 有关这些默认设置的详细信息,请参阅 DATABASEPROPERTYEX 中的值列表。For more information about these default settings, see the list of values in DATABASEPROPERTYEX.

MAXSIZE 提供限制数据库大小的功能。MAXSIZE provides the ability to limit the size of the database. 如果数据库的大小达到其 MAXSIZE,你将收到错误代码 40544。If the size of the database reaches its MAXSIZE, you receive error code 40544. 如果发生这种情况,您不能插入或更新数据或创建新的对象(如表、存储过程、视图和函数)。When this occurs, you cannot insert or update data, or create new objects (such as tables, stored procedures, views, and functions). 不过,您仍可以读取和删除数据、截断表、删除表和索引以及重新建立索引。However, you can still read and delete data, truncate tables, drop tables and indexes, and rebuild indexes. 然后,可以将 MAXSIZE 更新为比当前数据库大小更大的值,或者删除一些数据以释放存储空间。You can then update MAXSIZE to a value larger than your current database size or delete some data to free storage space. 在您可以插入新数据之前,可能有长达十五分钟的延迟。There may be as much as a fifteen-minute delay before you can insert new data.

若要在以后更改大小、版本或服务目标值,请使用 ALTER DATABASE - Azure SQL 数据库To change the size, edition, or service objective values later, use ALTER DATABASE - Azure SQL Database.

CATALOG_COLLATION 参数仅在数据库创建期间可用。The CATALOG_COLLATION argument is only available during database creation.

数据库复制Database Copies

适用于: 仅限单一数据库和池化数据库。Applies to: Single and pooled databases only.

使用 CREATE DATABASE 语句复制数据库是一个异步操作。Copying a database using the CREATE DATABASE statement is an asynchronous operation. 因此,在整个复制过程中,不需要与 SQL 数据库SQL Database 服务器建立连接。Therefore, a connection to the SQL 数据库SQL Database server is not needed for the full duration of the copy process. CREATE DATABASE 语句会在 sys.databases 中的条目创建之后,但是在数据库复制操作完成之前将控制权返还给用户。The CREATE DATABASE statement returns control to the user after the entry in sys.databases is created but before the database copy operation is complete. 换言之,当数据库复制仍在进行时,CREATE DATABASE 语句会成功返回。In other words, the CREATE DATABASE statement returns successfully when the database copy is still in progress.

  • SQL 数据库SQL Database 服务器上监视复制进程:在 dm_database_copies 中查询 percentage_completereplication_state_desc 列,或在 sys.databases 视图中查询 state 列。Monitoring the copy process on an SQL 数据库SQL Database server: Query the percentage_complete or replication_state_desc columns in the dm_database_copies or the state column in the sys.databases view. 可以使用 sys.dm_operation_status 视图,它还会返回数据库操作(包括数据库复制)的状态。The sys.dm_operation_status view can be used as well as it returns the status of database operations including database copy.

在复制过程成功完成后,目标数据库与源数据库在事务上是一致的。At the time the copy process completes successfully, the destination database is transactionally consistent with the source database.

以下语法和语义规则应用于您对 AS COPY OF 参数的使用:The following syntax and semantic rules apply to your use of the AS COPY OF argument:

  • 源服务器名称与复制目标的服务器名称可能相同,也可能不同。The source server name and the server name for the copy target may be the same or different. 如果相同,此参数是可选的,默认情况下将使用当前会话的服务器上下文。When they are the same, this parameter is optional and the server context of the current session is used by default.
  • 必须指定源数据库名称和目标数据库名称,并且这些名称必须唯一且符合 SQL ServerSQL Server 标识符规则。The source and destination database names must be specified, unique, and comply with the SQL ServerSQL Server rules for identifiers. 有关详细信息,请参阅标识符For more information, see Identifiers.
  • 必须在将创建新数据库的 SQL 数据库SQL Database 服务器的 master 数据库的上下文中执行 CREATE DATABASE 语句。The CREATE DATABASE statement must be executed within the context of the master database of the SQL 数据库SQL Database server where the new database will be created.
  • 在复制完成之后,必须将目标数据库作为独立的数据库进行管理。After the copying completes, the destination database must be managed as an independent database. 您可以独立于源数据库,针对新数据库执行 ALTER DATABASEDROP DATABASE 语句。You can execute the ALTER DATABASE and DROP DATABASE statements against the new database independently of the source database. 您还可以将新数据库复制到另一个新数据库。You can also copy the new database to another new database.
  • 当正在进行数据库复制时,可以继续访问源数据库。The source database may continue to be accessed while the database copy is in progress.

有关详细信息,请参阅使用 TRANSACT-SQL 创建 Azure SQL 数据库的副本For more information, see Create a copy of an Azure SQL database using Transact-SQL.

重要

默认使用与源数据库相同的备份存储冗余创建数据库副本。By default, the database copy is created with the same backup storage redundancy as that of the source database. 不支持在通过 T-SQL 创建数据库副本时更改备份存储冗余。Changing the backup storage redundancy while creating a database copy is not supported via T-SQL.

权限Permissions

要创建数据库,登录名必须为下列各项之一:To create a database, a login must be one of the following:

  • 服务器级别主体登录名The server-level principal login
  • 本地 Azure SQL Server 的 Azure AD 管理员The Azure AD administrator for the local Azure SQL Server
  • 登录名为 dbmanager 数据库角色的成员A login that is a member of the dbmanager database role

使用 CREATE DATABASE ... AS COPY OF 语法的其他要求: 在本地服务器上执行语句的登录名还必须至少是源服务器上的 db_ownerAdditional requirements for using CREATE DATABASE ... AS COPY OF syntax: The login executing the statement on the local server must also be at least the db_owner on the source server. 如果登录名基于 SQL ServerSQL Server 身份验证,那么在本地服务器上执行语句的登录名必须在源 SQL 数据库SQL Database 服务器上具有匹配的登录名,名称和密码均完全相同。If the login is based on SQL ServerSQL Server authentication, the login executing the statement on the local server must have a matching login on the source SQL 数据库SQL Database server, with an identical name and password.

示例Examples

简单示例Simple Example

创建数据库的简单示例。A simple example for creating a database.

CREATE DATABASE TestDB1;

版本的简单示例Simple Example with Edition

创建常规用途数据库的简单示例。A simple example for creating a general purpose database.

CREATE DATABASE TestDB2
( EDITION = 'GeneralPurpose' );

其他选项的示例Example with Additional Options

使用多个选项的示例。An example using multiple options.

CREATE DATABASE hito
COLLATE Japanese_Bushu_Kakusu_100_CS_AS_KS_WS
( MAXSIZE = 500 MB, EDITION = 'GeneralPurpose', SERVICE_OBJECTIVE = 'GP_GEN4_8' ) ;

创建副本Creating a Copy

创建数据库副本的示例。An example creating a copy of a database.

适用于: 仅限单一数据库和池化数据库。Applies to: Single and pooled databases only.

CREATE DATABASE escuela
AS COPY OF school;

在弹性池中创建数据库Creating a Database in an Elastic Pool

在名为 S3M100 的池中创建新数据库:Creates new database in pool named S3M100:

适用于: 仅限单一数据库和池化数据库。Applies to: Single and pooled databases only.

CREATE DATABASE db1 ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = S3M100 ) ) ;

在其他服务器上创建数据库副本Creating a Copy of a Database on Another Server

下面的示例创建 db_original 数据库的副本,将其名为 db_copy,保存在针对单个数据库的 P2 计算大小(服务对象)中。The following example creates a copy of the db_original database, named db_copy in the P2 compute size (service objective) for a single database. 无论 db_original 是位于弹性池中还是采用针对单个数据库的计算大小(服务对象),都是如此。This is true regardless of whether db_original is in an elastic pool or a compute size (service objective) for a single database.

适用于: 仅限单一数据库和池化数据库。Applies to: Single and pooled databases only.

CREATE DATABASE db_copy
  AS COPY OF ozabzw7545.db_original ( SERVICE_OBJECTIVE = 'P2' );

下面的示例在名为 ep1 的弹性池中创建名为 db_copy 的 db_original 数据库的副本。The following example creates a copy of the db_original database, named db_copy in an elastic pool named ep1. 无论 db_original 是位于弹性池中还是采用针对单个数据库的计算大小(服务对象),都是如此。This is true regardless of whether db_original is in an elastic pool or a compute size (service objective) for a single database. 如果 db_original 位于具有不同名称的弹性池中,那么仍将在 ep1 中创建 db_copy。If db_original is in an elastic pool with a different name, then db_copy is still created in ep1.

适用于: 仅限单一数据库和池化数据库。Applies to: Single and pooled databases only.

CREATE DATABASE db_copy
  AS COPY OF ozabzw7545.db_original
  (SERVICE_OBJECTIVE = ELASTIC_POOL( name = ep1 ) ) ;

使用指定的目录排序规则值创建数据库Create database with specified catalog collation value

下面的示例在数据库创建期间将目录排序规则设置为 DATABASE_DEFAULT,其会将目录排序规则设置为与数据库排序规则相同。The following example sets the catalog collation to DATABASE_DEFAULT during database creation, which sets the catalog collation to be the same as the database collation.

CREATE DATABASE TestDB3 COLLATE Japanese_XJIS_140 (MAXSIZE = 100 MB, EDITION = 'Basic')
  WITH CATALOG_COLLATION = DATABASE_DEFAULT

使用备份区域冗余创建数据库Create database using zone-redundancy for backups

下面的示例为数据库备份设置区域冗余。The following example sets zone-redundancy for database backups. 时间点还原备份和长期保留备份(如果已配置)将使用相同的备份存储冗余。Both point-in-time restore backups and long-term retention backups (if configured) will use the same backup storage redundancy.

CREATE DATABASE test_zone_redundancy 
  WITH BACKUP_STORAGE_REDUNDANCY = 'ZONE';

另请参阅See also

*SQL 数据库
托管实例 *
* SQL Database
Managed Instance *

 

Azure SQL 托管实例Azure SQL Managed Instance

概述Overview

在 Azure SQL 托管实例中,此语句用于创建数据库。In Azure SQL Managed Instance, this statement is used to create a database. 在托管实例上创建数据库时,可以指定数据库名称和排序规则。When creating a database on a managed instance, you specify the database name and collation.

语法Syntax

CREATE DATABASE database_name [ COLLATE collation_name ]
[;]

重要

要在托管实例中为数据库添加文件或设置包含,请使用 ALTER DATABASE 语句。To add files or set containment for a database in a managed instance, use the ALTER DATABASE statement.

参数Arguments

database_name 新数据库的名称。database_name The name of the new database. 此名称在 SQL 服务器上必须唯一,并且必须符合标识符的 SQL ServerSQL Server 规则。This name must be unique on the SQL server and comply with the SQL ServerSQL Server rules for identifiers. 有关详细信息,请参阅标识符For more information, see Identifiers.

Collation_name 指定数据库的默认排序规则。Collation_name Specifies the default collation for the database. 排序规则名称既可以是 Windows 排序规则名称,也可以是 SQL 排序规则名称。Collation name can be either a Windows collation name or a SQL collation name. 如果未指定,则会为数据库分配默认排序规则(即 SQL_Latin1_General_CP1_CI_AS)。If not specified, the database is assigned the default collation, which is SQL_Latin1_General_CP1_CI_AS.

有关 Windows 和 SQL 排序规则名称的详细信息,请参阅 COLLATE (Transact-SQL)For more information about the Windows and SQL collation names, COLLATE (Transact-SQL).

备注Remarks

Azure SQL 数据库Azure SQL Database 中的数据库具有多个在创建数据库时设置的默认设置。Databases in Azure SQL 数据库Azure SQL Database have several default settings that are set when the database is created. 有关这些默认设置的详细信息,请参阅 DATABASEPROPERTYEX 中的值列表。For more information about these default settings, see the list of values in DATABASEPROPERTYEX.

重要

CREATE DATABASE 语句必须是 Transact-SQLTransact-SQL 批处理中的唯一语句。The CREATE DATABASE statement must be the only statement in a Transact-SQLTransact-SQL batch.

以下是 CREATE DATABASE 限制:The following are CREATE DATABASE limitations:

  • 无法定义文件和文件组。Files and filegroups cannot be defined.

  • 不支持 WITH 选项。WITHoptions are not supported.

    提示

    解决方法是使用 ALTER DATABASEAs workaround, use ALTER DATABASE. CREATE DATABASE 之后设置数据库选项并添加文件。after CREATE DATABASE to set database options and to add files.

权限Permissions

要创建数据库,登录名必须为下列各项之一:To create a database, a login must be one of the following:

  • 服务器级别主体登录名The server-level principal login
  • 本地 Azure SQL Server 的 Azure AD 管理员The Azure AD administrator for the local Azure SQL Server
  • 登录名为 dbcreator 数据库角色的成员A login that is a member of the dbcreator database role

示例Examples

简单示例Simple Example

创建数据库的简单示例。A simple example for creating a database.

CREATE DATABASE TestDB1;

另请参阅See also

请参阅 ALTER DATABASESee ALTER DATABASE

* Azure Synapse
Analytics *
* Azure Synapse
Analytics *

 

Azure Synapse AnalyticsAzure Synapse Analytics

概述Overview

在 Azure Synapse 中,此语句可与 Azure SQL 数据库服务器一起使用,以创建 SQL Analytics 数据库。In Azure Synapse, this statement can be used with an Azure SQL Database server to create a SQL Analytics database. 使用此语句,可以指定数据库名称、排序规则、最大大小、版本和服务目标。With this statement, you specify the database name, collation, maximum size, edition, and service objective.

语法Syntax

CREATE DATABASE database_name [ COLLATE collation_name ]
(
    [ MAXSIZE = {
          250 | 500 | 750 | 1024 | 5120 | 10240 | 20480 | 30720
        | 40960 | 51200 | 61440 | 71680 | 81920 | 92160 | 102400
        | 153600 | 204800 | 245760
      } GB ,
    ]
    EDITION = 'datawarehouse',
    SERVICE_OBJECTIVE = {
         'DW100' | 'DW200' | 'DW300' | 'DW400' | 'DW500' | 'DW600'
        | 'DW1000' | 'DW1200' | 'DW1500' | 'DW2000' | 'DW3000' | 'DW6000'
        |'DW100c' | 'DW200c' | 'DW300c' | 'DW400c' | 'DW500c'
        | 'DW1000c' | 'DW1500c' | 'DW2000c' | 'DW2500c' | 'DW3000c' | 'DW5000c'
        | 'DW6000c' | 'DW7500c' | 'DW10000c' | 'DW15000c' | 'DW30000c'
    }
)
[;]

参数Arguments

database_name 新数据库的名称。database_name The name of the new database. 此名称在 SQL Server 上必须是唯一的,它可托管 Azure SQL 数据库Azure SQL Database 数据库和 Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse) 数据库,且符合标识符的 SQL ServerSQL Server 规则。This name must be unique on the SQL server, which can host both Azure SQL 数据库Azure SQL Database databases and Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse) databases, and comply with the SQL ServerSQL Server rules for identifiers. 有关详细信息,请参阅标识符For more information, see Identifiers.

collation_name 指定数据库的默认排序规则。collation_name Specifies the default collation for the database. 排序规则名称既可以是 Windows 排序规则名称,也可以是 SQL 排序规则名称。Collation name can be either a Windows collation name or a SQL collation name. 如果未指定,则会为数据库分配默认排序规则(即 SQL_Latin1_General_CP1_CI_AS)。If not specified, the database is assigned the default collation, which is SQL_Latin1_General_CP1_CI_AS.

有关 Windows 和 SQL 排序规则名称的详细信息,请参阅 COLLATE (Transact-SQL)For more information about the Windows and SQL collation names, see COLLATE (Transact-SQL).

EDITION 指定数据库的服务层。EDITION Specifies the service tier of the database. 对于 Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse),使用“datawarehouse”。For Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse) use 'datawarehouse'.

MAXSIZE 默认为 245,760 GB (240 TB)。MAXSIZE The default is 245,760 GB (240 TB).

适用于: 已针对计算代系 1 进行优化Applies to: Optimized for Compute Gen1

允许的最大数据库大小。The maximum allowable size for the database. 数据库大小不能超出 MAXSIZE。The database cannot grow beyond MAXSIZE.

适用于: 已针对计算代系 2 进行优化Applies to: Optimized for Compute Gen2

数据库中允许的最大行存储数据大小。The maximum allowable size for rowstore data in the database. 存储在行存储表中的数据、列存储索引的增量存储或非聚集索引(聚集在列存储索引上)都不可超过 MAXSIZE。压缩到列存储格式的数据没有大小限制,不受 MAXSIZE 约束。Data stored in rowstore tables, a columnstore index's deltastore, or a nonclustered index on a clustered columnstore index cannot grow beyond MAXSIZE.Data compressed into columnstore format does not have a size limit and is not constrained by MAXSIZE.

SERVICE_OBJECTIVE 指定计算大小(服务目标)。SERVICE_OBJECTIVE Specifies the compute size (service objective). 有关 Azure Synapse 服务目标的详细信息,请参阅数据仓库单位 (DWU)For more information about service objectives for Azure Synapse, see Data Warehouse Units (DWUs).

一般备注General Remarks

使用 DATABASEPROPERTYEX 查看数据库属性。Use DATABASEPROPERTYEX to see the database properties.

使用 ALTER DATABASE - Azure Synapse Analytics 在以后更改最大大小或服务目标值。Use ALTER DATABASE - Azure Synapse Analytics to change the max size, or service objective values later.

Azure Synapse 设置为 COMPATIBILITY_LEVEL 130,且不得更改。Azure Synapse is set to COMPATIBILITY_LEVEL 130 and cannot be changed. 有关详细信息,请参阅在 Azure SQL 数据库中通过兼容性级别 130 优化查询性能For more details, see Improved Query Performance with Compatibility Level 130 in Azure SQL Database.

权限Permissions

所需的权限:Required permissions:

  • 服务器级别主体登录名(由预配进程创建),或者Server level principal login, created by the provisioning process, or
  • dbmanager 数据库角色的成员。Member of the dbmanager database role.

错误处理Error Handling

如果数据库的大小达到 MAXSIZE,你将收到错误代码 40544。If the size of the database reaches MAXSIZE you will receive error code 40544. 如果发生这种情况,你不能插入或更新数据或创建新的对象(如表、存储过程、视图和函数)。When this occurs, you cannot insert and update data, or create new objects (such as tables, stored procedures, views, and functions). 不过,仍可以读取和删除数据、截断表、删除表和索引以及重新生成索引。You can still read and delete data, truncate tables, drop tables and indexes, and rebuild indexes. 然后,您可以将 MAXSIZE 更新为比当前数据库大小更大的值,或者删除一些数据以释放存储空间。You can then update MAXSIZE to a value larger than your current database size or delete some data to free storage space. 在您可以插入新数据之前,可能有长达十五分钟的延迟。There may be as much as a fifteen-minute delay before you can insert new data.

限制和局限Limitations and Restrictions

您必须连接到 master 数据库才能创建新的数据库。You must be connected to the master database to create a new database.

CREATE DATABASE 语句必须是 Transact-SQLTransact-SQL 批处理中的唯一语句。The CREATE DATABASE statement must be the only statement in a Transact-SQLTransact-SQL batch.

创建数据库后,无法更改数据库排序规则。You cannot change the database collation after the database is created.

示例:Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse)Examples: Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse)

A.A. 简单示例Simple example

创建数据仓库数据库的简单示例。A simple example for creating a data warehouse database. 这将创建最小最大大小为 10240 GB、默认排序规则为 SQL_Latin1_General_CP1_CI_AS 且 最小计算功率为 DW100 的数据库。This creates the database with the smallest max size which is 10240 GB, the default collation which is SQL_Latin1_General_CP1_CI_AS, and the smallest compute power which is DW100.

CREATE DATABASE TestDW
(EDITION = 'datawarehouse', SERVICE_OBJECTIVE='DW100');

B.B. 创建具有所有选项的数据仓库数据库Create a data warehouse database with all the options

创建使用所有选项的 10 TB 数据仓库的示例。An example of creating a 10 terabyte data warehouse using all the options.

CREATE DATABASE TestDW COLLATE Latin1_General_100_CI_AS_KS_WS
(MAXSIZE = 10240 GB, EDITION = 'datawarehouse', SERVICE_OBJECTIVE = 'DW1000');

另请参阅See Also

* Analytics Platform
System (PDW) *
* Analytics Platform
System (PDW) *

 

分析平台系统Analytics Platform System

概述Overview

在 Analytics Platform System 中,此语句用于在 Analytics Platform System 设备上创建新数据库。In Analytics Platform System, this statement is used to create a new database on a Analytics Platform System appliance. 使用该语句创建与设备数据库相关联的所有文件,并为数据库表和事务日志设置最大大小和自动增长选项。Use this statement to create all files associated with an appliance database and to set maximum size and auto-growth options for the database tables and transaction log.

语法Syntax

CREATE DATABASE database_name
WITH (
    [ AUTOGROW = ON | OFF , ]
    REPLICATED_SIZE = replicated_size [ GB ] ,
    DISTRIBUTED_SIZE = distributed_size [ GB ] ,
    LOG_SIZE = log_size [ GB ] )
[;]

参数Arguments

database_name 新数据库的名称。database_name The name of the new database. 有关允许的数据库名称的详细信息,请参阅 并行数据仓库产品文档Parallel Data Warehouse product documentation 中的“对象命名规则”和“保留的数据库名称”。For more information on permitted database names, see "Object Naming Rules" and "Reserved Database Names" in the 并行数据仓库产品文档Parallel Data Warehouse product documentation.

AUTOGROW = ON | OFF 指定此数据库的 replicated_size、distributed_size 和 log_size 参数是否将根据需要自动增加到超出其指定大小。AUTOGROW = ON | OFF Specifies whether the replicated_size, distributed_size, and log_size parameters for this database will automatically grow as needed beyond their specified sizes. 默认值为 OFF。Default value is OFF.

如果 AUTOGROW 为 ON,则 replicated_size、distributed_size 和 log_size 将根据所需(不是在初始指定大小的块中),随需要比已分配存储更多存储的每次数据插入、更新或其他操作增加 。If AUTOGROW is ON, replicated_size, distributed_size, and log_size will grow as required (not in blocks of the initial specified size) with each data insert, update, or other action that requires more storage than has already been allocated.

如果 AUTOGROW 为 OFF,则大小不会自动增加。If AUTOGROW is OFF, the sizes will not grow automatically. 当尝试执行需要将 replicated_size、distributed_size 或 log_size 增加到超过其指定值的操作时,并行数据仓库Parallel Data Warehouse 将返回错误 。并行数据仓库Parallel Data Warehouse will return an error when attempting an action that requires replicated_size, distributed_size, or log_size to grow beyond their specified value.

对于所有大小,AUTOGROW 要么都设置为 ON,要么都为 OFF。AUTOGROW is either ON for all sizes or OFF for all sizes. 例如,不可能对于 log_size 将 AUTOGROW 设置为 ON,对于 replicated_size,却不这样设置 。For example, it is not possible to set AUTOGROW ON for log_size, but not set it for replicated_size.

replicated_size [ GB ] 一个正数。replicated_size [ GB ] A positive number. 为分配到复制表和每个 Compute 节点上的相应数据的总空间设置大小(整数或十进制 GB)。Sets the size (in integer or decimal gigabytes) for the total space allocated to replicated tables and corresponding data on each Compute node. 有关 replicated_size 的最小和最大要求的信息,请参阅 并行数据仓库产品文档Parallel Data Warehouse product documentation 中的“最小值和最大值”。For minimum and maximum replicated_size requirements, see "Minimum and Maximum Values" in the 并行数据仓库产品文档Parallel Data Warehouse product documentation.

如果 AUTOGROW 为 ON,复制表允许增加到超出限制。If AUTOGROW is ON, replicated tables will be permitted to grow beyond this limit.

如果 AUTOGROW 为 OFF,那么当用户尝试创建新的复制表、将数据插入到现有复制表或以增加的大小可能会超过 replicated_size 的方式更新现有复制表时,将返回错误。If AUTOGROW is OFF, an error will be returned if a user attempts to create a new replicated table, insert data into an existing replicated table, or update an existing replicated table in a manner that would increase the size beyond replicated_size.

distributed_size [ GB ] 一个正数。distributed_size [ GB ] A positive number. 整个设备上分配给分布式表(以及相应数据)的总空间大小(整数或十进制 GB)。The size, in integer or decimal gigabytes, for the total space allocated to distributed tables (and corresponding data) across the appliance. 有关 distributed_size 的最小和最大要求的信息,请参阅 并行数据仓库产品文档Parallel Data Warehouse product documentation 中的“最小值和最大值”。For minimum and maximum distributed_size requirements, see "Minimum and Maximum Values" in the 并行数据仓库产品文档Parallel Data Warehouse product documentation.

如果 AUTOGROW 为 ON,分布式表将可以增加到超出限制。If AUTOGROW is ON, distributed tables will be permitted to grow beyond this limit.

如果 AUTOGROW 为 OFF,那么当用户尝试创建新的分布式表、将数据插入到现有分布式表或以增加的大小可能会超过 replicated_size 的方式更新现有分布式表时,将返回错误。If AUTOGROW is OFF, an error will be returned if a user attempts to create a new distributed table, insert data into an existing distributed table, or update an existing distributed table in a manner that would increase the size beyond distributed_size.

log_size [ GB ] 一个正数。log_size [ GB ] A positive number. 整个设备上的事务日志的大小(整数或十进制 GB)。The size (in integer or decimal gigabytes) for the transaction log across the appliance.

有关 log_size 的最小和最大要求的信息,请参阅 并行数据仓库产品文档Parallel Data Warehouse product documentation 中的“最小值和最大值”。For minimum and maximum log_size requirements, see "Minimum and Maximum Values" in the 并行数据仓库产品文档Parallel Data Warehouse product documentation.

如果 AUTOGROW 为 ON,日志文件将可以增加到超出限制。If AUTOGROW is ON, the log file is permitted to grow beyond this limit. 使用 DBCC SHRINKLOG (Azure Synapse Analytics) 语句将日志文件大小减少至初始大小。Use the DBCC SHRINKLOG (Azure Synapse Analytics) statement to reduce the size of the log files to their original size.

如果 AUTOGROW 为 OFF,那么对于在单个计算节点上增加的日志大小可能会超过 log_size 的任何操作,将向用户返回错误。If AUTOGROW is OFF, an error will be returned to the user for any action that would increase the log size on an individual Compute node beyond log_size.

权限Permissions

需要 master 数据库中的 CREATE ANY DATABASE 权限,或者 sysadmin 固定服务器角色的成员身份。Requires the CREATE ANY DATABASE permission in the master database, or membership in the sysadmin fixed server role.

以下示例向数据库用户 Fay 提供创建数据库的权限。The following example provides the permission to create a database to the database user Fay.

USE master;
GO
GRANT CREATE ANY DATABASE TO [Fay];
GO

一般备注General Remarks

数据库创建时的数据库兼容性级别为 120,这是 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 的兼容级别。Databases are created with database compatibility level 120, which is the compatibility level for SQL Server 2014 (12.x)SQL Server 2014 (12.x). 这可确保数据库将能够使用 PDW 所使用的所有 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 功能。This ensures the database will be able to use all of the SQL Server 2014 (12.x)SQL Server 2014 (12.x) functionality that PDW uses.

限制和局限Limitations and Restrictions

不允许在显式事务中使用 CREATE DATABASE 语句。The CREATE DATABASE statement is not allowed in an explicit transaction. 有关详细信息,请参阅语句For more information, see Statements.

有关数据库的最小和最大约束的信息,请参阅 并行数据仓库产品文档Parallel Data Warehouse product documentation 中的“最小值和最大值”。For information on minimum and maximum constraints on databases, see "Minimum and Maximum Values" in the 并行数据仓库产品文档Parallel Data Warehouse product documentation.

在创建数据库时,每个 Compute 节点上必须有足够的可用空间来分配以下大小的总和:At the time a database is created, there must be enough available free space on each Compute node to allocate the combined total of the following sizes:

  • SQL ServerSQL Server 数据库的表的大小为 replicated_table_size。database with tables the size of replicated_table_size.
  • SQL ServerSQL Server 数据库的表的大小为(distributed_table_size/Compute 节点数量)。database with tables the size of (distributed_table_size / number of Compute nodes ).
  • SQL ServerSQL Server 日志的大小为(log_size/Compute 节点数量)。logs the size of (log_size / number of Compute nodes).

锁定Locking

在 DATABASE 对象上采用共享锁。Takes a shared lock on the DATABASE object.

元数据Metadata

成功执行此操作后,sys.databasessys.objects 元数据视图中将会显示该数据库的条目。After this operation succeeds, an entry for this database will appear in the sys.databases and sys.objectsmetadata views.

示例:并行数据仓库Parallel Data WarehouseExamples: 并行数据仓库Parallel Data Warehouse

A.A. 基本数据库创建示例Basic database creation examples

以下示例创建数据库 mytest,对于复制表,每个 Compute 节点分配 100 GB 存储,对于分布式表,每个设备分配 500 GB 存储,对于事务日志,每个设备分配 100 GB 存储。The following example creates the database mytest with a storage allocation of 100 GB per Compute node for replicated tables, 500 GB per appliance for distributed tables, and 100 GB per appliance for the transaction log. 在此示例中,AUTOGROW 默认为关。In this example, AUTOGROW is off by default.

CREATE DATABASE mytest
  WITH
    (REPLICATED_SIZE = 100 GB,
    DISTRIBUTED_SIZE = 500 GB,
    LOG_SIZE = 100 GB );

以下示例使用和以上相同的参数创建数据库 mytest,但 AUTOGROW 为关闭。The following example creates the database mytest with the same parameters as above, except that AUTOGROW is turned on. 这允许数据库增加到超过参数的指定大小。This allows the database to grow outside the specified size parameters.

CREATE DATABASE mytest
  WITH
    (AUTOGROW = ON,
    REPLICATED_SIZE = 100 GB,
    DISTRIBUTED_SIZE = 500 GB,
    LOG_SIZE = 100 GB);

B.B. 创建部分 GB 级大小数据库Creating a database with partial gigabyte sizes

以下示例创建数据库 mytest,(AUTOGROW 为 关)对于复制表,每个计算节点分配 1.5 GB 存储,对于分布式表,每个设备分配 5.25 GB 存储,对于事务日志,每个设备分配 10 GB 存储。The following example creates the database mytest, with AUTOGROW off, a storage allocation of 1.5 GB per Compute node for replicated tables, 5.25 GB per appliance for distributed tables, and 10 GB per appliance for the transaction log.

CREATE DATABASE mytest
  WITH
    (REPLICATED_SIZE = 1.5 GB,
    DISTRIBUTED_SIZE = 5.25 GB,
    LOG_SIZE = 10 GB);

另请参阅See Also