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_nameos_file_nameIf 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_nameos_file_nameIf 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. 如需相關資訊,請參閱 Database Files and FilegroupsFor 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 FileTables 的完整非交易式存取。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

    如需此選項的完整描述,請參閱設定 default full-text language 伺服器設定選項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

    如需此選項的完整描述,請參閱設定 default language 伺服器設定選項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

    如需此選項的完整描述,請參閱設定 nested triggers 伺服器設定選項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

    如需此選項的完整描述,請參閱 transform noise words 伺服器設定選項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. 如需此選項的完整說明,請參閱設定兩位數年份的截止伺服器組態選項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 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 "Directory name" 選項的資料庫附加至 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.

FOR 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. 也就是說,會啟動訊息傳遞,且 is_broker_enabled 在 sys.databases 目錄檢視中會設定為 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 Database EngineSQL 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 Database EngineSQL 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 陳述式來提供服務主要金鑰 (SMK) 所加密的 DMK 複本給伺服器,以在未來啟用自動解密。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 EngineDatabase Engine 就會使用這些檔案,而不會重建記錄檔。If the log files are available, the Database EngineDatabase 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. 除非指定其中一個 FOR ATTACH 子句,否則指定 FILENAME 時,NAME 是必要的。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 路徑,則在您執行 ALTER DATABASE 之前,C:\MyFiles 必須存在,但是 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 EngineDatabase Engine會使用模型資料庫中主要檔案的大小。When size is not supplied for the primary file, the Database EngineDatabase Engine uses the size of the primary file in the model database. 模型的預設大小是 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 EngineDatabase 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 EngineDatabase 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. 如果 FILESTREAM 資料檔案包含在 CREATE DATABASE ON 子句中,此陳述式將會失敗,並引發錯誤。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 會使用模型資料庫的複本將資料庫及其中繼資料初始化。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 EngineDatabase Engine 就會在其餘的資料庫中填入空白頁面,但不包括含有記錄資料庫中空間使用方式之內部資料的頁面。The Database EngineDatabase 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

模型資料庫中的所有使用者定義物件都會複製到所有新建立的資料庫中。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. 例如,在模型資料庫及您建立的任何新資料庫中,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> 項目,所以主要資料庫檔案的大小就是模型資料庫主要檔案的大小。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 為單位配置,因為 MB 參數中明確陳述 SIZE 後置詞。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

下列範例會建立含有下列檔案群組的資料庫 SalesThe 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 中建立的資料庫 SalesThe 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

下列範例會建立 FileStreamDB 資料庫。The 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.mdf (具有預設路徑)。It contains one file, FileStreamDB_data.mdf with the default path.
  • FileStreamPhotos 包含 FILESTREAM 資料。FileStreamPhotos contains FILESTREAM data. 其包含兩個 FILESTREAM 資料容器:一個是位於 FSPhotosC:\MyFSfolder\Photos,一個是位於 FSPhotos2D:\MyFSfolder\PhotosIt 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. 其包含一個 FILESTREAM 資料容器 FSResumes (位於 C:\MyFSfolder\Resumes)。It 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

下列範例會建立 BlobStore1 資料庫。The following example creates the BlobStore1 database. 此資料庫是使用一個資料列檔案群組和一個 FILESTREAM 檔案群組 FS 所建立。The 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 Database ** SQL Database *

 

SQL DatabaseSQL Database

概觀Overview

Azure SQL DatabaseAzure SQL Database 中,此陳述式可與 Azure SQL Server 搭配使用,以建立單一資料庫或彈性集區中的資料庫。In Azure SQL DatabaseAzure 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 Database 伺服器上建立資料庫複本。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 Database 上的預設設定。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 Database 的 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 Database 服務會自動新增儲存體;您不需要設定大小上限。The SQL Database service adds storage automatically - you do not need to set a maximum size.

SQL Database 伺服器上適用於單一和集區資料庫的 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/AN/A
10 GB10 GB N/AN/A
20 GB20 GB N/AN/A
30 GB30 GB N/AN/A
40 GB40 GB N/AN/A
50 GB50 GB N/AN/A
100 GB100 GB N/AN/A
150 GB150 GB N/AN/A
200 GB200 GB N/AN/A
250 GB250 GB N/AN/A √ (D)√ (D) √ (D)√ (D)
300 GB300 GB N/AN/A N/AN/A
400 GB400 GB N/AN/A N/AN/A
500 GB500 GB N/AN/A N/AN/A √ (D)√ (D)
750 GB750 GB N/AN/A N/AN/A
1024 GB1024 GB N/AN/A N/AN/A √ (D)√ (D)
從 1024 GB 至最大 4096 GB (以每 256 GB 的大小遞增)*From 1024 GB up to 4096 GB in increments of 256 GB* N/AN/A N/AN/A N/AN/A N/AN/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 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
虛擬核心數上限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
虛擬核心數上限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
虛擬核心數上限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 值,預設值為 32 GB。If no MAXSIZE value is set when using the vCore model, the default is 32 GB. 如需有關虛擬核心模型資源限制的其他詳細資訊,請參閱虛擬核心資源限制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_1HS_GEN4_2HS_GEN4_4HS_GEN4_8HS_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 Database 服務層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 Database 服務層和效能層級 (部分機器翻譯)、DTU 資源限制 (部分機器翻譯) 和虛擬核心資源限制 (部分機器翻譯)。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. 如需詳細資訊,請參閱建立和管理 SQL Database 彈性資料庫集區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 DatabaseSQL Database 伺服器上。For copying a database to the same or a different SQL DatabaseSQL Database server.

source_server_name 來源資料庫所在的 SQL DatabaseSQL Database 伺服器名稱。source_server_name The name of the SQL DatabaseSQL Database server where the source database is located. 當來源資料庫和目的地資料庫位於相同的 SQL DatabaseSQL Database 伺服器上時,這個參數是選擇性的。This parameter is optional when the source database and the destination database are to be located on the same SQL DatabaseSQL 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 DatabaseAzure SQL Database 中的資料庫有數個預設設定,這些設定是在建立資料庫時所設定。Databases in Azure SQL DatabaseAzure 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 DatabaseTo 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 DatabaseSQL Database 伺服器。Therefore, a connection to the SQL DatabaseSQL 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 DatabaseSQL Database 伺服器上的複製程序:查詢 dm_database_copies 中的 percentage_completereplication_state_desc 資料行,或是 sys.databases 檢視中的 state 資料行。Monitoring the copy process on an SQL DatabaseSQL 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.
  • CREATE DATABASE 陳述式必須在將要建立新資料庫之 SQL DatabaseSQL Database 伺服器的 master 資料庫內容中執行。The CREATE DATABASE statement must be executed within the context of the master database of the SQL DatabaseSQL 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 DatabaseSQL 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 DatabaseSQL Database server, with an identical name and password.

範例Examples

簡單範例Simple Example

建立資料庫的簡單範例。A simple example for creating a database.

CREATE DATABASE TestDB1;

使用 Edition 的簡單範例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 資料庫的複本,在單一資料庫的 P2 計算大小 (服務目標) 中名為 db_copy。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' );

下列範例會建立 db_original 資料庫的複本,在名為 ep1 的彈性集區中名為 db_copy。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 在彈性集區中,但使用不同的名稱,則 db_copy 仍會建立在 ep1 中。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 Database
受控執行個體 *
* 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 DatabaseAzure SQL Database 中的資料庫有數個預設設定,這些設定是在建立資料庫時所設定。Databases in Azure SQL DatabaseAzure 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 Database 伺服器搭配使用,以建立 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 DatabaseAzure 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 DatabaseAzure 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 Database 中改善的查詢效能與相容性層級 130For 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_sizedistributed_sizelog_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. 預設值是 OFFDefault value is OFF.

如果 AUTOGROW 設為 ON,在每次插入資料、更新資料或進行其他動作而導致所需儲存空間大於已配置大小時,replicated_sizedistributed_sizelog_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. 平行處理資料倉儲Parallel Data Warehouse 在嘗試進行需要 replicated_sizedistributed_sizelog_size 成長至超出其指定值的動作時,將會傳回錯誤。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 將 AUTOGROW 設為 ON。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. 設定配置給「每個計算節點上」複寫資料表和相對應資料的總空間大小 (以整數或小數 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 時,如果使用者嘗試建立新的分散式資料表、在現有的分散式資料表中插入資料,或更新現有的分散式資料表而導致大小增加至超出 distributed_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.

建立資料庫時,「每個計算節點上」都必須有足夠的可用空間,以配置下列大小的總和: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_sizedatabase with tables the size of replicated_table_size.
  • SQL ServerSQL Server 資料庫的資料表大小為 (distributed_table_size / 計算節點數目)。database with tables the size of (distributed_table_size / number of Compute nodes ).
  • SQL ServerSQL Server 會記錄 (log_size / 計算節點數目) 的大小。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,其儲存區配置為每一計算節點具備 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

以下範例會建立 AUTOGROW 設為關閉的資料庫 mytest,其儲存區配置為每一計算節點具備 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