Introduction to SQL Azure
Exercise 2: Working with Data Basic DDL and DML

Collapse All Expand All Language Filter: All Language Filter: Multiple Language Filter: Visual Basic Language Filter: C#

Visual Basic Usage
C#

In this exercise, you will create a new database and work with its data. This means you will create some tables, index those tables appropriately, and then insert and query data. For this purpose, you will use two different tools. The first tool, the Database Manager for SQL Azure, is a browser based Silverlight database administration tool that you can access from the Windows Azure Platform portal. The other tool is SQL Server Management Studio, a tool normally associated with SQL Server management. You will see that this tool is equally useful for managing your SQL Azure databases.

Task 1 – Creating a New Database

1. In the Windows Azure Management portal UI, select the Database option.
2. Under Subscriptions, expand your project in the tree view on the left, select the server name where you wish to create a database and then click Create in the Database group of the ribbon.

   Figure 10
   Creating a new database

3. In the Create Database dialog, set the Database name to HoLTestDB, select the Web Edition and set the Maximum size to 1 GB.

   Figure 11
   Choosing database features

Note:
In this hands-on lab, you create a database using the SQL Azure portal. Databases can also be created by executing a DDL query against your assigned server using the T-SQL CREATE DATABASE statement, specifying which SQL Azure database edition (Web or Business) to create as well as its maximum size. For example, to create a Business Edition database with a maximum size of 30GB, use the following T-SQL command: CREATE DATABASE HolTestDB (MAXSIZE = 30GB) Once a database reaches its maximum size, you cannot insert additional data until you delete some data to free storage space or increase its maximum size.

Task 2 – Managing your Database with the Database Manager for SQL Azure

In this task, you use the Database Manager for SQL Azure, a Silverlight client that runs in your browser, to connect to your SQL Azure database, create and populate a table, and then query its contents.

1. Expand the server node under your subscription, click the HoLTestDB database to select it, and then click Manage on the ribbon.

   Figure 12
   Managing a database

2. You will be redirected to the SQL Azure Management Portal. Enter your Server Administrator username and password and then click Log on.

   Figure 13
   Signing in to the SQL Azure Management Portal

3. Wait until you are connected to your database and the initial page is shown.

   Figure 14
   SQL Azure Management Portal start page

4. In the ribbon, in the New group, click New Table.

   Figure 15
   Creating a new table

5. In the table creation UI, set the Name of the table to People.
6. Next, define three table columns using the information shown below.

   Column | Type | Is Identity? | Is Required? | Is Primary Key?
   --- | --- | --- | --- | ---
   ID | Int | Yes | Yes | Yes
   Name | nvarchar(50) | No | Yes | No
   Age | Int | No | Yes | No

Figure 16
Defining the table schema

7. In the ribbon, in the Edit group, click Save.

   Figure 17
   Saving the table schema

8. Once the table is saved, in the Edit group of the ribbon, click Data.
9. Now, click Add Row and enter sample data for the Name and Age columns.

   Name | Age
   --- | ---
   Alexandra | 16
   Ian | 18
   Marina | 45

Figure 18
Adding rows to the table

10. Repeat the previous step to add another two rows and then click Save in the Edit group of the ribbon to commit the data to the table.
11. Next, click New Query in the Query group of the ribbon.
12. In the query window, enter the following T-SQL statement to select all the rows in the People table and then click Execute in the Query group of the ribbon. Verify that the results grid shows the rows that you entered previously.

    T-SQL | Copy Code
    --- | ---
        select * from People

    Figure 19
    Querying the database

Task 3 – Managing your Database with SQL Server Management Studio

In this task, you use SQL Server Management Studio, a tool typically used for managing SQL Server, to connect to your SQL Azure server and administer it.

1. Open SQL Server Management Studio from Start | All Programs | Microsoft SQL Server 2008 R2 | SQL Server Management Studio. You will be presented with a logon dialog.
2. In the Connect to Server dialog, enter your login information ensuring that you select SQL Server Authentication. SQL Azure currently only supports SQL Server Authentication.

   Note:
   Please replace server name with your server (e.g. REPLACE_SERVER_NAME.database.windows.net.)

   Figure 20
   Connecting to SQL Azure with SQL Server Management Studio

3. Click Connect.
4. You should now see in your Object Explorer the structure of your database. Notice that your SQL Azure database is no different to an on-premise relational database.

   Figure 21
   Object Explorer showing the HoLTestDB database

5. In Object Explorer, select the HoLTestDB database in the tree view and then click New Query on the toolbar.

   Figure 22
   Creating a new query window

6. You now have a query window with an active connection to your account. You can test your connection by display the result of the @@version scalar function. To do this, type the following statement into the query window and press the Execute button. You will get back a scalar result that indicates the edition as Microsoft SQL Azure.

   T-SQL | Copy Code
   --- | ---
       SELECT @@version

   Figure 23
   Retrieving the SQL Azure version

7. Replace the previous query with the statement shown below and click Execute. Notice that the results grid shows the databases currently accessible.

   T-SQL | Copy Code
   --- | ---
       SELECT * FROM sys.databases

   Figure 24
   Query results showing the list of databases in your subscription

8. You can check that you are now in the context of your user database by executing the following query. Make sure that you replace the previous query.

   T-SQL | Copy Code
   --- | ---
       SELECT db_name()

   Figure 25
   Querying the database currently in use

9. Do not close the query window. You will need it during the next task.

Task 4 – Creating Logins and Database Users

Much like SQL Server, SQL Azure allows you to create additional logins and then assign those logins as users with permissions on a database. In this task, you will create a new login and then create a user that uses the new login in your HoLTestDB database.

1. Open a new query window connected to the master database. To do this, in Object Explorer, expand the System Databases node inside Databases and then select master. Then, click New Query on the toolbar.

   Figure 26
   Querying the master database

   Note:
   You cannot reuse the previous query window connected to the HoLTestDB database because you cannot change the database context without closing the current connection. The USE <database_name> command does not work with SQL Azure. Therefore, you need to open a new query window or disconnect and reconnect in order to change from the HoLTestDB to the master database.

2. Create a new login by executing the following statement:

   T-SQL | Copy Code
   --- | ---
       CREATE LOGIN HoLUser WITH password='Password1'

   Note:
   You should choose your own password for this login account and use it where appropriate throughout the lab. If you do not choose a unique password, you should ensure that you remove this login when you finish the lab. To do this, execute the following statement in the master database: DROP LOGIN HoLUser

3. Go back to the query window connected to the HoLTestDB database. If you closed this window, open it again by selecting the HoLTestDB database in Object Explorer and then click New Query.
4. In the query window, execute the following statement to create a new user in the HoLTestDB database for the login HoLUser.

   T-SQL | Copy Code
   --- | ---
       -- Create a new user from the login and execute
       CREATE USER HoLUser FROM LOGIN HoLUser

5. Next, add the user to the db_owner role of your HoLTestDB database by executing the following:

   T-SQL | Copy Code
   --- | ---
       -- Add the new user to the db_owner role and execute
       EXEC sp_addrolemember 'db_owner', 'HoLUser'

   Note:
   By making your user a member of the db_owner role, you have granted a very extensive permission set to the user. In a real world scenario, you should be careful to ensure that you grant users only the smallest privilege set possible.

6. Change the user associated with the current connection to the newly created HoLUser. To do this, right-click the query window, point to Connection, and then select Change Connection.

   Figure 27
   Changing the database connection properties

7. In the Connect to Database Engine dialog, replace the Login name with HoLUser and set the Password to the value that you chose earlier when you created the database user.

   Figure 28
   Connecting to the database as a different user

8. Click Options to show additional connection settings. Switch to the Connection Properties tab and ensure that the name of the database for the connection isHoLTestDB. If the current value is different, you will need to type this rather than use the drop down list, then press the Connect button.

   Figure 29
   Connecting to a specific database

   Note:
   You are now connected to the database as the HoLUser database user. You will continue with this user for the remaining steps of this exercise.

Task 5 – Creating Tables, Indices, and Queries

1. In the query window, replace the current content with the following SQL query to create a Contact table and execute it.

   T-SQL | Copy Code
   --- | ---
       CREATE TABLE [Contact](
           [ContactID] [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
       )

   Note:
   SQL Azure requires that every table have a clustered index. If you create a table without a clustered index, you will not be able to insert rows into the table until you have created one. Because the clustered index determines the order of rows on disk, and thus affects certain queries, you may choose to place the clustered index on a column other than the primary key column.

2. You will add an index on the EmailAddress field. To do this, execute the following query:

   T-SQL | Copy Code
   --- | ---
       CREATE INDEX IX_Contact_EmailAddress
           ON Contact(EmailAddress)

3. Execute the following query to add a row to the new Contact table:

   T-SQL | Copy Code
   --- | ---
       INSERT INTO [Contact]
       ([Title],[FirstName],[LastName],[EmailAddress],[Phone])
            VALUES
       ('Mr','David','Alexander','davida@fabrikam.com','555-1234-5555')

4. Now, query the data back out, but start by enabling the SHOWPLAN_ALL option to show the execution plan. To do that, execute the following query:

   T-SQL | Copy Code
   --- | ---
       SET SHOWPLAN_ALL ON
       GO
       SELECT * FROM Contact WHERE EmailAddress ='davida@fabrikam.com'
       GO
       SET SHOWPLAN_ALL OFF

   Figure 30
   Query execution plan for a small set

5. Next, you will add a large number of rows to the database and then examine the query plan again. To do this, execute the following query to create a stored procedure named AddData. This stored procedure will loop incrementing a counter each time through and add a new record with an email address with the pattern [Counter]davida@fabrikam.com:

   T-SQL | Copy Code
   --- | ---
       CREATE PROCEDURE AddData
       @NumRows int
       AS
       DECLARE @counter int
       SELECT @counter = 1
       WHILE (@counter < @NumRows)
       BEGIN
           INSERT INTO [Contact]
               ([Title],[FirstName],[LastName],[EmailAddress],[Phone])
               VALUES
               ('Mr','David','Alexander',CAST(@counter as nvarchar)+'davida@fabrikam.com','555-1234-5555')
               SELECT @counter = @counter + 1
       END

6. Now, insert 10,000 rows into the Contact table by executing the new stored procedure. Each row will have a unique email address. To do this, execute the following T-SQL statement:

   T-SQL | Copy Code
   --- | ---
       EXEC AddData 10000

   Note:
   It may take some time to generate the 10,000 rows.

7. Execute the following query again and examine the generated plan. Compare the result with the one obtained previously, when the table contained few rows.

   T-SQL | Copy Code
   --- | ---
       SET SHOWPLAN_ALL ON
       GO
       SELECT * FROM Contact WHERE EmailAddress ='davida@fabrikam.com'
       GO
       SET SHOWPLAN_ALL OFF

   Figure 31
   Query execution plan for a large set

   Note:
   Notice that the second time around the query optimizer is likely to use the index that you defined: This is the Index Seek line in the query plan.

8. For the most part, you can use any tool that you might have previously used with SQL Server on-premise. For an example of this, look at the query plan graphically. In SQL Server Management Studio press Ctrl-L to display the Estimated Execution Plan.

   Figure 32
   Showing the query execution plan graphically