Overview of the CREATE DATABASE statement in Azure SQL Server

In this article, we will review CREATE DATABASE statement in the Azure SQL database with various examples.

CREATE DATABASE command is used to create a database in on-premises SQL Server or a database in Azure SQL Server. The syntax of CREATE DATABASE is a bit different in Azure SQL Server compared to on-premises SQL Server or a SQL Server on Azure VM.

We will discuss a few examples of creating a database using CREATE DATABASE statement in Azure SQL Server.

Syntax

The following is the basic syntax of creating a database in Azure SQL Server.

1	CREATE DATABASE DBName

For example:

1	CREATE DATABASE DemoDB

If no other options are specified, the database is created on the Azure SQL Server where the CREATE DATABASE command was executed with the default configuration. i.e. the database is created with edition “General Purpose” with service objective “Gen5, 2 vCores”. The max size property is set to 32 GB.

Creating a database by specifying the edition

Following is the syntax of creating a database in the specific edition.

1 2	CREATE DATABASE DemoDB2 ( EDITION = 'basic') ;

The above statement creates a database in the Azure SQL Server with a basic edition. If you do not specify the max size option, the max size of the database is set to the default value in basic edition i.e. 2 GB

Below are the different types of editions in the Azure SQL database.

BASIC EDITION
STANDARD EDITION
PREMIUM EDITION
GENERAL PURPOSE EDITION
HYPER SCALE EDITION
BUSINESS CRITICAL EDITION

The following example creates a database in Azure SQL Server with service objective “S0” and Standard edition. As we specified the MAXSIZE option, the max size of the database is set to 500 MB. The allowed max size value of a database in the standard edition is 250 GB.

1 2	CREATE DATABASE DemoDB</p> ( EDITION = 'standard', SERVICE_OBJECTIVE = 'S0', MAXSIZE = 500 MB ) ;

Let us see how to create a database equivalent to the above T-SQL script from the Azure portal.

Enter the name of the database and select the Azure SQL Server. Click on Configure database as shown in the below image.

Select the edition like Standard and service objective as S0. Set the max size and click on Apply.

Click on Review + Create and then Create.

Default MAXSIZE of the database

Below are the default values of max size as per edition and service level objective.

For a database in the basic version, the default max size is 2 GB and for a database standard edition, the default max size is 250 GB
If you are using a premium version the default max size depends on the service objective of the database
If the service objective is between P1-P6, then the default max size is 500 GB and if the service objective is between P11-P15, the default max size IS 1024 GB
For other editions using the vCore model, the default max size is 32 GB

Creating a database in an elastic pool

To create a database in an elastic pool using the CREATE DATABASE statement, the elastic pool must be pre-existing.

So, the database can be in an existing elastic pool only.

Following is the syntax of creating a database in an existing elastic pool. This script creates a database with the name DemoDB in the elastic pool “DemoPool”.

1 2	CREATE DATABASE DemoDB ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = DemoPool ) ) ;

Creating a database from an existing database

By using ‘AS COPY OF’ clause in CREATE DATABASE statement, we can create a copy of the database existing in the same or different server. Please note that the edition of the new database cannot be changed with the AS COPY OF clause. We can only change the service objective of the new database.

Following is the example of creating a database from a copy of an existing database within the same Azure SQL Server.

1 2	CREATE DATABASE DemoDB</p> AS COPY OF SampleDB

If the service objective is not specified while creating a database, the new database which is copied from the existing database is created with the same service objective.

Below is the T-SQL script to create a new database from an existing database by changing the service objective.

CREATE DATABASE DemoDB</p>

AS COPY OF SampleDB

(SERVICE_OBJECTIVE = 'S1' ) ;

If you want to create a copy of a database from an existing database in another server, just specify the server name. In this case, rbc2 is the server name where existing database SampleDB exists. You do not need to specify the fully qualified server name.

CREATE DATABASE DemoDB

AS COPY OF rbc2.SampleDB

(SERVICE_OBJECTIVE = 'S1' );

Creating a database in Azure SQL Database Managed Instance

To create a database in Azure SQL Database Managed Instance, just use CREATE DATABASE statement with the database name and specify the collation of the database. We cannot specify files and filegroups while creating a database. Use ALTER DATABASE to add new files if any.

1	CREATE DATABASE DemoDB COLLATE SQL_Latin1_General_CP1_CI_AS

Conclusion

In this article, we explored CREATE DATABASE statement in Azure SQL Server with different examples and created a database using the Azure portal. In case you have any questions, please feel free to ask in the comment section below.

Author
Recent Posts

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies