When reading Transact-SQL documentation, I usually skip the Backus–Naur Form (BNF) at the top of the documentation and go directly to the samples. So, to add on to Cihan Biyikoglu blog post about the new SQL Azure database sizes available June 28, 2010, I want to show some samples of new CREATE DATABASE syntax.

You can still create a database without any parameters; this will generate the smallest database of the web edition:

CREATE DATABASE Test

This is the same as declaring:

CREATE DATABASE Test (EDITION=’WEB’, MAXSIZE=1GB)

The database created can hold up to 1 Gigabyte of data and then will return a 40544 error when trying to add more data. See Cichan’s blog post for more details.

You can also create a web edition database with a larger maximum size of 5 Gigabytes like this:

CREATE DATABASE Test (EDITION=’WEB’, MAXSIZE=5GB)

Business edition databases will start with a maximum size of 10 Gigabytes:

CREATE DATABASE Test (EDITION=’BUSINESS’)

However, they can be increased to a 50 Gigabytes using the maximum size parameter:

CREATE DATABASE Test (EDITION=’BUSINESS’, MAXSIZE=50GB)

The valid MAXSIZE settings for WEB edition are 1 and 5 GB. The valid options for BUSINESS edition are 10, 20, 30, 40, and 50 GB.

Pricing

The good news with the new database sizes is that for some database sizes you are not billed for the maximum size until you use it. See the official pricing guide for more details. The table below tries to highlight this point.

Alter Database

Most of the time you will know the database size you need before you deploy to SQL Azure, however if you are in a growth scenario you can start out with a web edition database and change it to business edition as it grows. This will save you some money. To change the database edition you can use the ALTER DATABASE Syntax like this:

ALTER DATABASE Test MODIFY (EDITION=’BUSINESS’, MAXSIZE=50GB)

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.