Azure SQL Data Warehouse uses familiar T-SQL language constructs to create databases. However, there are differences between the create database syntax for on premise SQL Servers and Azure SQL Data Warehouse. Let’s examine the differences that are important for a developer to know.
We will start with the CREATE DATABASE statement itself. This can be performed once you have connected to the SQL Server instance – that was described in this post --in Visual Studio and run the following command:
CREATE DATABASE EquityDB COLLATE SQL_Latin1_General_CP1_CI_AS
(
EDITION = 'DataWarehouse'
, SERVICE_OBJECTIVE = 'DW100'
, MAXSIZE = 1024 GB
)
;You will note that the command does not define the creation of data files and log files. This is not required when creating a database in SQL Data Warehouse, as the data will be spread across all of the 60 available distributions that we discussed in the previous post, and the log is created under the hood.
You will see that there are COLLATE and MAXSIZE options available that work in the same way as the options for an on premise SQL Server. At the time of writing, the maximum size of an Azure SQL Data Warehouse is 245760GB (24TB).
However, there is an EDITION clause and a SERVICE_OBJECTIVE clause. These are options that are not available with an on premise SQL Server, but are specific to Azure version of SQL Server services such as SQL Database and SQL Data Warehouse. When creating an Azure Data Warehouse, you must specify the edition as DataWarehouse, and the SERVICE_OBJECTIVE as the capacity of your data warehouse. This service_objective can go from DW100 to a maximum of DW6000. This determines the compute level that is being used by the data warehouse.
You can also create a database using a PowerShell script as shown in the following code:
# CREATE A SQL DATABASE $resourceGroupName = "cto_ads_prep_rg" $serverName = "ctomsftadssqlsrv" $databaseName = "ContosoRetailDW" $databaseEdition = "DataWarehouse" $RequestedServiceObjectiveName = "DW400" New-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -RequestedServiceObjectiveName $RequestedServiceObjectiveName -ServerName $serverName -DatabaseName $databaseName -Edition $databaseEdition
The following video shows you how to create a database in Azure SQL Data Warehouse.
It is important to note that within Azure SQL Data Warehouse, cross database queries are not permitted. In scenarios where you would typically do this, your design should change to incorporate the creation of database schemas to hold data within the schema that you would typically hold in separate databases. This is performed using the CREATE SCHEMA T-SQL statement that works in the same way as on premise SQL Server.
With the database created, you can then proceed to create tables. That’s the next installment of this series….
Previous Articles
In this series, we have also covered the following topics:
