Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

The SQL Server Sample Databases

Last updated Mar 28, 2003.

I give a lot of presentations. No, I’m not in marketing, I’m in a technical role, but there are many times that I have to explain some technology or concept to another technical audience.

To do that, I mix a discussion of theory, and an explanation of the architecture of the technical feature. Most of the people I deal with are very familiar and comfortable with this format, but there’s simply nothing like showing the concept or feature using a demonstration.

I also have a lot to learn. Any time a new feature comes out in SQL Server, or when I need to brush up on how to use a particular function, stored procedure or datatype. And from time to time I need to test a piece of code or a function I’ve written myself.

So in all these cases I need a test database. Sure, I can write and develop one of those myself, but happily Microsoft has several that they have created. The advantage of using one of these “sample” databases is that Microsoft puts a lot of thought into them, and they include most all of the supported datatypes, many sample stored procedures, lots of views and so on.

Another advantage is that the sample databases are well known. They are documented by Microsoft in Books Online, and they base most of the examples on the sample databases. Since Microsoft includes the sample databases on installation (if you select them), many other authors (yours truly included) use these databases for their examples as well.

You should take a little time to learn at least one of these databases thoroughly, and be on speaking terms with the rest of them. You probably have the same needs that I do, such as presenting, learning and testing. In the next few tutorials I’ll introduce you to these databases, show you where to get them and the options you have for installing them, and give you a few examples on working with them.

There are three main sample databases that you can use with SQL Server, starting with a small (and admittedly simplistic) sample database that shipped with the earliest versions of SQL Server all the way through a complicated set of databases built from a single, huge database in the latest versions that have almost every scenario covered. Which should you use? Well, I personally carry the smaller databases with me all the time and I install all of the sample databases on my testing and development systems.

So let’s get started. In this overview, I’ll list out the sample databases and what they look like, and how to install them. At the end of the article I’ll show you where you can get them.

Samples Available

As I mentioned earlier, there are three main databases you can download and install on your system. In general, the earlier versions install just fine on the higher version. I’ll explain that further in just a moment.

Here is a quick rundown of the samples you have available:

Database Name

Scenario

Comments

pubs

An Unnamed Book Publishing Company

Introduced in the oldest versions of SQL Server, this database was grandfathered in from the Sybase installation, when SQL Server was based on that engine code. Very small installation, easy to understand, and still my all-time favorite, but it is limited.

Northwind

A company called Northwind Traders, which imports and exports specialty foods from around the world

A larger database introduced in Microsoft Access and SQL Server 2000. I use this one periodically, especially if someone is upgrading from Access and is familiar with it.

AdventureWorks

An OLTP database that supports standard online transaction processing scenarios for a bicycle manufacturer called Adventure Works Cycles, including Manufacturing, Sales, Purchasing, Product Management, Contact Management, and Human Resources

Introduced in SQL Server 2005, and still in use. An extensive, huge, complex database with lots of datatypes, schemas, views, stored procedures, functions, and just about everything else you could want. I’m still learning to use it.

AdventureWorksDW

Same as above

Demonstrates how to build a data warehouse

AdventureWorksAS

Same as above

Used to build an AS database for business intelligence scenarios

AdventureWorksLT

Similar to above

A highly simplified version of AdventureWorks, much smaller and less complex, helpful for those who are new to databases. I still don’t use this one as much as pubs, but I have played with it a bit.

Installing

You can install the databases using several methods. The most straightforward method is to visit the links I’ve got at the bottom of this tutorial and use the MSI (Microsoft Installer) packages. The one drawback here is that not all of the installer packages work for the earlier databases (such as pubs) for the higher versions. In some cases, whoever wrote the installer thought the process through quite well. They queried the data directories and drives and automatically install the databases there, but in others, well, not so much. Some of the installers install the database, others just drop you off with a “Done” sign and you have to hunt the files down and use the sp_dbattach or RESTORE commands to get them. In the following articles I’ll make sure I perform an install on each of the databases to tell you the exact process.

In some of the later databases, you can download the Data Definition Language (DDL) and Data Manipulation Language (DML) statements to build the database for you. This avoids the whole problem of where the database gets installed and has the advantage of allowing you to change the installation options. I’ve also used this option to learn the DDL and DML myself.

The method I’ve used most often, however, is to install the databases and then back them up with a normal maintenance plan. Then I can just restore them to the newer versions, one up at a time. In other words, I restored pubs from 7 to 2000, and then from 2000 to 2005 and so on. This works great, but I have to remember the WITH MOVE part of the RESTORE command to place the files in the right place, and also I have to set the compatibility level to the new version once I’m done. I’ll show you how to do that as well in the next few tutorials.

InformIT Articles and Sample Chapters

An oldie but a goodie – This sample chapter from all the way back in SQL Server 7.0 talks about restoring the pubs database.

Books and eBooks

Here’s a great book that uses pubs to teach you about Visual Basic for Applications.

Online Resources

This is the link for the SQL Server 2000 sample databases (pubs and Northwind). 

This is the link for the SQL Server 2005 sample databases (all the AdventureWorks flavors).  

This is the link for the SQL Server 2008 sample databases.