SQL Server Essentials: SQL Server Sample Databases
- Blog
- SQL Server
- Post
SQL Server sample databases can be very useful for learning new techniques or trying out various samples of code. In this article, I’ll detail what you can do with the main SQL Server sample databases including Northwind, AdventureWorks, and WideWorldImporters. I’ll also show you how to find these SQL Server sample databases and install them.
What are SQL Server sample databases?
SQL Server sample databases contain fictitious information that can be safely used for testing and development. However, no sample databases have been installed during the SQL Server installation process since SQL Server 2005.
At some point, Microsoft felt that sample databases were a potential security exposure, and that’s why they removed them from the SQL Server code base. That said, over the years Microsoft has provided several sample databases for the SQL Server relational database management system and Analysis Services.
The main sample databases available today are Pubs, Northwind, AdventureWorks, and WideWorldImporters.
As you’ll see throughout this article, these different sample databases give you different types of data you can use for your testing and development scenarios.
Pubs and Northwind sample databases
Pubs is the first sample publisher database that came with SQL Server, and it was originally developed by Sybase. It’s based on a publishing company tracking books, authors, and sales.
The Pubs sample databse used to be available as an option as a part of the SQL Server installation process until the SQL Server 2005 release. However, many SQL Server users thought it was too simple to be a good sample.
To provide a more complex sample database, Microsoft made the Northwind database available with SQL Server 2000 release. It was originally developed by the Access team, and it’s based on a food import and export company called Northwind Traders.
Microsoft later removed the Northwind database with the release of SQL Server 2005. However, SQL Server users can still download them from Northwind and pubs Sample Databases for SQL Server 2000.
The download is a .msi installation file called SQL2000SampleDb.msi. Running the installation file extracts T-SQL database scripts into the default folder (C:\SQL Server 2000 Sample Databases), as you can see below.
You can install the two samples by running the instpubs.sql script to install the Pubs sample, as well as the instnwnd.sql script to install the Northwind sample. The scripts contain all of the code needed to create and populate the tables for each database.
- First, open the .sql files using the SQL Server Management Studio (SSMS) Query editor as you can see in the image below.
- Click the green Execute button to run the scripts.
When I first ran the instpubs.sql script to install the Pubs sample, I got the error “Could not find stored procedure ‘sp_dboption’.” This stored procedure was removed from SQL Server following the SQL Server 2008 release. However, deleting the following lines allowed the script to run with no errors.
execute sp_dboption 'pubs' ,'trunc. log on chkpt.' ,'true' GO
Running the instnwd.sql script to install the Northwinds sample produced the same “Could not find stored procedure ‘sp_dboption’ error.” In this case, deleting the following lines allowed the script to run to completion.
exec sp_dboption 'Northwind','trunc. log on chkpt.','true' exec sp_dboption 'Northwind','select into/bulkcopy','true' GO
Running these T-SQL scripts will create and populate the Pubs and Northwind sample databases. Afterward, you can use them like any other SQL Server database.
AdventureWorks and AdventureWorksLT sample databases
Starting with SQL Server 2005, Microsoft released the AdventureWorks sample database, which was intended to replace Pubs and Northwind. AdventureWorks is an example database for a multinational bicycle manufacturing and sales company called Adventure Works Cycles, and it uses the SQL Server user-schema naming system that Microsoft introduced with SQL Server 2005.
The primary relational versions of the AdventureWorks databases are AdventureWorks and AdventureWorksLT. The latter is a smaller and simpler version of the full AdventureWorks database. There are multiple versions of these databases for each release of SQL Server since SQL Server 2008 R2. However, so far, there hasn’t been a new release for SQL Server 2022.
The AdventureWorks samples for the different SQL Server versions were originally found on CodePlex, but the CodePlex site was shut down in 2017. They have since been moved to GitHub. The table below includes download links for all versions of the AdventureWorks sample database.
These downloads are full database backups (.bak) files that you can use to install the AdventureWorks samples. You can simply download the .bak file and then use SSMS or the T-SQL RESTORE statement to restore the backup file as a SQL Server database.
In the image below, you can see that SSMS restore dialog for the AdventureWorks2019 prompts you for the .bak file to restore and also asks for a database name.
In addition to the relational database samples, Microsoft has also made business intelligence/data warehousing samples for the AdventureWorks database called AdventureWorksDW and AdventureWorksAS. These samples can be used with SQL Server Analysis Services. They are installed just like the relational samples by downloading a restoring a .bak file. The BI samples are at the following GitHub locations:
- AdventureWorksDW2019.bak
- AdventureWorksDW2017.bak
- AdventureWorksDW2016.bak
- AdventureWorksDW2016_EXT.bak
- AdventureWorksDW2014.bak
- AdventureWorksDW2012.bak
- AdventureWorksDW2008R2.bak
WideWorldImporters sample database
With the release of SQL Server 2016, Microsoft made the new WideWorldImporters database available. It’s for an example wholesale novelty goods importer and distributor, and it can be used to get familiar with temporal tables, In-Memory OLTP, columnstore indexes, Row-Level Security (RLS), dynamic data masking (DDM), and more.
There are different versions of the WideWorldImporters relational database for the on-premises version of SQL Server 2016 and higher as well as for Azure SQL Database:
- WideWorldImporters-Full.bak: For the SQL Server 2016 Enterprise edition and higher. It is in the backup file format.
- WideWorldImporters-Standard.bak: For the SQL Server 2016 Standard edition and higher. It is also in the backup file format.
- WideWorldImporters-Full.bacpac: This is for the Azure SQL Database Premium tier. It provides a full-featured OLTP sample database in BACPAC format.
- WideWorldImporters-Standard.bacpac – This is also for the Azure SQL Database Standard tier and it provides a standard edition OLTP sample database in BACPAC format.
To install the WideWorldImporters sample database on a local SQL Server system, you can download the .bak file and then use SSMS or T-SQL RESTORE to restore the backup file as a SQL Server database. You can see the SSMS restore dialog for the WideWorldImporter database in the following figure.
The various BACPAC files can be used with SQL Server on-premises or Azure SQL Server. To import a .bacpac file as a new SQL Server database, you can use the SQL Server Management Studio.
- Click on the Database node and select the Import Data-Tier Application option from the context menu.
- Follow the prompts in the Import Wizard you can see below to import the sample database from the .bacpac file.
Like you might expect, in addition to the relational samples there are also several data warehousing examples.
- WideWorldImportersDW-Full.bak – For the SQL Server 2016 Enterprise edition and higher. It is in the backup file format.
- WideWorldImportersDW-Standard.bak – For the SQL Server 2016 Standard edition and higher. It is in the backup file format.
- WideWorldImportersDW-Fullbacpac – For Azure SQL Database Premium tier. It provides a full featured OLTP sample database in the BACPAC format.
- WideWorldImportersDW-Standard.bacpac – For Azure SQL Database Standard tier. It provides a standard edition OLTP sample database in BACPAC format.
For more details about generating data for the sample database, you can check out the article about WideWorldImporters data generation on Microsoft Learn. There are code samples in the sample–scripts.zip file that contain T-SQL scripts showing how to use different features in this database. Plus, there is a workload–drivers.zip file that contains two programs that can that simulate a workload running against the WideWorldImporters database.
Summary
In this tutorial, you learned about the different Microsoft SQL Server sample databases including Northwind, AdventureWorks, and WideWorldImporters. This guide should help you decide which samples you might want to use for your testing and development, and you can find more information in the Microsoft SQL Server documentation.
Related article: