Note:
This exercise makes use of the HoLTestDB database that was created in Exercise 1. If you have not yet created this database, please complete Exercise 1.

Task 1 – Creating Tables and Indexes

1. If you do not have SQL Server Management Studio open then open it by following the instructions in Exercise 1 > Task 2
2. Connect to the HoLTestDB database using the HoLTestUser login as shown in Exercise 1 > Task 3.
3. Now we will add a simple table to our database. Execute the following query in a new query window:

   CREATE TABLE HoLTestTable
   (
   MyRowID int PRIMARY KEY CLUSTERED
   )
   

   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.

4. We will test our table by inserting some rows. Execute the following query:

   INSERT INTO HoLTestTable VALUES (1)
   GO
   INSERT INTO HoLTestTable VALUES (2)
   GO
   INSERT INTO HoLTestTable VALUES (3)
   GO
   

5. Now query the rows back out of the database. Execute following the query

    SELECT * FROM HoLTestTable
   

   

   Figure 1 Querying simple data from SQL Azure

6. Let’s drop that table and create something more sophisticated. Execute the following query

   DROP TABLE HoLTestTable
   

7. Create a Customer table by Executing the following SQL 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
   )
   

8. We will add an index on the EmailAddress field. Execute the following query

   CREATE INDEX IX_Customer_EmailAddress
   ON Customer(EmailAddress)
   

9. Execute the following query to add a row to the new Customer table

   INSERT INTO [Customer]
   ([Title],[FirstName],[LastName],[EmailAddress],[Phone])
        VALUES
   ('Mr','David','Alexander','davida@fabrikam.com','555-1234-5555')
   

10. Now let’s query the data back out, but, let’s start by enabling the SHOWPLAN_ALL option to show the execution plan. Execute the following query

    SET SHOWPLAN_ALL ON
    GO
    SELECT * FROM Customer WHERE EmailAddress ='davida@fabrikam.com'
    GO
    SET SHOWPLAN_ALL OFF
    

    

    Figure 2 Showing the Query Plan

11. Let’s add a whole bunch more rows to the database and then look at the query plan again. Execute the following query to add a stored procedure named AddData. This stored procedure will loop by adding 1 to the counter each time through and adding a new record with the adding an email address of [Counter]davida@fabrikam.com:

    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)+'davida@fabrikam.com','555-1234-5555')
            SELECT @counter = @counter + 1
        END
    

12. Now add 10,000 rows into the database by running the newly created Stored Procedure. Each row will have a unique email address. Execute the query EXEC AddData 10000

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

13. Execute the following query again

    SET SHOWPLAN_ALL ON
    GO
    SELECT * FROM Customer WHERE EmailAddress ='davida@fabrikam.com'
    GO
    SET SHOWPLAN_ALL OFF
    



Figure 3 Showing the Query Plan

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

14. For the most part, we can use any tool that we might previously have used with SQL Server on premise. For an example of this, let’s look at the query plan graphically. In SQL Server Management Studio press Ctrl-Lto display the Estimated Execution Plan.

    

    Figure 4 Showing the Query Plan