How to create database objects in SQL Azure

In this blog post, we are going to show you how to create the following important database objects in SQL Azure cloud database:

  • Database
  • Table
  • Stored procedure

If you want more information about our TSQL scripts on SQL Azure, plz visit this blog post:

http://sqlazuretutorials.com/wordpress/transact-sql-scripts-sql-azure-video-tutorials/

 

Create Database in SQL Azure

You can create a database in SQL Azure with the CREATE DATABASE command.
Here is the syntax:

CREATE DATABASE database_name
{
   (<edition_options> [, ...n])
}
<edition_options> ::=
{
   (MAXSIZE = {1 | 5 | 10 | 20 | 30 | 40 | 50} GB)
    |(EDITION = {‘web’ | ‘business’})
}
[;]

One item to note is the missing key clause ON PRIMARY. In addition notice you do not need to define the Data and Log file info as you typically do in an On-Premise SQL Server. These are taken care of by Microsoft Cloud services.

USE [master]
GO

CREATE DATABASE [HR]
GO

 

Create Database Table in SQL Azure

You can create a table in SQL Azure with the CREATE TABLE command.
Here is the syntax:

Create table [table_name]
(
    [field 1] [data type]
    [field 2] [data type]
    [field 3] [data type]
)

Please note the missing key clause ON PRIMARY, we have included the error we received below. Another fact is that you cannot specify the “USE” clause in SQL Azure. You have to open a session with a specific database and then execute the SQL code.

–USE HR does not work

CREATE TABLE dbo.Customer(
    CustomerId int IDENTITY(1,1) NOT NULL,
    CompanyName varchar(100) NULL,
    CustomerLastName varchar(100) NULL,
    CustomerFirstName varchar(100) NULL,
    CustomerDOB date NULL,
    CustomerLastName varchar(100) NULL,
    City varchar(100) NULL,
    Country varchar(100) NULL,
    ModifyDate date NULL,
 CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED
(
    CustomerId ASC
)
)
GO

 

Create a Stored procedure in SQL Azure database

Stored procedure are a vital component of a database as they provide the following:

  1. Performance gain
  2. Reusability
  3. Security
  4. Manageability

We create a simple stored procedure here that we pass in two parameters, city and country. It then runs a select statement on customers table.

CREATE PROCEDURE [dbo].[PROC_CUSTOMER]

–PASSING IN TWO PARAMETERS

@City VARCHAR(50),
@COUNTRY VARCHAR(50)

AS

–THIS SUPPRESSES MESSAGES ABOUT NUMBER OR ROWS RETURNED
SET NOCOUNT ON

SELECT
CustomerID
,CompanyName
,ContactName
,ContactTitle
,Address
,City
,Region
,PostalCode
,Country
FROM CUSTOMERS
WHERE
CITY=@City
AND
COUNTRY=@COUNTRY

/* Execute the following to test the Stored Procedure

DECLARE
@CITY VARCHAR(50),
@COUNTRY VARCHAR(50)
SET @CITY=’LONDON’
SET @COUNTRY=’UK’
EXEC PROC_CUSTOMER @CITY, @COUNTRY

–OR

EXEC PROC_CUSTOMER ‘LONDON’, ‘UK’

*/

This entry was posted in Development, SQL Programming, TSQL and tagged , , . Bookmark the permalink.

Comments are closed.