Creating Objects in SQL Azure

Demo Script

Creating Objects in SQL Azure

Lab version: 1.0.3

Last updated: 8/5/2010

Contents

Overview 3

Key Messages 3

Key Technologies 3

Time Estimates 4

Setup and Configuration 4

Task 1 – Running the Dependency Checker 4

Task 2 – Ensuring that the HolTestDB and HoLTestUser are created and configured 5

Demo Flow 5

Opening Statement 6

Step-by-Step Walkthrough 7

Create a Simple Table 7

Create a More Complex Table and Index 10

Examine the Query Plan 11

Summary 14

Overview

This document provides setup documentation, step-by-step instructions, and a written script for showing a demo of SQL Azure. This document can also serve as a tutorial or walkthrough of the technology. In this demo, you will look at how to create objects in SQL Azure. You will create tables and indexes and insert data into the database. Finally, you will examine the query plans for queries against SQL Azure using the tools provided by SQL Server Management Studio. For additional demos of the Windows Azure platform, please visit http://www.azure.com.

Note: In order to run through this demo, you must have a SQL Azure developer account. For more information on how to purchase an account, visit the SQL Azure Portal at http://www.microsoft.com/windowsazure/sqlazure/.

Key Messages

In this demo you will see three key things:

1.  Working with SQL Azure is very similar to working with SQL Server

2.  We can create tables, views, stored procedures and other objects using Data Definition Language in the same way that we do with SQL Server.

3.  We can use many advanced features such as the ability to request the query plan with SQL Azure.

Key Technologies

This demo uses the following technologies:

1.  SQL Azure

2.  SQL Server Management Studio

Time Estimates

·  Estimated time for setting up and configuring the demo: 1 min

·  Estimated time to complete the demo: 10 min

Setup and Configuration

This demo does not have any advanced configuration requirements. You simply need to have the prerequisites installed and have an account for SQL Azure. For more information on how to purchase an account, visit the SQL Azure Portal at http://www.microsoft.com/windowsazure/sqlazure/.

Task 1 – Running the Dependency Checker

The following steps describe how to run the Dependency Checker utility included with the demo to verify that you have the prerequisite components. You can skip this exercise if you are confident that the prerequisites are properly installed and configured.

1.  Open a Windows Explorer window and browse to the demo’s Source\Script folder.

2.  Double-click the Dependencies.dep file in this folder to launch the Dependency Checkertool and install any missing prerequisites.

3.  If the User Account Control dialog is shown, confirm the action to proceed.

Note:This process may require elevation.The .dep extension is associated with the Dependency Checker tool during its installation. For additional information about thesetupprocedure and how to install the Dependency Checker tool, refer to the Setup.docx document in the Assets folder of the training kit.

Task 2 – Ensuring that the HolTestDB and HoLTestUser are created and configured

You should ensure that you have followed the steps in the Demo scripts for 'Connecting to SQL Azure' and ' Managing Logins and Security in SQL Azure'. Specifically you need to have created the HoLTestDB database and HolTestUser login and user account and you must have granted the role db_owner to the HoLTestUser login.

Demo Flow

The step-by-step guide in this document follows the following demo flow/outline:

Figure 1

Diagram

Opening Statement

In this demo, we will look at how to create objects in SQL Azure including tables and indexes. We will see how to execute arbitrary T-SQL to bulk insert rows into a table. Finally, we will look at the query plan determined by the SQL Azure query optimizer.

Step-by-Step Walkthrough

This demo is composed of the following segments:

1.  Create a Simple Table

2.  Create a more Complex Table and Index

3.  Examine the SQL Azure Query Plan

Note: All of the queries for this demo are stored in the Assets/queries subfolder.

Create a Simple Table

In this first segment, we will create a simple table in SQL Azure.

Action / Script / Screenshot
1.  Open SQL Server Management Studio (SSMS) Start > All Programs > Microsoft SQL Server 2008 R2 November CTP > SQL Server Management Studio / ·  We are going to be using SQL Server 2008 R2 Management studio to work with SQL Azure today.
2.  Complete the dialog as follows
3.  Server name is the name of your server that is found I the SQL Azure portal. (See the Demo - Preparing your SQL Azure Account)
4.  SQL Server Authentication
5.  Username: HoLTestUser
6.  Password: REPLACE_HoLTestUser_PASSWORD
7.  Press the Options button / ·  The HoLTestUser is in the db_owner role in the HoLTestDB database. This means that we’ll be able to execute DDL statements to create tables and objects. /
8.  Type HoLTestDB into the Connect to database combo box.
9.  Click Connect. / ·  We need to specify that we want to connect to the HoLTestDB database. /
10.  Click New Query
11.  Execute the query
CREATE TABLE HoLTestTable
(
MyRowID int PRIMARY KEY CLUSTERED
)
/ ·  Let’s start by creating the simplest of tables. A table with a single column of type int.
·  An important thing to note here is that our table has a clustered index. While it is possible to create a table in SQL Azure without a clustered index you will not be able to insert any data until you’ve actually added a clustered index to your table.
·  Remember that your clustered index needn’t necessarily be on your primary key column. For performance reasons it’s often better placed on other columns. /
12.  Execute the following query
INSERT INTO HoLTestTable VALUES (1)
GO
INSERT INTO HoLTestTable VALUES (2)
GO
INSERT INTO HoLTestTable VALUES (3)
GO / ·  Let’s insert some data into our table. /
13.  Execute the query
SELECT * FROM HoLTestTable / ·  And then we can select the data back out. /

Create a More Complex Table and Index

In this segment, we will create a more complex table and an index on one of the columns.

Action / Script / Screenshot
14.  Execute the query
CREATE TABLE [Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[Title] [nvarchar](8) NULL,
[FirstName] [nvarchar] (50) NOT NULL,
[LastName] [nvarchar] (50) NOT NULL,
[EmailAddress] [nvarchar](50) NULL,
[Phone] [nvarchar] (30) NULL,
[Timestamp] [timestamp] NOT NULL
) / ·  Let’s create something a bit more sophisticated- a customer table. /
15.  Execute the query
CREATE INDEX IX_Customer_EmailAddress
ON Customer (EmailAddress) / ·  And before we work with it we’ll add an index to our table on the EMailAddress column. /
16.  Execute the Query
INSERT INTO [Customer]
([Title],[FirstName],[LastName],[EmailAddress],[Phone])
VALUES
('Mr','David','Alexander','','555-1234-5555') / ·  Now we will add a row to our table. /

Examine the Query Plan

In this segment, we will look at the Query plan that SQL Azure uses to query data from our Customer table. We will execute some T-SQL to insert 10,000 rows and compare the differences in the plan.

Action / Script / Screenshot
17.  Execute the query
SET SHOWPLAN_ALL ON
GO
SELECT * FROM Customer WHERE EmailAddress = ''
GO
SET SHOWPLAN_ALL OFF / ·  Let's query the one row back out. But, let’s do something a bit different and actually retrieve the query plan for this statement.
·  The query plan describes the approach that the SQL Azure query processor is going to take to executing our query.
·  The query plan will likely show that it is performing a clustered index scan. This means that it’s scanning through the rows on disk and finding the one with an email address of
·  In a table containing only one row this makes more sense than using the index we just created. /
18.  Execute the query
CREATE PROCEDURE AddData
@NumRows int
AS
DECLARE @counter int
SELECT @counter = 1
WHILE (@counter < @NumRows)
BEGIN
INSERT INTO [Customer]
([Title],[FirstName],[LastName],[EmailAddress],[Phone])
VALUES
('Mr','David','Alexander',CAST(@counter as nvarchar)+'','555-1234-5555')
SELECT @counter = @counter + 1
END / ·  Let’s add a bunch more rows to our table to see if we can get the query processor to take a different approach and use our index on email address.
·  We'll do this by creating a stored procedure that will let us insert an arbitrary number of rows. /
19.  Execute the statement
EXEC AddData 10000 / ·  Now we'll add 10,000 rows by calling our stored procedure.
·  This may take some time /
20.  Execute the query
SET SHOWPLAN_ALL ON
GO
SELECT * FROM Customer WHERE EmailAddress = ''
GO
SET SHOWPLAN_ALL OFF / ·  Now let’s execute the select statement again and look at the query plan again.
·  Note how this time it has used our Customer_Email index? That is the Index Seek operation you see in the result. /
21.  Press Ctrl-L to show the estimated query plan. / ·  Of course a picture tells 1000 words and because we’re using SQL Server Management Studio we can also view the query plan in a nice graphical format. /
22.  Execute the query
DROP TABLE Customer / ·  Let’s finish by dropping that table along with the 10,000 rows we created. /

Summary

In this demo we’ve created some objects in our database and then executed queries against these. We’ve seen how more advanced operations such as examining the query plan are possible in SQL Azure and we’ve looked at how this is largely supported by SQL Server Management Studio.