Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
Microsoft SQL Server Programming
- An Outline for Development
- Database Services
- Database Objects: Databases
- Database Objects: Tables
- Database Objects: Table Relationships
- Database Objects: Keys
- Database Objects: Constraints
- Database Objects: Data Types
- Database Objects: Views
- Database Objects: Stored Procedures
- Database Objects: Indexes
- Database Objects: User Defined Functions
- Database Objects: Triggers
- Database Design: Requirements, Entities, and Attributes
- Business Process Model Notation (BPMN) and the Data Professional
- Business Questions for Database Design, Part One
- Business Questions for Database Design, Part Two
- Database Design: Finalizing Requirements and Defining Relationships
- Database Design: Creating an Entity Relationship Diagram
- Database Design: The Logical ERD
- Database Design: Adjusting The Model
- Database Design: Normalizing the Model
- Creating The Physical Model
- Database Design: Changing Attributes to Columns
- Database Design: Creating The Physical Database
- Database Design Example: Curriculum Vitae
- The SQL Server Sample Databases
- The SQL Server Sample Databases: pubs
- The SQL Server Sample Databases: NorthWind
- The SQL Server Sample Databases: AdventureWorks
- The SQL Server Sample Databases: Adventureworks Derivatives
- UniversalDB: The Demo and Testing Database, Part 1
- UniversalDB: The Demo and Testing Database, Part 2
- UniversalDB: The Demo and Testing Database, Part 3
- UniversalDB: The Demo and Testing Database, Part 4
- Getting Started with Transact-SQL
- Transact-SQL: Data Definition Language (DDL) Basics
- Transact-SQL: Limiting Results
- Transact-SQL: More Operators
- Transact-SQL: Ordering and Aggregating Data
- Transact-SQL: Subqueries
- Transact-SQL: Joins
- Transact-SQL: Complex Joins - Building a View with Multiple JOINs
- Transact-SQL: Inserts, Updates, and Deletes
- An Introduction to the CLR in SQL Server 2005
- Design Elements Part 1: Programming Flow Overview, Code Format and Commenting your Code
- Design Elements Part 2: Controlling SQL's Scope
- Design Elements Part 3: Error Handling
- Design Elements Part 4: Variables
- Design Elements Part 5: Where Does The Code Live?
- Design Elements Part 6: Math Operators and Functions
- Design Elements Part 7: Statistical Functions
- Design Elements Part 8: Summarization Statistical Algorithms
- Design Elements Part 9:Representing Data with Statistical Algorithms
- Design Elements Part 10: Interpreting the Data—Regression
- Design Elements Part 11: String Manipulation
- Design Elements Part 12: Loops
- Design Elements Part 13: Recursion
- Design Elements Part 14: Arrays
- Design Elements Part 15: Event-Driven Programming Vs. Scheduled Processes
- Design Elements Part 16: Event-Driven Programming
- Design Elements Part 17: Program Flow
- Forming Queries Part 1: Design
- Forming Queries Part 2: Query Basics
- Forming Queries Part 3: Query Optimization
- Forming Queries Part 4: SET Options
- Forming Queries Part 5: Table Optimization Hints
- Using SQL Server Templates
- Transact-SQL Unit Testing
- Index Tuning Wizard
- Unicode and SQL Server
- SQL Server Development Tools
- The SQL Server Transact-SQL Debugger
- The Transact-SQL Debugger, Part 2
- Basic Troubleshooting for Transact-SQL Code
- An Introduction to Spatial Data in SQL Server 2008
- Performance Tuning
- Practical Applications
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
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.
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:
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.
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.
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.
Same as above
Demonstrates how to build a data warehouse
Same as above
Used to build an AS database for business intelligence scenarios
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.
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.
This is the link for the SQL Server 2000 sample databases (pubs and Northwind).
This is the link for the SQL Server 2008 sample databases.