CREATE DATABASE
Creates a new database.
Select one of the following tabs for the syntax, arguments, remarks, permissions, and examples for a particular SQL version with which you are working.
In the following row, select the product name you're interested in, and only that product's information is displayed.
* 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.
Create a database.
For more information about the syntax conventions, see Transact-SQL syntax conventions.
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>' )
| LEDGER = {ON | OFF }
}
<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
[;]
This is the name of the new database. Database names must be unique within an instance of SQL Server and comply with the rules for identifiers.
database_name can be a maximum of 128 characters, unless a logical name is not specified for the log file. If a logical log file name is not specified, SQL Server generates the logical_file_name and the os_file_name for the log by appending a suffix to database_name. This limits database_name to 123 characters so that the generated logical file name is no more than 128 characters.
If data file name is not specified, SQL Server uses database_name as both the logical_file_name and as the os_file_name. The default path is obtained from the registry. The default path can be changed in the Server Properties (Database Settings Page) in Management Studio. Changing the default path requires restarting SQL Server.
Applies to: SQL Server 2012 (11.x) and later
Specifies the containment status of the database. NONE = non-contained database. PARTIAL = partially contained database.
Specifies that the disk files used to store the data sections of the database, data files, are explicitly defined. ON is required when followed by a comma-separated list of <filespec> items that define the data files for the primary 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.
Specifies that the associated <filespec> list defines the primary file. The first file specified in the <filespec> entry in the primary filegroup becomes the primary file. A database can have only one primary file. For more information, see Database Files and Filegroups.
If PRIMARY is not specified, the first file listed in the CREATE DATABASE statement becomes the primary file.
Specifies that the disk files used to store the database log, log files, are explicitly defined. LOG ON is followed by a comma-separated list of <filespec> items that define the log files. 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. For information about this location, see View or Change the Default Locations for Data and Log Files in SSMS.
LOG ON cannot be specified on a database snapshot.
Specifies the default collation for the database. Collation name can be either a Windows collation name or a SQL collation name. If not specified, the database is assigned the default collation of the instance of SQL Server. A collation name cannot be specified on a database snapshot.
A collation name cannot be specified with the FOR ATTACH or FOR ATTACH_REBUILD_LOG clauses. For information about how to change the collation of an attached database, visit this Microsoft Web site.
For more information about the Windows and SQL collation names, see COLLATE.
Note
Contained databases are collated differently than non-contained databases. For more information, see Contained Database Collations.
NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }
Applies to: SQL Server 2012 (11.x) and later.
Specifies the level of non-transactional FILESTREAM access to the database.
Value | Description |
---|---|
OFF |
Non-transactional access is disabled. |
READONLY |
FILESTREAM data in this database can be read by non-transactional processes. |
FULL |
Full non-transactional access to FILESTREAM FileTables is enabled. |
DIRECTORY_NAME = <directory_name>
Applies to: SQL Server 2012 (11.x) and later
A windows-compatible directory name. This name should be unique among all the Database_Directory names in the SQL Server instance. Uniqueness comparison is case-insensitive, regardless of SQL Server collation settings. This option should be set before you create a FileTable in this database.
The following options are allowable only when CONTAINMENT has been set to PARTIAL. If CONTAINMENT is set to NONE, errors will occur.
Applies to: SQL Server 2012 (11.x) and later
See Configure the default full-text language Server Configuration Option for a full description of this option.
Applies to: SQL Server 2012 (11.x) and later
See Configure the default language Server Configuration Option for a full description of this option.
Applies to: SQL Server 2012 (11.x) and later
See Configure the nested triggers Server Configuration Option for a full description of this option.
Applies to: SQL Server 2012 (11.x) and later
See transform noise words Server Configuration Option for a full description of this option.
Four digits representing a year. 2049 is the default value. See Configure the two digit year cutoff Server Configuration Option for a full description of this option.
When ON is specified, the database can be the source or target of a cross-database ownership chain.
When OFF, the database cannot participate in cross-database ownership chaining. The default is OFF.
Important
The instance of SQL Server will recognize this setting when the cross db ownership chaining server option is 0 (OFF). 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. This option is set by using sp_configure.
To set this option, requires membership in the sysadmin fixed server role. The DB_CHAINING option cannot be set on these system databases: master
, model
, tempdb
.
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.
When OFF, database modules in an impersonation context cannot access resources outside the database. The default is OFF.
TRUSTWORTHY is set to OFF whenever the database is attached.
By default, all system databases except the msdb
database have TRUSTWORTHY set to OFF. The value cannot be changed for the model
and tempdb
databases. We recommend that you never set the TRUSTWORTHY option to ON for the master
database.
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. Applies to: SQL Server 2017 (14.x) and newer.
When set to ON
, it creates a ledger database, in which the integrity of all user data is protected. Only ledger tables can be created in a ledger database. The default is OFF
. The value of the LEDGER
option cannot be changed once the database is created. For more information, see Configure a ledger database.
Specifies that the database is created by attaching an existing set of operating system files. There must be a <filespec> entry that specifies the primary file. 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. A <filespec> entry must be specified for these files.
FOR ATTACH requires the following:
- All data files (MDF and NDF) must be available.
- If multiple log files exist, they must all be available.
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. 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.
Note
A database created by a more recent version of SQL Server cannot be attached in earlier versions.
In SQL 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.
Attaching a database that contains a FILESTREAM option of "Directory name", into a SQL Server instance will prompt SQL Server to verify that the Database_Directory name is unique. If it is not, the ATTACH
operation fails with the error, FILESTREAM Database_Directory name is not unique in this SQL Server instance
. To avoid this error, the optional parameter, directory_name, should be passed in to this operation.
FOR ATTACH cannot be specified on a database snapshot.
FOR ATTACH can specify the RESTRICTED_USER option. 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.
If the database uses Service Broker, use the WITH <service_broker_option> in your FOR ATTACH clause:
Controls Service Broker message delivery and the Service Broker identifier for the database. Service Broker options can only be specified when the FOR ATTACH clause is used.
Specifies that Service Broker is enabled for the specified database. That is, message delivery is started, and is_broker_enabled
is set to true in the sys.databases
catalog view. The database retains the existing Service Broker identifier.
Creates a new service_broker_guid
value in both sys.databases
and the restored database. Ends all conversation endpoints with cleanup. The broker is enabled, but no message is sent to the remote conversation endpoints. Any route that references the old Service Broker identifier must be re-created with the new identifier.
Ends all conversations with an error stating that the database is attached or restored. The broker is disabled until this operation is completed and then enabled. The database retains the existing Service Broker identifier.
When you attach a replicated database that was copied instead of being detached, consider:
- If you attach the database to the same server instance and version as the original database, no additional steps are required.
- 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.
- 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.
Note
Attach works with the vardecimal storage format, but the SQL Server Database Engine must be upgraded to at least SQL Server 2005 (9.x) SP2. You cannot attach a database using vardecimal storage format to an earlier version of SQL Server. For more information about the vardecimal storage format, see Data Compression.
When a database is first attached or restored to a new instance of SQL Server, a copy of the database master key (encrypted by the service master key) is not yet stored in the server. You must use the OPEN MASTER KEY
statement to decrypt the database master key (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). When a database has been upgraded from an earlier version, the DMK should be regenerated to use the newer AES algorithm. For more information about regenerating the DMK, see ALTER MASTER KEY. The time required to regenerate the DMK key to upgrade to AES depends upon the number of objects protected by the DMK. Regenerating the DMK key to upgrade to AES is only necessary once, and has no effect 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.
Important
We recommend that you do not attach databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. 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.
Note
The TRUSTWORTHY and DB_CHAINING options have no effect when attaching a database.
Specifies that the database is created by attaching an existing set of operating system files. This option is limited to read/write databases. There must be a <filespec> entry specifying the primary file. If one or more transaction log files are missing, the log file is rebuilt. The ATTACH_REBUILD_LOG automatically creates a new, 1-MB log file. This file is placed in the default log-file location. For information about this location, see View or Change the Default Locations for Data and Log Files in SSMS.
Note
If the log files are available, the Database Engine uses those files instead of rebuilding the log files.
FOR ATTACH_REBUILD_LOG requires the following conditions:
- A clean shutdown of the database.
- All data files (MDF and NDF) must be available.
Important
This operation breaks the log backup chain. We recommend that a full database backup be performed after the operation is completed. For more information, see BACKUP.
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 cannot be specified on a database snapshot.
For more information about attaching and detaching databases, see Database Detach and Attach.
Controls the file properties.
Specifies the logical name for the file. NAME is required when FILENAME is specified, except when specifying one of the FOR ATTACH clauses. A FILESTREAM filegroup cannot be named PRIMARY.
logical_file_name
Is the logical name used in SQL Server when referencing the file. Logical_file_name must be unique in the database and comply with the rules for identifiers. The name can be a character or Unicode constant, or a regular or delimited identifier.
Specifies the operating system (physical) file name.
' os_file_name '
Is the path and file name used by the operating system when you create the file. The file must reside on one of the following devices: the local server on which SQL Server is installed, a Storage Area Network [SAN], or an iSCSI-based network. The specified path must exist before executing the CREATE DATABASE statement. For more information, see Database Files and Filegroups later in this article.
SIZE, MAXSIZE, and FILEGROWTH parameters can be set when a UNC path is specified for the file.
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.
Note
Raw partitions are not supported in SQL Server 2014 and later versions.
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 '
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. 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.
The filegroup and file (<filespec>
) must be created in the same statement.
The SIZE and FILEGROWTH properties do not apply to a FILESTREAM filegroup.
Specifies the size of the file.
SIZE cannot be specified when the os_file_name is specified as a UNC path. SIZE does not apply to a FILESTREAM filegroup.
size
Is the initial size of the file.
When size is not supplied for the primary file, the Database Engine uses the size of the primary file in the model
database. The default size of the model
database is 8 MB (beginning with SQL Server 2016 (13.x)) or 1 MB (for earlier versions). When a secondary data file or log file is specified, but size is not specified for the file, the Database Engine makes the file 8 MB (beginning with SQL Server 2016 (13.x)) or 1 MB (for earlier versions). The size specified for the primary file must be at least as large as the primary file of the model
database.
The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. The default is MB. Specify a whole number. Do not include a decimal. Size is an integer value. For values greater than 2147483647, use larger units.
Specifies the maximum size to which the file can grow. MAXSIZE cannot be specified when the os_file_name is specified as a UNC path.
max_size
Is the maximum file size. The KB, MB, GB, and TB suffixes can be used. The default is MB. Specify a whole number. Do not include a decimal. If max_size is not specified, the file grows until the disk is full. Max_size is an integer value. For values greater than 2147483647, use larger units.
UNLIMITED
Specifies that the file grows until the disk is full. In SQL 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.
Note
There is no maximum size when this option is specified for a FILESTREAM container. It continues to grow until the disk is full.
Specifies the automatic growth increment of the file. The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting. FILEGROWTH cannot be specified when the os_file_name is specified as a UNC path. FILEGROWTH does not apply to a FILESTREAM filegroup.
growth_increment
Is the amount of space added to the file every time that new space is required.
The value can be specified in MB, KB, GB, TB, or percent (%). 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. The size specified is rounded to the nearest 64 KB, and the minimum value is 64 KB.
A value of 0 indicates that automatic growth is off and no additional space is allowed.
If FILEGROWTH is not specified, the default values are:
Version | Default values |
---|---|
Beginning SQL Server 2016 (13.x) | Data 64 MB. Log files 64 MB. |
Beginning SQL Server 2005 (9.x) | Data 1 MB. Log files 10%. |
Before SQL Server 2005 (9.x) | Data 10%. Log files 10%. |
Controls the filegroup properties. Filegroup cannot be specified on a database snapshot.
Is the logical name of the filegroup.
filegroup_name
filegroup_name must be unique in the database and cannot be the system-provided names PRIMARY and PRIMARY_LOG. 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
Specifies that the filegroup stores FILESTREAM binary large objects (BLOBs) in the file system.
DEFAULT
Specifies the named filegroup is the default filegroup in the database.
CONTAINS MEMORY_OPTIMIZED_DATA
Applies to: SQL Server 2014 (12.x) and later
Specifies that the filegroup stores memory_optimized data in the file system. For more information, see In-Memory Optimization Overview and Usage Scenarios. 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.
Is the name of the new database snapshot. Database snapshot names must be unique within an instance of SQL Server and comply with the rules for identifiers. database_snapshot_name can be a maximum of 128 characters.
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 filegroups are not supported by database snapshots. If a FILESTREAM data file is included in a CREATE DATABASE ON clause, the statement will fail and an error will be raised.
For descriptions of NAME and FILENAME and their values, see the descriptions of the equivalent <filespec> values.
Note
When you create a database snapshot, the other <filespec> options and the keyword PRIMARY are disallowed.
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.
Prior to SQL Server 2019, the source database for a database snapshot could not contain a MEMORY_OPTIMIZED_DATA filegroup. Support for in-memory database snapshots was added in SQL Server 2019.
For more information, see Database Snapshots.
The master database should be backed up whenever a user database is created, modified, or dropped.
The CREATE DATABASE
statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.
You can use one CREATE DATABASE
statement to create a database and the files that store the database. SQL Server implements the CREATE DATABASE statement by using the following steps:
- The SQL Server uses a copy of the model database to initialize the database and its metadata.
- A service broker GUID is assigned to the database.
- The Database Engine then fills the rest of the database with empty pages, except for pages that have internal data that records how the space is used in the database.
A maximum of 32,767 databases can be specified on an instance of SQL Server.
Each database has an owner that can perform special activities in the database. The owner is the user that creates the database. 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 CHECKDB
- FileStream
- Online backups using VSS and file snapshots
- Database snapshot creation
- Memory Optimized Data filegroup
Every database has at least two files, a primary file and a transaction log file, and at least one filegroup. 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.
We recommend that you use a Storage Area Network (SAN), iSCSI-based network, or locally attached disk for the storage of your SQL Server database files, because this configuration optimizes SQL Server performance and reliability.
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.
Note
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. For more information, see DROP DATABASE.
Each snapshot persists until it is deleted by using DROP DATABASE
.
For more information, see Database Snapshots and Create a database snapshot (Transact-SQL).
Several database options are automatically set whenever you create a database. For a list of these options, see ALTER DATABASE SET Options.
All user-defined objects in the model database are copied to all newly created databases. 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.
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.
Unless FOR ATTACH
is specified, each new database inherits the database option settings from the model
database. For example, the database option auto shrink is set to true in model
and in any new databases you create. If you change the options in the model
database, these new option settings are used in any new databases you create. Changing operations in the model
database does not affect existing databases. If FOR ATTACH is specified on the CREATE DATABASE statement, the new database inherits the database option settings of the original database.
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.
Requires CREATE DATABASE
, CREATE ANY DATABASE
, or ALTER ANY DATABASE
permission.
To maintain control over disk use on an instance of SQL Server, permission to create databases is typically limited to a few logins.
The following example provides the permission to create a database to the database user Fay
.
USE master;
GO
GRANT CREATE DATABASE TO [Fay];
GO
In SQL 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.
Note
Microsoft SQL Server 2005 Express edition does not set data and log file permissions.
The following example creates the database mytest
and creates a corresponding primary and transaction log file. Because the statement has no <filespec> items, the primary database file is the size of the model
database primary file. The transaction log is set to the larger of these values: 512 KB or 25% the size of the primary data file. Because MAXSIZE is not specified, the files can grow to fill all available disk space. 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
The following example creates the database Sales
. Because the keyword PRIMARY is not used, the first file (Sales_dat
) becomes the primary file. Because neither MB nor KB is specified in the SIZE parameter for the Sales_dat
file, it uses MB and is allocated in megabytes. 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
The following example creates the database Archive
that has three 100-MB
data files and two 100-MB
transaction log files. The primary file is the first file in the list and is explicitly specified with the PRIMARY
keyword. The transaction log files are specified following the LOG ON
keywords. 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. 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
The following example creates the database Sales
that has the following filegroups:
- The primary filegroup with the files
Spri1_dat
andSpri2_dat
. The FILEGROWTH increments for these files are specified as15%
. - A filegroup named
SalesGroup1
with the filesSGrp1Fi1
andSGrp1Fi2
. - A filegroup named
SalesGroup2
with the filesSGrp2Fi1
andSGrp2Fi2
.
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
The following example detaches the database Archive
created in example D, and then attaches it by using the FOR ATTACH
clause. Archive
was defined to have multiple data and log files. 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. Beginning with 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
The 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.
The source database for this example is the Sales
database created in example D.
USE master;
GO
CREATE DATABASE sales_snapshot0600 ON
( NAME = SPri1_dat, FILENAME = 'D:\SalesData\SPri1dat_0600.ss'),
( NAME = SPri2_dat, FILENAME = 'D:\SalesData\SPri2dt_0600.ss'),
( NAME = SGrp1Fi1_dat, FILENAME = 'D:\SalesData\SG1Fi1dt_0600.ss'),
( NAME = SGrp1Fi2_dat, FILENAME = 'D:\SalesData\SG1Fi2dt_0600.ss'),
( NAME = SGrp2Fi1_dat, FILENAME = 'D:\SalesData\SG2Fi1dt_0600.ss'),
( NAME = SGrp2Fi2_dat, FILENAME = 'D:\SalesData\SG2Fi2dt_0600.ss')
AS SNAPSHOT OF Sales ;
GO
The following example creates the database MyOptionsTest
. A 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
The following example attaches the full-text catalog AdvWksFtCat
along with the AdventureWorks2022
data and log files. In 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 AdventureWorks2022 database
sp_detach_db AdventureWorks2022;
GO
-- Physically move the full text catalog to the new location.
--Attach the AdventureWorks2022 database and specify the new location of the full-text catalog.
CREATE DATABASE AdventureWorks2022 ON
(FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2022_data.mdf'),
(FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2022_log.ldf'),
(FILENAME = 'c:\myFTCatalogs\AdvWksFtCat')
FOR ATTACH;
GO
The following example creates the FileStreamDB
database. The database is created with one row filegroup and two FILESTREAM filegroups. Each filegroup contains one file:
FileStreamDB_data
contains row data. It contains one file,FileStreamDB_data.mdf
with the default path.FileStreamPhotos
contains FILESTREAM data. It contains two FILESTREAM data containers,FSPhotos
, located atC:\MyFSfolder\Photos
andFSPhotos2
, located atD:\MyFSfolder\Photos
. It is marked as the default FILESTREAM filegroup.FileStreamResumes
contains FILESTREAM data. It contains one FILESTREAM data container,FSResumes
, located atC:\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
The following example creates the BlobStore1
database. The database is created with one row filegroup and one FILESTREAM filegroup, FS
. The FILESTREAM filegroup contains two files, FS1
and FS2
. 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
* SQL Database *
In Azure SQL Database, this statement can be used with an Azure SQL server to create a single database or a database in an elastic pool. With this statement, you specify the database name, collation, maximum size, edition, service objective, and, if applicable, the elastic pool for the new database. It can also be used to create the database in an elastic pool. Additionally, it can be used to create a copy of the database on another SQL Database server.
For more information about the syntax conventions, see Transact-SQL syntax conventions.
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' | 'GEOZONE' }
| LEDGER = {ON | OFF }
}
<edition_options> ::=
{
MAXSIZE = { 100 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'
| 'BC_DC_n'
| 'BC_Gen5_n'
| 'BC_M_n'
| 'GP_DC_n'
| 'GP_Fsv2_n'
| 'GP_Gen5_n'
| 'GP_S_Gen5_n'
| 'HS_DC_n'
| 'HS_Gen5_n'
| 'HS_MOPRMS_n'
| 'HS_PRMS_n'
| { ELASTIC_POOL(name = <elastic_pool_name>) } })
}
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_Gen5_n'
| 'GP_Fsv2_n'
| 'GP_S_Gen5_n'
| 'BC_Gen5_n'
| 'BC_M_n'
| 'HS_Gen5_n'
| 'HS_PRMS_n'
| 'HS_MOPRMS_n'
| { ELASTIC_POOL(name = <elastic_pool_name>) } })
]
[ WITH ( BACKUP_STORAGE_REDUNDANCY = { 'LOCAL' | 'ZONE' | 'GEO' } ) ]
[;]
The name of the new database. This name must be unique on the SQL Server and comply with the SQL Server rules for identifiers. For more information, see Identifiers.
Specifies the default collation for the database data. Specify CATALOG_COLLATION
for system metadata, such as object identifiers.
Collation name can be either a Windows collation name or a SQL collation name. If not specified, the database is assigned the default collation, which is SQL_Latin1_General_CP1_CI_AS.
For more information about the Windows and SQL collation names, COLLATE (Transact-SQL).
Specifies the default collation for the metadata catalog. The CATALOG_COLLATION
argument is only available during database creation and cannot be changed after creation.
By default, the metadata catalog for system object names is collated to SQL_Latin1_General_CP1_CI_AS collation. This is the default setting on Azure SQL Database if CATALOG_COLLATION is unspecified.
DATABASE_DEFAULT specifies that the metadata catalog used for system views and system tables be collated to match the collation for the database. If you desire that object identifiers in system metadata follow the same collation as data, you should create the database WITH CATALOG_COLLATION = DATABASE_DEFAULT
.
You might desire different collations for data and object identifiers. The following example creates the database with a case-sensitive collation for row data, but will use the default SQL_Latin1_General_CP1_CI_AS case-insensitive collation for object identifiers.
CREATE DATABASE [different-collations] COLLATE SQL_Latin1_General_CP1_CS_AS
If you desire that both data and system metadata use the same collation, specify
WITH CATALOG_COLLATION = DATABASE_DEFAULT
. The following example creates the database with a case-sensitive collation, which will be used for object identifiers.CREATE DATABASE [same-collations] COLLATE SQL_Latin1_General_CP1_CS_AS WITH CATALOG_COLLATION = DATABASE_DEFAULT
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. Unless explicitly specified, databases created with T-SQL use geo-redundant backup storage.
To enforce data residency when you're creating a database by using T-SQL, use LOCAL
or ZONE
as input to the BACKUP_STORAGE_REDUNDANCY parameter.
When creating a database as a copy of another database with AS COPY OF
, specifying options is supported and should be wrapped in parentheses. For example, WITH (BACKUP_STORAGE_REDUNDANCY = 'LOCAL');
.
When set to ON
, it creates a ledger database, in which the integrity of all user data is protected. Only ledger tables can be created in a ledger database. The default is OFF
. The value of the LEDGER
option cannot be changed once the database is created. For more information, see Configure a ledger database.
Specifies the maximum size of the database. MAXSIZE must be valid for the specified EDITION (service tier).
Following are the supported MAXSIZE values and defaults (D) for the service tiers.
Note
The MAXSIZE argument does not apply to single databases in the Hyperscale service tier. Single Hyperscale tier databases grow as needed, up to 128 TB. The SQL Database service adds storage automatically - you do not need to set a maximum size.
DTU model for single and pooled databases on a SQL Database server
MAXSIZE | Basic | S0-S2 | S3-S12 | P1-P6 | P11-P15 |
---|---|---|---|---|---|
100 MB | √ | √ | √ | √ | √ |
500 MB | √ | √ | √ | √ | √ |
1 GB | √ | √ | √ | √ | √ |
2 GB | √ (D) | √ | √ | √ | √ |
5 GB | N/A | √ | √ | √ | √ |
10 GB | N/A | √ | √ | √ | √ |
20 GB | N/A | √ | √ | √ | √ |
30 GB | N/A | √ | √ | √ | √ |
40 GB | N/A | √ | √ | √ | √ |
50 GB | N/A | √ | √ | √ | √ |
100 GB | N/A | √ | √ | √ | √ |
150 GB | N/A | √ | √ | √ | √ |
200 GB | N/A | √ | √ | √ | √ |
250 GB | N/A | √ (D) | √ (D) | √ | √ |
300 GB | N/A | N/A | √ | √ | √ |
400 GB | N/A | N/A | √ | √ | √ |
500 GB | N/A | N/A | √ | √ (D) | √ |
750 GB | N/A | N/A | √ | √ | √ |
1,024 GB | N/A | N/A | √ | √ | √ (D) |
From 1,024 GB up to 4,096 GB in increments of 256 GB* | N/A | N/A | N/A | N/A | √ |
* P11 and P15 allow MAXSIZE up to 4 TB with 1,024 GB being the default size. P11 and P15 can use up to 4 TB of included storage at no additional charge. 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. For more information regarding resource limitations for the DTU model, see DTU resource limits.
The MAXSIZE value for the DTU model, if specified, has to be a valid value shown in the previous table for the service tier specified.
For limits such as maximum data size and tempdb
size in the vCore purchasing model, refer to the articles for resource limits for single databases or resource limits for elastic pools.
If no MAXSIZE
value is set when using the vCore model, the default is 32 GB. For more information on resource limitations for vCore model, see vCore resource limits.
Specifies the service tier of the database.
Single and pooled databases. The available values are: 'Basic', 'Standard', 'Premium', 'GeneralPurpose', 'BusinessCritical', and 'Hyperscale'.
The following rules apply to MAXSIZE and EDITION arguments:
- If EDITION is specified but MAXSIZE is not specified, the default value for the edition is used. For example, if the EDITION is set to Standard, and the MAXSIZE is not specified, then the MAXSIZE is automatically set to 250 MB.
- If neither MAXSIZE nor EDITION is specified, the EDITION is set to
GeneralPurpose
, and MAXSIZE is set to 32 GB.
Specifies the compute size and service objective.
- For DTU purchasing model:
S0
,S1
,S2
,S3
,S4
,S6
,S7
,S9
,S12
,P1
,P2
,P4
,P6
,P11
,P15
- For the latest vCore purchasing model, choose the tier and provide the number of vCores from a preset list of values, where the number of vCores is
n
. Refer to the resource limits for single databases or resource limits for elastic pools.- For example:
GP_Gen5_8
for General Purpose Standard-series (Gen5) compute, 8 vCores.GP_S_Gen5_8
for General Purpose Serverless Standard-series (Gen5) compute, 8 vCores.HS_Gen5_8
for Hyperscale - provisioned compute - standard-series (Gen5), 8 vCores.
For service objective descriptions and more information about the size, editions, and the service objectives combinations, see Azure SQL Database Service Tiers. If the specified SERVICE_OBJECTIVE is not supported by the EDITION, you receive an error. To change the SERVICE_OBJECTIVE value from one tier to another (for example from S1 to P1), you must also change the EDITION value. Support for PRS service objectives has been removed.
Applies to: Single and pooled databases only. Does not apply to databases in the Hyperscale service tier. 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. For more information, see Create and manage a SQL Database elastic pool.
Applies to: Single and pooled databases only.
Use AS COPY OF
to copy a database to the same or a different SQL Database server.
When creating a database as a copy of another database with AS COPY OF
, specifying options is supported and should be wrapped in parentheses. For example, WITH (BACKUP_STORAGE_REDUNDANCY = 'LOCAL');
.
source_server_name The name of the SQL Database server where the source database is located. This parameter is optional when the source database and the destination database are to be located on the same SQL Database server.
Note
The AS COPY OF
argument does not support the fully qualified unique domain names. In other words, if your server's fully qualified domain name is serverName.database.windows.net
, use only serverName
during database copy.
source_database_name
The name of the database that is to be copied.
Databases in Azure SQL Database have several default settings that are set when the database is created. For more information about these default settings, see the list of values in DATABASEPROPERTYEX.
MAXSIZE
provides the ability to limit the size of the database. 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. You can then update MAXSIZE
to a value larger than your current database size or delete some data to free storage space. There might be as much as a fifteen-minute delay before you can insert new data.
To change the size, edition, or service objective values later, use ALTER DATABASE (Azure SQL Database).
Applies to: Single and pooled databases only.
Copying a database using the CREATE DATABASE
statement is an asynchronous operation. Therefore, a connection to the SQL Database server is not needed for the full duration of the copy process. 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. In other words, the CREATE DATABASE
statement returns successfully when the database copy is still in progress.
- Monitoring the copy process on an SQL Database server: Query the
percentage_complete
orreplication_state_desc
columns in the dm_database_copies or thestate
column in the sys.databases view. 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.
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 might 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.
- The source and destination database names must be specified, unique, and comply with the SQL Server rules for identifiers. For more information, see Identifiers.
- The
CREATE DATABASE
statement must be executed within the context of themaster
database of the SQL Database server where the new database will be created. - After the copying completes, the destination database must be managed as an independent database. You can execute the
ALTER DATABASE
andDROP 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 might continue to be accessed while the database copy is in progress.
For more information, see Create a copy of an Azure SQL database using Transact-SQL.
Important
By default, the database copy is created with the same backup storage redundancy as that of the source database.
To create a database, the login must be one of the following principals:
- The server-level principal login
- The Microsoft Entra administrator for the logical server in Azure
- A login that is a member of the
dbmanager
database role
Additional 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. If the login is based on SQL Server authentication, the login executing the statement on the local server must have a matching login on the source SQL Database server, with an identical name and password.
A simple example for creating a database.
CREATE DATABASE TestDB1;
A simple example for creating a general purpose database.
CREATE DATABASE TestDB2
( EDITION = 'GeneralPurpose' );
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_Gen5_8' ) ;
An example creating a copy of a database.
Applies to: Single and pooled databases only.
CREATE DATABASE escuela
AS COPY OF school;
Creates new database in pool named S3M100:
Applies to: Single and pooled databases only.
CREATE DATABASE db1 ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = S3M100 ) ) ;
The following example creates a copy of the db_original
database named db_copy
in the General Purpose service objective for a single database. 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 ( EDITION = 'GeneralPurpose', SERVICE_OBJECTIVE = 'GP_Gen5_8' );
The following example creates a copy of the db_original
database, named db_copy
in an elastic pool named ep1
. This is true regardless of whether db_original
is in an elastic pool or a compute size (service objective) for a single database. 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 ) ) ;
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;
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';
CREATE DATABASE MyLedgerDB ( EDITION = 'GeneralPurpose' ) WITH LEDGER = ON;
The following example sets different options for the new copy of a database, including a different service tier and backup storage redundancy settings. By default, the database copy is created with the same settings as the source database.
CREATE DATABASE copy_testdb
AS COPY OF [test_db]
(EDITION = 'GeneralPurpose', SERVICE_OBJECTIVE = 'GP_Gen5_8')
WITH (BACKUP_STORAGE_REDUNDANCY = 'LOCAL');
* SQL Managed Instance *
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.
For more information about the syntax conventions, see Transact-SQL syntax conventions.
CREATE DATABASE database_name [ COLLATE collation_name ]
[ WITH <with_options> [,..n]]
[;]
<with_options> ::=
{
LEDGER = { ON | OFF }
}
Important
To add files or set containment for a database in a managed instance, use the ALTER DATABASE statement.
The name of the new database. This name must be unique on the SQL server and comply with the SQL Server rules for identifiers. For more information, see Identifiers.
Specifies the default collation for the database. Collation name can be either a Windows collation name or a SQL collation name. If not specified, the database is assigned the default collation, which is SQL_Latin1_General_CP1_CI_AS.
For more information about the Windows and SQL collation names, COLLATE (Transact-SQL).
When set to ON
, it creates a ledger database, in which the integrity of all user data is protected. Only ledger tables can be created in a ledger database. The default is OFF
. The value of the LEDGER
option cannot be changed once the database is created. For more information, see Configure a ledger database.
Databases in Azure SQL Database have several default settings that are set when the database is created. For more information about these default settings, see the list of values in DATABASEPROPERTYEX.
Important
The CREATE DATABASE
statement must be the only statement in a Transact-SQL batch.
The following are CREATE DATABASE
limitations:
Files and filegroups cannot be defined.
WITH
options are not supported, except forWITH LEDGER
.Tip
As workaround, use ALTER DATABASE. after
CREATE DATABASE
to set database options and to add files.
To create a database, a login must be one of the following:
- The server-level principal login
- The Microsoft Entra administrator for the for the logical server in Azure
- A login that is a member of the
dbcreator
database role
A simple example for creating a database.
CREATE DATABASE TestDB1;
CREATE DATABASE MyLedgerDB WITH LEDGER = ON;
* Azure Synapse
Analytics *
In Azure Synapse, this statement can be used with an Azure SQL Database server to create a dedicated SQL pool. With this statement, you specify the database name, collation, maximum size, edition, and service objective.
- CREATE DATABASE is supported for standalone dedicated SQL pools (formerly SQL DW) using Gen2 service levels.
- CREATE DATABASE is not supported for dedicated SQL pools in an Azure Synapse Analytics workspace. Instead, use the Azure portal.
- CREATE DATABASE is supported for serverless SQL pools in Azure Synapse Analytics.
For more information about the syntax conventions, see Transact-SQL syntax conventions.
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 = {
'DW100c' | 'DW200c' | 'DW300c' | 'DW400c' | 'DW500c'
| 'DW1000c' | 'DW1500c' | 'DW2000c' | 'DW2500c' | 'DW3000c' | 'DW5000c'
| 'DW6000c' | 'DW7500c' | 'DW10000c' | 'DW15000c' | 'DW30000c'
}
)
[;]
The name of the new database. This name must be unique on the SQL server, which can host both databases in Azure SQL Database and Azure Synapse Analytics databases, and comply with the SQL Server rules for identifiers. For more information, see Identifiers.
Specifies the default collation for the database. Collation name can be either a Windows collation name or a SQL collation name. If not specified, the database is assigned the default collation, which is SQL_Latin1_General_CP1_CI_AS.
For more information about the Windows and SQL collation names, see COLLATE (Transact-SQL).
The default is 245,760 GB (240 TB).
Applies to: Optimized for Compute Gen1
The maximum allowable size for the database. The database cannot grow beyond MAXSIZE.
Applies to: Optimized for Compute Gen2
The maximum allowable size for rowstore data in the database. 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.
Specifies the service tier of the database. For Azure Synapse Analytics use datawarehouse
.
Specifies the compute size (service objective). The service levels for Gen2 are measured in compute data warehouse units (cDWU), for example DW2000c
. Gen1 service levels are measured in DWUs, for example DW2000
. For more information about service objectives for Azure Synapse, see Data Warehouse Units (DWUs). Gen1 service objectives (no longer listed) are no longer supported, you can receive an error: Azure SQL Data Warehouse Gen1 has been deprecated in this region. Please use SQL Analytics in Azure Synapse.
Use DATABASEPROPERTYEX to see the database properties.
Use ALTER DATABASE - Azure Synapse Analytics to change the max size, or service objective values later.
Azure Synapse is set to COMPATIBILITY_LEVEL 130 and cannot be changed. For more information, see Improved Query Performance with Compatibility Level 130 in Azure SQL Database.
Required permissions:
- Server level principal login, created by the provisioning process, or
- Member of the
dbmanager
database role.
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. You can then update MAXSIZE to a value larger than your current database size or delete some data to free storage space. There might be as much as a fifteen-minute delay before you can insert new data.
You must be connected to the master
database to create a new database.
The CREATE DATABASE
statement must be the only statement in a Transact-SQL batch.
You cannot change the database collation after the database is created.
A simple example for creating a standalone dedicated SQL pool (formerly SQL DW). This creates the database with the smallest max size (10,240 GB), the default collation (SQL_Latin1_General_CP1_CI_AS), and the smallest Gen2 service objective (DW100c).
CREATE DATABASE TestDW
(EDITION = 'datawarehouse', SERVICE_OBJECTIVE='DW100c');
An example of creating a 10-terabyte standalone dedicated SQL pool (formerly SQL DW).
CREATE DATABASE TestDW COLLATE Latin1_General_100_CI_AS_KS_WS
(MAXSIZE = 10240 GB, EDITION = 'datawarehouse', SERVICE_OBJECTIVE = 'DW1000c');
This creates the database in the serverless pool, specifying a collation (Latin1_General_100_CI_AS_KS_WS).
CREATE DATABASE TestDW COLLATE Latin1_General_100_CI_AS_KS_WS
* Analytics Platform
System (PDW) *
In Analytics Platform System, this statement is used to create a new database on an 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.
For more information about the syntax conventions, see Transact-SQL syntax conventions.
CREATE DATABASE database_name
WITH (
[ AUTOGROW = ON | OFF , ]
REPLICATED_SIZE = replicated_size [ GB ] ,
DISTRIBUTED_SIZE = distributed_size [ GB ] ,
LOG_SIZE = log_size [ GB ] )
[;]
The name of the new database. For more information on permitted database names, see "Object Naming Rules" and "Reserved Database Names" in the Analytics Platform System (PDW) product documentation.
Specifies whether the replicated_size, distributed_size, and log_size parameters for this database will automatically grow as needed beyond their specified sizes. Default value is OFF.
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.
If AUTOGROW is OFF, the sizes will not grow automatically. Analytics Platform System (PDW) will return an error when attempting an action that requires replicated_size, distributed_size, or log_size to grow beyond their specified value.
AUTOGROW is either ON for all sizes or OFF for all sizes. For example, it is not possible to set AUTOGROW ON for log_size, but not set it for replicated_size.
A positive number. Sets the size (in integer or decimal gigabytes) for the total space allocated to replicated tables and corresponding data on each Compute node. For minimum and maximum replicated_size requirements, see "Minimum and Maximum Values" in the Analytics Platform System (PDW) product documentation.
If AUTOGROW is ON, replicated tables will be permitted to grow beyond this limit.
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.
A positive number. The size, in integer or decimal gigabytes, for the total space allocated to distributed tables (and corresponding data) across the appliance. For minimum and maximum distributed_size requirements, see "Minimum and Maximum Values" in the Analytics Platform System (PDW) product documentation.
If AUTOGROW is ON, distributed tables will be permitted to grow beyond this limit.
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.
A positive number. The size (in integer or decimal gigabytes) for the transaction log across the appliance.
For minimum and maximum log_size requirements, see "Minimum and Maximum Values" in the Analytics Platform System (PDW) product documentation.
If AUTOGROW is ON, the log file is permitted to grow beyond this limit. Use the DBCC SHRINKLOG (Azure Synapse Analytics) statement to reduce the size of the log files to their original 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.
Requires the CREATE ANY DATABASE
permission in the master
database, or membership in the sysadmin fixed server role.
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
Databases are created with database compatibility level 120, which is the compatibility level for SQL Server 2014 (12.x). This ensures that the database will be able to use all of the SQL Server 2014 (12.x) functionality that PDW uses.
The CREATE DATABASE statement is not allowed in an explicit transaction. For more information, see Statements.
For information on minimum and maximum constraints on databases, see "Minimum and Maximum Values" in the Analytics Platform System (PDW) 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 Server database with tables the size of replicated_table_size.
- SQL Server database with tables the size of (distributed_table_size / number of Compute nodes).
- SQL Server logs the size of (log_size / number of Compute nodes).
Takes a shared lock on the DATABASE object.
After this operation succeeds, an entry for this database will appear in the sys.databases and sys.objects metadata views.
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. In this example, AUTOGROW is off by default.
CREATE DATABASE mytest
WITH
(REPLICATED_SIZE = 100 GB,
DISTRIBUTED_SIZE = 500 GB,
LOG_SIZE = 100 GB );
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);
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);