Overview of the CREATE DATABASE statement in Azure SQL Server

SQLShack

SQL Server training Español

Overview of the CREATE DATABASE statement in Azure SQL Server

July 23, 2019 by Ranga Babu 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. 12 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. 12 CREATE DATABASE DemoDB

( 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. Log in to the Azure portal. Navigate to SQL databases and click on Add. 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”. 12 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. 12 CREATE DATABASE DemoDB

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. 123 CREATE DATABASE DemoDB

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. 123 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 BabuSQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies Latest posts by Ranga Babu (see all) Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019 Overview of the Collate SQL command - October 22, 2019 Recover a lost SA password - September 20, 2019

Related posts

The new elastic databases in Azure Azure SQL – Elastic Job Agent How to configure Azure SQL Database long-term retention (LTR) backups Azure SQL Database vs SQL Server on Azure Virtual Machines How to create a linked server to an Azure SQL database 12,397 Views

Follow us

Popular

SQL Convert Date functions and formats SQL Variables: Basics and usage SQL PARTITION BY Clause overview Different ways to SQL delete duplicate rows from a SQL Table How to UPDATE from a SELECT statement in SQL Server SQL Server functions for converting a String to a Date SELECT INTO TEMP TABLE statement in SQL Server SQL WHILE loop with simple examples How to backup and restore MySQL databases using the mysqldump command CASE statement in SQL Overview of SQL RANK functions Understanding the SQL MERGE statement INSERT INTO SELECT statement overview and examples SQL multiple joins for beginners with examples Understanding the SQL Decimal data type DELETE CASCADE and UPDATE CASCADE in SQL Server foreign key SQL Not Equal Operator introduction and examples SQL CROSS JOIN with examples The Table Variable in SQL Server SQL Server table hints – WITH (NOLOCK) best practices

Trending

SQL Server Transaction Log Backup, Truncate and Shrink Operations Six different methods to copy tables between databases in SQL Server How to implement error handling in SQL Server Working with the SQL Server command line (sqlcmd) Methods to avoid the SQL divide by zero error Query optimization techniques in SQL Server: tips and tricks How to create and configure a linked server in SQL Server Management Studio SQL replace: How to replace ASCII special characters in SQL Server How to identify slow running queries in SQL Server SQL varchar data type deep dive How to implement array-like functionality in SQL Server All about locking in SQL Server SQL Server stored procedures for beginners Database table partitioning in SQL Server How to drop temp tables in SQL Server How to determine free space and file size for SQL Server databases Using PowerShell to split a string into an array KILL SPID command in SQL Server How to install SQL Server Express edition SQL Union overview, usage and examples

Solutions

Read a SQL Server transaction logSQL Server database auditing techniquesHow to recover SQL Server data from accidental UPDATE and DELETE operationsHow to quickly search for SQL database data and objectsSynchronize SQL Server databases in different remote sourcesRecover SQL data from a dropped table without backupsHow to restore specific table(s) from a SQL Server database backupRecover deleted SQL data from transaction logsHow to recover SQL Server data from accidental updates without backupsAutomatically compare and synchronize SQL Server dataOpen LDF file and view LDF file contentQuickly convert SQL code to language-specific client codeHow to recover a single table from a SQL Server database backupRecover data lost due to a TRUNCATE operation without backupsHow to recover SQL Server data from accidental DELETE, TRUNCATE and DROP operationsReverting your SQL Server database back to a specific point in timeHow to create SSIS package documentationMigrate a SQL Server database to a newer version of SQL ServerHow to restore a SQL Server database backup to an older version of SQL Server

Categories and tips

►Auditing and compliance (50) Auditing (40) Data classification (1) Data masking (9) Azure (295) Azure Data Studio (46) Backup and restore (108) ►Business Intelligence (482) Analysis Services (SSAS) (47) Biml (10) Data Mining (14) Data Quality Services (4) Data Tools (SSDT) (13) Data Warehouse (16) Excel (20) General (39) Integration Services (SSIS) (125) Master Data Services (6) OLAP cube (15) PowerBI (95) Reporting Services (SSRS) (67) Data science (21) ►Database design (233) Clustering (16) Common Table Expressions (CTE) (11) Concurrency (1) Constraints (8) Data types (11) FILESTREAM (22) General database design (104) Partitioning (13) Relationships and dependencies (12) Temporal tables (12) Views (16) ►Database development (418) Comparison (4) Continuous delivery (CD) (5) Continuous integration (CI) (11) Development (146) Functions (106) Hyper-V (1) Search (10) Source Control (15) SQL unit testing (23) Stored procedures (34) String Concatenation (2) Synonyms (1) Team Explorer (2) Testing (35) Visual Studio (14) DBAtools (35) DevOps (23) DevSecOps (2) Documentation (22) ETL (76) ►Features (213) Adaptive query processing (11) Bulk insert (16) Database mail (10) DBCC (7) Experimentation Assistant (DEA) (3) High Availability (36) Query store (10) Replication (40) Transaction log (59) Transparent Data Encryption (TDE) (21) Importing, exporting (51) Installation, setup and configuration (121) Jobs (42) ►Languages and coding (686) Cursors (9) DDL (9) DML (6) JSON (17) PowerShell (77) Python (37) R (16) SQL commands (196) SQLCMD (7) String functions (21) T-SQL (275) XML (15) Lists (12) Machine learning (37) Maintenance (99) Migration (50) Miscellaneous (1) ►Performance tuning (869) Alerting (8) Always On Availability Groups (82) Buffer Pool Extension (BPE) (9) Columnstore index (9) Deadlocks (16) Execution plans (125) In-Memory OLTP (22) Indexes (79) Latches (5) Locking (10) Monitoring (100) Performance (196) Performance counters (28) Performance Testing (9) Query analysis (121) Reports (20) SSAS monitoring (3) SSIS monitoring (10) SSRS monitoring (4) Wait types (11) ►Professional development (68) Professional development (27) Project management (9) SQL interview questions (32) Recovery (33) Security (84) Server management (24) SQL Azure (271) SQL Server Management Studio (SSMS) (90) SQL Server on Linux (21) ►SQL Server versions (177) SQL Server 2012 (6) SQL Server 2016 (63) SQL Server 2017 (49) SQL Server 2019 (57) SQL Server 2022 (2) ►Technologies (334) AWS (45) AWS RDS (56) Azure Cosmos DB (28) Containers (12) Docker (9) Graph database (13) Kerberos (2) Kubernetes (1) Linux (44) LocalDB (2) MySQL (49) Oracle (10) PolyBase (10) PostgreSQL (36) SharePoint (4) Ubuntu (13) Uncategorized (4) Utilities (21) Helpers and best practices BI performance counters SQL code smells rules SQL Server wait types © 2022 Quest Software Inc. ALL RIGHTS RESERVED. GDPR Terms of Use Privacy