Microsoft Azure SQL Database Step by Step

---

5. Repeat steps 3 and 4 to enter three more wines to add to the table as follows:

a. Enter a row for McLaren Valley, Cabernet, 2005.

b. Enter a row for Mendoza, Merlot, 2010.

c. Enter a row for Valle Central, Merlot, 2009.

6. Click Save in the toolbar at the top of the page.

None of the data you entered is actually saved to the database until you click Save in step 6. At that point, the rows are inserted and the display is refreshed to show the WineId primary-key values that were automatically assigned by SQL Database. Being the very first four rows added to the table, those primary keys were assigned the numbers 1 through 4, as shown in Figure 1-19.

FIGURE 1-19 The Wine table populated with rows of data with automatically assigned primary-key identity values

Now add some data to the Customer table. You’ll use the same procedure you just followed for the Wine table. The only additional consideration to keep in mind is that each customer has a foreign-key value that identifies that customer’s favorite wine. Because you informed SQL Database about this foreign-key relationship in a previous procedure, you can only supply a value of 1 through 4 for each customer’s FavoriteWineId column (or other integers for rows that are added to the Wine table in the future). Your only other option is to supply NULL, becauseFavoriteWineId is optional (meaning that it’s OK if the customer’s favorite wine is unknown). Otherwise, as we’ll demonstrate, SQL Database will not permit you to add a customer row with a non-NULL value for FavoriteWineId that does not have a related row in the Wine table.

To populate the Customer table, follow these steps:

1. Navigate to the Customer table design page.

2. Click Data at the top of the page.

3. Click Add Row

4. Enter a row with FirstName, LastName, and FavoriteWineId values of Jeff, Hay, and 4, respectively.

5. Click Add Row again to enter another row for Mark, Hanson, 3.

6. Click Save to save the rows to the database. The two rows are automatically assigned primary key values of 1 and 2 for the CustomerId column.

7. Click Add Row again to enter a third row for Jeff, Phillips, but this time type a 6 for the FavoriteWineId.

8. Click Save. Now SQL Database complains of an error.

9. Expand the Error Details as shown in Figure 1-20. This displays the error message describing the foreign-key conflict that occurred because there is no row in the Wine table with a WineId of 6.

FIGURE 1-20 Error message displayed when attempting to violate a defined foreign-key relationship

10. Correct the problem by changing the 6 to a 2.

11. Click Save. Now Jeff Phillips gets saved to the Customer table. (See Figure 1-21.)

FIGURE 1-21 The Customer table populated with several rows of data

Interestingly, the Jeff Phillips row was assigned a primary key value of 4, skipping over the next available value (which, after Mark Hanson, would have been 3). This is because the number 3 got “used up” during the failed insert attempt that occurred with the foreign-key conflict,

Querying the database

Now you have a database with two related, populated tables. The next logical step is to query this data. Because SQL Database is essentially an adapted version of on-premises SQL Server tailored for running on Microsoft Azure, most typical queries can be expressed using the same T-SQL syntax that works with on-premises SQL Server. So the Customer and Wine tables can be easily joined together to display the names of each customer’s favorite wine. Furthermore, they can be filtered to limit the results by some specific criteria.

The SQL Database management portal has an ad-hoc query window that lets you run T-SQL queries and view their results. To query for customers and their favorite wines, follow these steps:

1. Log in to the SQL Database management portal for the WineCloudDb database.

2. Click New Query at the top of the page to open a new query window.

3. Type the following T-SQL code into the query window:

SELECT
c.FirstName,
c.LastName,
w.Category,
w.Name
FROM
Customer AS c
LEFT OUTER JOIN Wine AS w ON c.FavoriteWineId = w.WineId
ORDER BY
c.LastName, c.FirstName;

4. Click Run at the top of the page. SQL Database executes the query and displays the results in the bottom portion of the query window, as shown in Figure 1-22.

FIGURE 1-22 Creating and running a query that joins customers with their favorite wine

5. Modify the query by adding a WHERE clause. Just before the ORDER BY clause, type WHERE w.Category = ‘Merlot’

6. Click Run again. The query executes once more, this time returning only customers whose favorite wine is a Merlot, as shown in Figure 1-23.

FIGURE 1-23 Applying a query filter using the WHERE clause

7. Change the WHERE clause of the query from w.Category = ‘Merlot’ to w.Year < 2010.

8. Click Run once more. This time, the query filters on the wine year, as shown in Figure 1-24.

FIGURE 1-24 Filtering on the customer’s favorite wine year

In step 3, notice how you joined the Customer and Wine tables in the query’s FROM clause. The LEFT OUTER JOIN ensures that customer rows are returned even if they contain a NULL in FavoriteWineId—that is, even if they have no favorite wine. Using an INNER JOIN instead would automatically exclude customers without a favorite wine.

The initial version of this query had no WHERE clause, and with a LEFT OUTER JOIN, there was no filtering at all. So, at first, it returned every customer and displayed their favorite wine. (NULL would be returned for the wine name and category for customers without a favorite wine.)

By adding the WHERE clause in step 5, you asked SQL Database to filter the results to include only customers whose favorite wine is any kind of Merlot. The Wine table was aliased as w in the FROM clause, so w.Category in the WHERE clause refers to, and filters by, the Categorycolumn in the row joined in from the Wine table. Running this version of the query returns just the two customers with Merlot as their favorite wine category.

In step 7, you changed the WHERE clause to filter by w.Year, which is the year of the wine. Notice how this column is not actually in the result set returned by the SELECT statement, yet it is perfectly valid to filter on it. This version of the query now returns the two customers with favorite wines (in any category) older than 2010. These are Jeff Hay (with a Velle Central Merlot from 2009) and Jeff Phillips (with a McLaren Valley Cabernet from 2005). Mark Hanson’s favorite wine is the Mendoza Merlot, but he is filtered out from these results because that wine is from 2010, and the query is returning only customer rows with favorite wines that are older than 2010.

Exploring additional portal capabilities

In just this first chapter alone, you’ve already accomplished quite a lot with SQL Database. You’ve created a server and a database, defined tables with relationships, populated data, and executed queries. And you’ve done all those things with nothing more than a browser, using the Microsoft Azure management portal and the Microsoft Azure SQL Database management portal. But all that is still just scratching the surface of what’s possible.

The SQL Database management portal has matured greatly since the early days of Microsoft Azure (when it was called the SQL Azure management portal). And you can expect it to continue evolving—quite possibly even by the time this book goes to press. Before concluding the chapter, we recommend you take the time to examine some of these additional capabilities available in the current SQL Database management portal at the time of this writing.

Creating views

Views are essentially encapsulated queries that are stored in the database. In most respects, your queries can treat views just as ordinary tables.

For example, you could create three views that encapsulate the three versions of the query from the previous section “Querying the database.” You might name those three views as follows:

CustomersWithFavoriteView

CustomersWIthFavoriteMerlotView

CustomersWithFavoritePre2010View

With those views in place, it becomes much easier to query the database. For example, you can just select from the CustomersWithFavoriteMerlotView, instead of writing the lengthier version of the query that joins the Customer and Wine tables.

Creating stored procedures

Like views, stored procedures can also encapsulate queries, but they can also do much more than that. In addition to returning data from queries, stored procedures can contain any T-SQL logic. They can also accept parameters, update data, and call other stored procedures.

Stored procedures are commonplace in professional relational databases. They are often used to protect underlying tables from inappropriate usage. They can also build on views, implement business logic, or further abstract details of the underlying database structure—hiding the way that tables, views, and columns are named; how the table relationships are defined; and so on. Essentially, and particularly from the perspective of designing multitiered layered architectures, stored procedures can be effectively leveraged to implement a service layer over your data, at the database level.

Here’s a stored procedure you can create that joins the Customer and Wine tables to return customers with their favorite wine, just as you did in the previous query. But this stored procedure will have some added flexibility; it will accept a @FavoriteWineId parameter so that the results can be limited to returning just those customers whose favorite wine matches the value passed in through this parameter. If NULL is specified for @FavoriteWineId, the stored procedure will return all customers. As before, you will use an OUTER JOIN, so if no parameter value is passed, even customers with no favorite wines will be returned (and NULL will be returned for the name of their favorite wine). You will aptly name this stored procedure GetCustomers.

To create the GetCustomers stored procedure, follow these steps:

1. Log in to the Microsoft Azure SQL Database portal.

2. Click the Design tab on the left side of the page.

3. Click Stored Procedures at the top of the page.

4. Click New Stored Procedure. The stored procedure designer opens with a default name of Stored Procedure1.

5. Change the Stored Procedure name to GetCustomers.

6. Click Add Parameter to create a new parameter named @Parameter1 with a data type of nvarchar(50).

7. Change the parameter name to @FavoriteWineId.

8. Click the drop-down list beneath Select Type to change the parameter data type to int.

9. Type the following code into the code window:

SELECT
c.FirstName, c.LastName, w.Category, w.Name
FROM
Customer AS c
LEFT OUTER JOIN Wine AS w ON c.FavoriteWineId = w.WineId
WHERE
(@FavoriteWineId IS NULL) OR (c.FavoriteWineId = @FavoriteWineId)
ORDER BY
c.LastName, c.FirstName;

10. Click Save at the top of the page.

Your screen show appear similar to Figure 1-25.

FIGURE 1-25 Designing the GetCustomers stored procedure

As we explained, this stored procedure accepts an integer parameter named @FavoriteWineId. The stored procedure encapsulates the same join logic between Customer and Wine in the FROM clause you’ve already seen, but it also adds logic in the WHERE clause to determine if a favorite wine was requested (that is, if the value for the @FavoriteWineId parameter was not passed in as NULL). It therefore returns either all customers (if NULL was passed in for @FavoriteWineId) or only those customers with favorite wines that match the value in @FavoriteWineId.

To test the stored procedure, follow these steps:

1. Click New Query at the top of the page to open a new query window.

2. In the new query window, type EXEC GetCustomers @FavoriteWineId = NULL.

3. Click Run at the top of the page to execute the stored procedure, passing in NULL for the @FavoriteWineId parameter. SQL Database returns all three customers and the names of their favorite wine.

4. Modify the EXEC statement from step 2 by changing NULL to 3, which is the WineId for the 2010 Mendoza Merlot.

5. Click Run again. This time, only Mark Hanson is returned, because (currently) he’s the only customer that has selected 2010 Mendoza Merlot as his favorite wine.

Database administration features

The SQL Database management portal also offers several database administration features you should be aware of, such as DACPAC and BACPAC support (for migration and backup) and event tracking.

The portal supports the Data-tier Application Component Package file format, commonly referred to as DACPAC files. A DACPAC file contains the complete definition of a database, and it can be leveraged for streamlined incremental deployments of the database design. A BACPAC file is similar, except that in addition to the definition of the database, it includes actual data as well. In the management portal, there is full support for importing and exporting DACPAC and BACPAC files. Locally, SQL Server Data Tools (SSDT) in Visual Studio can also be used to define DACPAC files and deploy them to Microsoft Azure SQL Database. You will learn more about DACPAC and BACPAC in later chapters.

You can also track events from the SQL Database management portal. This lets you keep an eye on things like database connections (whether they succeed or fail), deadlocks, and throttling events.

Summary

This chapter got you acquainted with Microsoft Azure SQL Database. We began with an overview of Azure and cloud computing, and then demonstrated how easy it is to get signed up for a Microsoft account and an Azure subscription. You then used the Microsoft Azure management portal to quickly create a new server, and then create a new database on that server.

You also learned how to use the SQL Database portal (which you launched from the Microsoft Azure portal using a special management URL) to design the database. You created and populated two related tables, and then you opened a query window to run a few SELECT queries that joined the tables. You also learned about creating views, creating stored procedures, and the availability of other database administration features in the SQL Database management portal.

Now that we’ve introduced you to the SQL Database platform, you’re ready to move on to Chapter 2, where we will delve more deeply into the details of setup and configuration.