Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
Analytics Platform System (PDW)
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.
Transact-SQL syntax conventions
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
The name of the new database. For more information on permitted database names, see Reserved Database Names.
AUTOGROW = { ON | OFF }
Specifies whether the replicated_size, distributed_size, and log_size parameters automatically grow as needed beyond their specified sizes. Default value is OFF.
If AUTOGROW is ON, replicated_size, distributed_size, and log_size grow as required with each data insert, update, or other action that requires more storage than has already been allocated.
If AUTOGROW is OFF, the sizes don't grow automatically.
AUTOGROW is either ON for all sizes or OFF for all sizes. For example, it's not possible to set AUTOGROW ON for log_size, but not set it for replicated_size.
replicated_size [ GB ]
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.
If AUTOGROW is ON, replicated tables can grow beyond this limit.
If AUTOGROW is OFF, an error is 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 ]
A positive number. The size, in integer or decimal gigabytes, for the total space allocated to distributed tables (and corresponding data) across the appliance.
If AUTOGROW is ON, distributed tables are permitted to grow beyond this limit.
If AUTOGROW is OFF, an error is 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 ]
A positive number. The size (in integer or decimal gigabytes) for the transaction log across the appliance.
If AUTOGROW is ON, the log file is permitted to grow beyond this limit. Use the DBCC SHRINKLOG - Analytics Platform System (PDW) statement to reduce the size of the log files to their original size.
If AUTOGROW is OFF, an error is returned to the user for any action that would increase the log size on an individual Compute node beyond log_size.
Permissions
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
Remarks
Databases are created with database compatibility level 120, which is the compatibility level for SQL Server 2014 (12.x). This ensures that the database can use all of the SQL Server 2014 (12.x) functionality that PDW uses.
Limitations
The CREATE DATABASE statement isn't allowed in an explicit transaction. For more information, see Transact-SQL statements.
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).
Locking
Takes a shared lock on the DATABASE object.
Metadata
After this operation succeeds, an entry for this database appears in the sys.databases and sys.objects metadata views.
Examples: Analytics Platform System (PDW)
A. Basic database creation examples
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, 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. Create a database with partial gigabyte sizes
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);