Problem
I’m trying to learn T-SQL and write better queries. I am looking for sample databases to practice with. In various documents, I see references to AdventureWorks and WideWorldImporters, but I don’t know where to find them; they aren’t included with SQL Server. Where can I locate these SQL Server sample databases and any others that might be relevant?
Solution
There are a lot of sample databases and data sets available that you can use. However, let’s stick with the Microsoft-provided ones, including a couple you might see in older articles and books.
In this article, we will look at how you can get copies of the following sample databases:
- Northwind and Pubs
- AdventureWorks
- WideWorldImporters
Northwind and Pubs Sample Databases
Once upon a time, the sample databases that could be installed with SQL Server were named Northwind and Pubs. They are both small databases that provide a basic structure and some data to work with.
- Northwind simulates the order and inventory data for a fictional company called Northwind Traders.
- The Pubs database simulates a small set of publishers, authors, books, and sales information about them.
Microsoft provides the scripts as part of their SQL Server samples GitHub repository. The specific location for the Northwind and Pubs creation scripts: https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs
The two scripts to download are instnwnd.sql and instpubs.sql. As the names imply, these scripts can be run in SQL Server Management Studio (SSMS).
Before running them, see the notes below about the instnwnd.sql script.
Creating the Pubs Database
Let’s start with the Pubs script, instpubs.sql.
This script checks to see if the Pubs database exists, and if it does, it’ll drop that database. Therefore, if you have an existing Pubs database with anything you wish to retain, preserve your data/database first, such as by renaming it.
The script will then create the Pubs database and execute the rest of the script to create the database objects and load the data into the database. Unless you need to preserve a database by the same name, you can run this without too much concern.
Creating the Northwind Database
The instnwnd.sql script, however, performs no such checks. If we look at the script itself, we’ll find the following comment:
-- This script does not create a database.
-- Run this script in the database you want the objects to be created.
-- Default schema is dbo.For instance, if you only open the script and your connection is using the master database, that’s where everything will be created. As a result, I’ve modified my version to have these lines at the top. Since this is a sample database that I’ll install on a demo SQL Server, I don’t have any error handling – just CREATE and USE.
CREATE DATABASE northwind
GO
USE northwind
GOAdventureWorks Sample Databases
In SQL Server 2008, the Northwind and Pubs databases were swapped out for the AdventureWorks database.
The direct location for the AdventureWorks databases is: https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/adventure-works.
However, if you’re using SQL Server 2012 and above (hopefully you are), you can restore the AdventureWorks databases from backups, which is significantly quicker than running the various scripts to generate the databases. In that case, the URL you’ll want to use is: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks.
Microsoft has provided backups for the respective versions of SQL Server, starting with SQL Server 2012. Download the backups that match your version of SQL Server and perform a restore as you normally would, such as using SSMS.
Three Different AdventureWorks Databases?
Yes, there are three different AdventureWorks databases you can use (OLTP, LT and DW)
The primary one is the OLTP version, which will restore as AdventureWorks[SQL Version] unless you specify a different database name. For instance, on SQL Server 2022, it’ll be AdventureWorks2022.
If you want a smaller version of that database (less data and a greatly simplified data model), that’s what the Lightweight, or LT, backup is for. The LT database will only have a handful of tables, and the amount of data in those tables is greatly reduced. For instance, below are the row counts for the Customer table in each database.
Finally, the data warehousing, or DW, AdventureWorks database employs a dimension model for its database model with fact and dimensional tables.
WideWorldImporters Sample Databases
Finally, we’ll look at the WideWorldImporters database set.
The company modeled here is an international wholesaler. This set of databases was designed to show off new features in SQL Server 2016, and they are a bit bigger than their AdventureWorks counterparts. For instance, the 2022 version of AdventureWorks uses about 198 MB while the WideWorldImporters database has approximately 413 MB.
The base location for these sample databases is: https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers.
To go directly to the backups: https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0.
If you’re restoring to an instance of SQL Server, such as on-premises, you’ll want the .bak files at the top of the list. Note: You must be using SQL Server 2016 or higher. If you are interested in restoring the data to an Azure SQL Database, you’ll also find the .bacpac files at this location.
Similar to AdventureWorks, WideWorldImporters has two types of databases. There is one without the DW, OLTP version, and one with DW, representing an OLAP dimensional model with the appropriate tables for facts and dimensions, like the AdventureWorks data warehouse database.
Next Steps
- Download the appropriate sample databases for your use using the links provided in the article.
- Read up on how to restore a database using T-SQL, especially if you want to automate the restoration of your sample databases from backup.
- Learn how to overwrite a database with a restore, in case you want to set up an automatic refresh of your sample databases.
- Learn the T-SQL basics:
- Be sure to also checkout the SQL Reference Guide.
