Ranga Babu
CREATE DATABASE using Azure portal

Overview of the CREATE DATABASE statement in Azure SQL Server

July 23, 2019 by

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.

For example:

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.

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.

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

Log in to the Azure portal. Navigate to SQL databases and click on Add.

CREATE DATABASE using Azure portal

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

CREATE DATABASE in Azure SQL Server - Configure Edition, Service Objective and the max size of an Azure SQL database.

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

CREATE DATABASE in Azure SQL Server - Edition, service objective, max size of a database

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”.

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.

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.

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.

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.

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.

Ranga Babu

Ranga Babu

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

About Ranga Babu

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

168 Views