The AdventureWorks SQL Server Sample Database

Access developers are likely familiar with the Northwind Access template and its much improved contemporaries, Northwind 2.0 Developer and Starter editions:

Northwind 2.0: The Access Database Template You’ve Been Waiting For

It only took 30 years, but Microsoft Access finally has a set of templates showcasing the best of what Access and its community of experts have to offer.

No Longer SetMike Wolfe

The SQL Server equivalent to Northwind is the AdventureWorks sample database:

The AdventureWorks databases are sample databases that were originally published by Microsoft to show how to design a SQL Server database using SQL Server 2008.

Database design has progressed since AdventureWorks was first published. For a sample database leveraging more recent features of SQL Server, see WideWorldImporters.

Note that AdventureWorks has not seen any significant changes since the 2012 version. The only differences between the various versions of AdventureWorks are the name of the database and the database compatibility level.

The nice thing about a sample database that doesn't change much is that I can write code and scripts against the sample and not have to constantly update them to keep up with changes to the sample database schema.

Going forward, I plan on using this sample database for many of my future articles.

Installing the AdventureWorks Database

Note that the steps below are derived from the AdventureWorks Github project Readme file as of September 8, 2023.  The prerequisites and install steps may change in the future.  I've included them here to hopefully streamline the setup process for you.

Prerequisites

You need the following software, features, and settings configured to run the AdventureWorks database.

1. SQL Server Instance

Since we will be using this for testing and development (and not production use), we can use the free Developer Edition of SQL Server.  This version has pretty much every feature available in the top-of-the-line Enterprise editions, but it can't be used in production.

2. Install SQL Server Management Studio

There may be an option to install this as part of your SQL Server installer.  If not, a quick Google search will get you what you need here.

3. Full-Text Search Feature is Installed

Be sure to include this feature if you are installing a new instance of SQL Server.

If, like me, you installed SQL Server awhile ago and you can't remember whether you installed the Full-Text Search feature, you can run the following T-SQL query to check whether the feature is installed in your instance of SQL Server (source):

SELECT 
CASE FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')
    WHEN 1 THEN 'Full-Text installed.' 
    ELSE 'Full-Text is NOT installed.' 
END;

4. FILESTREAM Feature is Installed

Follow the documentation to enable and configure FILESTREAM:

1. Open SQL Server Configuration Manager
2. Click on "SQL Server Services"
3. Right-click on you SQL Server instance and choose "Properties"
4. Go to the FILESTREAM tab
5. Check box to "[√] Enable FILESTREAM for Transact-SQL access"
6. Click [OK]

Installation Steps

1. Download and extract the .zip file to C:\Samples\AdventureWorks

While you can extract them to a different folder, that requires making changes at the top of the .sql script.  You're welcome to do that, but to keep things simple for this tutorial, I'm going to stick with the default paths:

2. Open instawdb.sql in SSMS

1. In SSMS, go to File > Open > File... or press Ctrl + O
2. Browse to C:\Samples\AdventureWorks\instawdb.sql

Review the notes at the top of the script in case they have changed.  If you've followed the steps above, the only thing you should need to do is enable SQLCMD mode (see step 3 below screenshot):

3. Enable SQLCMD Mode

From SSMS, go to Query > SQLCMD Mode.

Make sure the  icon is highlighted to show that the feature is enabled:

4. Execute the Script

Press F5 or click the [ ▶ Execute ] button to execute the script.

The script ran for about 20 seconds on my relatively high-performance desktop computer.  Here are screenshots of the beginning and end of the output from the Messages tab in SSMS:

5. Verify the AdventureWorks Database was Created

If all went well, you should see AdventureWorks listed as one of your databases in SSMS.  Here's a sample of what the Tables collection looks like:

UPDATE [2023-09-12]: Remove duplicate (and erroneous) entry for enabling SQLCMD mode as a prerequisite.  SQLCMD mode appears to be a per-query setting and not a per-instance setting.