SQL Server Reference Guide

This article is the next in a series that explains the sample databases that you can use with SQL Server, and in this tutorial I'll show you how to find, install and work with the primary sample database that ships with SQL Server 2005 and later: AdventureWorks. By the way, I often get asked if this database can be used with earlier versions. The answer to that is no — the reason why is that it contains constructs and features not found in earlier versions.

When I started using SQL Server 2005, the first thing I did was to install the pubs and Northwind sample databases. I was comfortable with those, I knew the structure and the data, and I was able to get to work quickly. For quite a while I worked that way — but one day I was preparing to teach a class on database design and needed to include new data types and higher table counts, and so I installed AdventureWorks.

The AdventureWorks database is a complete departure from previous sample databases. It is quite large, uses the new user-schema separations, and even comes with a detailed business case surrounding it. Previously, the databases were not always explained fully, but that's not the case with AdventureWorks.

Microsoft has included dozens of sample applications with the database, including the samples found in Books Online. Simply put, you could learn more than many DBAs ever know about database design if you simply worked through the examples included with AdventureWorks. As I started working with those samples myself and reading the documentation, I found that this was my new database of choice — although I still keep those small databases like pubs around.

And AdventureWorks fun doesn't stop there. This database has spawned other derivatives, which I'll explain in the next tutorial in this series:

• AdventureWorksLT — a smaller sample OLTP database
• AdventureWorksDW — a sample data warehouse
• AdventureWorksAS — a sample analysis services database

Not only that, with each release of SQL Server since 2005, as of this writing the AdventureWorks database is changed along with it. That means you'll see an AdventureWorks2008, AdventureWorks2008R2 and so on. Let's take a look at where you can find this database and how to install it.

Finding and Installing the AdventureWorks Database

When you install SQL Server 2005, you can choose to install the samples along with the product. Once again, on my test and even development systems, I always do this, and recommend that you do as well. I do not recommend installing sample databases on production servers, although I have done it from time to time.

If you don't install the samples when you first install the product, not to worry. Grab that DVD and follow the instructions at this link, about halfway down the page. Note that this isn't the way to re-install a sample database once you've toyed with it — this is the process you follow if you've never installed it.

You can also install the AdventureWorks database by restoring it from a backup taken from another system, as I've described in the previous tutorials in this series.

Once again, I always take a backup of the database right away. The code for that is quite simple. Sure, you can do this graphically as well by right-clicking the database name and then selecting the All Tasks…| Backup option from the menu, but the code is very simple to type and save. To back up the database to the TEMP directory, I simply type:

USE master;
GO
BACKUP DATABASE AdventureWorks TO DISK = 'c:\temp\AdventureWorks.bak'
WITH INIT;
GO

Now I can make changes to the sample database, and put it back quickly and easily - sort of a "reset" if you will. 

And you have yet another option. You can actually build the database from scripts. This is handy if you want to change something basic like file layouts or collations. All you have to do is grab that DVD again and follow the instructions at this link.

Structure and Contents

Any design, as I've written about before, comes down to the business scenarios that the system was designed to meet. Proper layout of these scenarios is absolutely essential. But learning to create a scenario is not something that is taught in a lot of DBA classes — they are more concerned with learning the product than they are in learning to use it. To be fair, I've taught a number of these courses, and there simply isn't the time to do it all.

As you may recall, the pubs sample database is based around a fictional publishing company, including books, authors and sales. The Northwind database is based on a food import and export company. AdventureWorks is a bicycle manufacture, assembly and sales business, and it is not only international in scope, in one part of its business description you find that the company bought another company in a different country. 

You can learn the entire business environment by reading and understanding the business scenarios created for AdventureWorks. When you do, you'll understand the data dictionary laid out for the system much better.

The AdventureWorks database is much larger than the previous sample databases. It has around 69 tables and many views, stored procedures, User-Defined Functions, several data types, and so on. I'll start with a simple extraction of the schemas, which are the main containers for the objects within the database. If designed with schemas in mind, you can learn a lot about the database by understanding the schemas. The schemas revolve around the following large business areas:

• Sales and Marketing
• Products and vendors
• Manufacturing

These are only the schemas. You can find the entire data dictionary here.

I do not recommend creating a single Entity Relationship (ERD) map of the AdventureWorks database using the database diagram tool in SQL Server 2005. In fact, I don't model it using any ERD tools — at least not the entire database. Much like one a real production database, the structure is just too complex to see in one view.

I have used modeling tools to take a "slice" of the tables to model the relationships between them. That's one way to look at the information I described in those tables earlier.

In fact, it's a great exercise to "discover" the relationships of, say, sales to customers using AdventureWorks. That means using the statements and tools I explained in the last tutorial to find the tables, their relationships, the indexes, stored procedures, views, data types and so on for the sales and the customers in the database.

Sample Database Code

Another great selling point about AdventureWorks is the amount of examples available for it. Of course, most of the examples in Books Online are based on AdventureWorks, but even beyond that are specific examples that show you how to do "DBA" work, like FileGroups, Event Notification and so on. Check those out here.

There are a few basic examples I use to show result sets for classes, demos and testing In previous sample databases, we're left to come up with many of the primary examples ourselves. But in AdventureWorks, Microsoft created not only the examples scattered throughout Books Online, but many that show the primary relationships. Rather than reiterate those, I'll direct you to this link that shows you just about everything you need: http://technet.microsoft.com/en-us/library/ms124825(SQL.100).aspx The links near the bottom of that page have many examples within those areas, complete with the proper joins.

Sample Application Code

No database exists for the data alone — you need some sort of way for your users to get the data in and out. Unless you want to teach them all Transact-SQL, you‘ll want a "front end" application. In the previous sample databases, Microsoft did not spend a great deal of time on the application code for those, instead focusing on the T-SQL statements for the DBA. This time, however, Microsoft provides several application examples, which you can find here. If you want to learn to code database applications, definitely download and examine these. Installing and running these applications can help you define the path of an application from start to finish.

In future articles I'll explore these applications in more depth, and what they can teach you about learning to properly program against a SQL Server database. Even if you're not a developer, this can be a very good exercise.

Special Features

And there are yet more examples in Books Online for AdventureWorks. Here's a few of those:

• Full Web Application: http://msdn.microsoft.com/en-us/library/ms160795.aspx
• XML: http://msdn.microsoft.com/en-us/library/ms160786.aspx
• Large Objects: http://msdn.microsoft.com/en-us/library/ms160754.aspx
• Full-Text Search: http://msdn.microsoft.com/en-us/library/ms160844.aspx
• Service Broker: http://msdn.microsoft.com/en-us/library/ms160932.aspx

So you can see why I'm learning to love the new sample database in my life — AdventureWorks. It provides everything you need to learn how to work with SQL Server, from the front end to the back.

Leveraging What You Know

One of the best things about AdventureWorks is that Microsoft realized that we had spent a significant amount of time learning and understanding pubs and Northwind. Luckily, you don't have to throw all that knowledge away.

There are several tables in AdventureWorks that resemble the business structures in pubs and Northwind. In other words, if you've built up examples that you can easily explain to others using pubs, you can use the following chart to show you the breakdown of how AdventureWorks could show the same kind of examples:

And if you're into Northwind instead, you can get the same kind of demo's there as well. As you know, Northwind is a great database to demonstrate sales and purchasing systems, so these tables and columns map well to show the same type of info:

One of the reasons I've really come to like AdventureWorks is its rich business descriptions. As you progress in your DBA career, even if you're not asked to design databases (and you probably will be), you'll need to understand them. The primary reason for this is that at some point your performance tuning efforts will meet the design — there's simply nothing else you can do with the indexes, physical constructs, like files and FileGroups, or anything else. It comes down to "this wasn't thought out properly" or "we've outgrown this design." 

One final word on AdventureWorks sample database — spend some time on your own exploring this system. From the multitude of business scenarios you can demonstrate to the rich data types you can use to experiment with for your own designs, it's time well spent. If you still only want to focus on a small dataset that you are very familiar with, take one of those primary schema areas I mentioned earlier (like Manufacturing or even just the Inventory data it holds) and learn that well. Then you'll be able to leverage AdventureWorks without being overwhelmed.