CREATE DATABASE (Azure SQL Data Warehouse)

THIS TOPIC APPLIES TO: noSQL Server (starting with 2008)noAzure SQL DatabaseyesAzure SQL Data Warehouse noParallel Data Warehouse

Creates a new database.

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' 
        | 'DW1000c' | 'DW1500c' | 'DW2000c' | 'DW2500c' | 'DW3000c' | 'DW5000c' 
        | 'DW6000c' | 'DW7500c' | 'DW10000c' | 'DW15000c' | 'DW30000c'
    }  
)  
[;]  

Arguments

database_name
The name of the new database. This name must be unique on the SQL server, which can host both Azure SQL Database databases and SQL Data Warehouse databases, and comply with the SQL Server rules for identifiers. For more information, see Identifiers.

collation_name
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).

EDITION
Specifies the service tier of the database. For SQL Data Warehouse use 'datawarehouse' .

MAXSIZE
The default is 10,240 GB (10 TB).

Applies to: Optimized for Elasticity performance tier

The maximum allowable size for the database. The database cannot grow beyond MAXSIZE.

Applies to: Optimized for Compute performance tier

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.

SERVICE_OBJECTIVE
Specifies the performance level. For more information about service objectives for SQL Data Warehouse, see Performance Tiers.

General Remarks

Use DATABASEPROPERTYEX (Transact-SQL) to see the database properties.

Use ALTER DATABASE (Azure SQL Data Warehouse) to change the max size, or service objective values later.

SQL Data Warehouse is set to COMPATIBILITY_LEVEL 130 and cannot be changed. For more details, see Improved Query Performance with Compatibility Level 130 in Azure SQL Database.

Permissions

Required permissions:

  • Server level principal login, created by the provisioning process, or

  • Member of the dbmanager database role.

Error Handling

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 may be as much as a fifteen-minute delay before you can insert new data.

Limitations and Restrictions

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.

Examples: Azure SQL Data Warehouse

A. Simple example

A simple example for creating a data warehouse database. 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. Create a data warehouse database with all the options

An example of creating a 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

ALTER DATABASE (Azure SQL Data Warehouse( CREATE TABLE (Azure SQL Data Warehouse) DROP DATABASE (Transact-SQL(