Six sample databases for SQL Server and how to find them
SQL Server sample databases are useful for test and dev, but they can be difficult to parse. Use this SQL database sample overview to decide which to use and how to access them.
It used to be that finding and using a sample SQL database was quite simple. In fact, the venerable Pubs database used to be installed as part of the SQL Server installation process. Fast forward to 2018 and it's quite a different story.
Sample databases have not been installed as part of the SQL Server installation process since SQL Server 2005 and, over the years, Microsoft has developed a number of different sample databases for SQL Server. With so many options, it can be tricky to determine which sample to use for which database. Use this overview of the six main sample databases for SQL Server to determine which ones to use and how to access them.
Pubs
Pubs is the original sample database included with SQL Server. It was originally developed by Sybase and came to SQL Server as part of the Microsoft-Sybase partnership. The Pubs sample database is based on a publishing company that tracks books, authors and sales.
Pubs was included with SQL Server until the SQL Server 2005 release, and it was originally installed as part of the SQL Server installation process.
As time passed, the Pubs database was considered too small and too simple to be a good sample. Plus, many people felt the example of a fictitious book publishing company did not accurately reflect the database needs of real customers.
Northwind
Moving toward a more realistic sample database for SQL Server, Microsoft adopted the Northwind database. Northwind was originally developed by the Access team, and Microsoft included it with the SQL Server 2000 release. The Northwind database is based on a food import and export company called Northwind Traders.
Over the years, Microsoft has developed a number of different sample databases for SQL Server.
Like Pubs, Northwind was removed from SQL Server with the SQL Server 2005 release.
The Pubs and Northwind databases are still available for download for SQL Server 2000. Running the installation file extracts the database scripts into the default folder: C:\SQL Server 2000 Sample Databases. Running the T-SQL scripts from SQL Server Management Studio or SQLCMD will create and populate the Pubs and Northwind example databases. The scripts have a few minor incompatibilities with the recent SQL Server versions, but can be easily fixed.
AdventureWorks
Beginning with SQL Server 2005, the AdventureWorks sample database replaced Pubs and Northwind. AdventureWorks was intended to be a bigger, more realistic sample database that could be used to demonstrate SQL Server's Power BI capabilities and new data types. It has a highly normalized structure and uses the SQL Server user-schema naming system that was introduced with SQL Server 2005.
AdventureWorks evolved into a collection of different databases, but the primary versions are:
AdventureWorks -- a larger and more complex online transaction processing (OLTP) sample database;
AdventureWorksLT -- a smaller and simpler OLTP sample;
AdventureWorksDW -- a data warehousing sample; and
AdventureWorksAS -- an analysis services cube sample based on AdventureWorksDW.
A new version of the AdventureWorks database has been released for each release of SQL Server since 2005. However, the AdventureWorks schema and data have not significantly changed since the AdventureWorks2012 release.
AdventureWorks and other SQL Server samples were formerly found in CodePlex, but CodePlex has been archived and has since moved to GitHub, which Microsoft acquired in June 2018. You can find versions of AdventureWorks2008R2 through AdventureWorks2017 on GitHub with the AdventureWorks sample databases.
WideWorldImporters
With the release of SQL Server 2016, Microsoft developed the new WideWorldImporters database example. WideWorldImporters is designed to demonstrate how the SQL Server 2016 feature set can be used in a realistic database. It includes samples of temporal tables, in-memory OLTP, the columnstore index, Row-Level Security, Dynamic Data Masking and more.
The database is for a fictional wholesale novelty goods importer and distributor operating out of a San Francisco called Wide World Importers. There are four main versions of the WideWorldImporters database:
WideWorldImporters-Full: The main OLTP database. It uses the features only available in SQL Server 2016 Enterprise Edition.
WideWorldImporters-Standard: The main OLTP database. It only uses the features that are supported by SQL Server 2016 Standard Edition.
WideWorldImportersDW-Full: The main online transaction processing (OLAP) data warehousing database. It is derived from the OLTP database, but the scheme is optimized for BI. It uses the features that are only available in SQL Server 2016 Enterprise Edition.
WideWorldImportersDW-Standard: The main OLAP data warehousing database. It is derived from the OLTP database, but the scheme is optimized for BI. It only uses the features that are supported by SQL Server 2016 Standard Edition.
You can download the new WideWorldImporters sample database on GitHub, and full backups are available for download, as are .bacpac files.
In addition to the database, there is a SQL Server Integration Services package that can load the data warehousing databases from the relational database. There is a sample-scripts.zip file that contains T-SQL scripts showing how to use the different SQL Server 2016 features in the WideWorldImporters sample databases. Plus, there is a workload-drivers.zip file that contains two workload drivers that can simulate a workload running against the WideWorldImporters database.
The data in WideWorldImporters ranges from Jan. 1, 2013, to May 31, 2016. However, you have the option to load more data up to the current date.
Contoso
While Pubs, Northwind, AdventureWorks and WideWorldImporters are the main sample databases for SQL Server, the most notable sample database from Microsoft is the Contoso sample database; Contoso is a fictional company often used as an example for Microsoft domains.
The Contoso BI Demo data set has a prebuilt star schema that is used to demonstrate data warehouse/business intelligence functionalities for SQL Server and Microsoft Office. You can get Contoso from the Microsoft Contoso BI Demo Dataset for Retail Industry website. There is also a version available for Azure on GitHub.
Stack Overflow
Additionally, a non-Microsoft sample database for SQL Server that has been used fairly widely is Stack Overflow. Stack Overflow is a popular Q&A site that periodically produces downloads of their SQL Server-based database. Unlike some of the Microsoft samples, this database is fairly easy to understand and use.
The simplest way to get this database is by following the directions at SQL expert Brent Ozar's site, where you can directly download the 1 GB sample, or you can use the BitTorrent instructions to download the full 19 GB database.
