Northwind Sample Database

This article analyzes the Northwind sample database first offered with Microsoft SQL Server 2000.

Site Map
About this site

Screen: Max • 1024 • 800

Northwind and Pubs Samples

Many books and tutorials on SQL Server refer to the NorthWind (and its PUBS) sample database.

There are several versions of Northwind.

Download Micorosoft's Access 2000 Northwind.mdb The extracts file Nwind.exe which extracts to 1,510 KB Nwind.mdb, which is convert to Nwind2002.mdb when opened using MS-Access 2003 or 2007.

Download from Microsoft SQL2000SampleDb.msi for SQL Server 2000 (1.5MB file dated 4/8/2004). This is not available from CodePlex. Running this results only in the creation of folder
"C:\SQL Server 2000 Sample Databases" containing

instnwnd.sql

instpubs.sql

The Access .MDB version has a smaller number of tables than the SQL Server version, but the same number rows for the same tables.

Loading Northwind Into SQL 2008

To load the data in SQL 2005 and 2008 (instead of the osql command in SQL 2000), open a command windows and run this against the default SQL server instance:

sqlcmd -i instnwnd.sql

sqlcmd -i instpubs.sql

Pinal Dava offers other ways to install Northwind on SQL 2005, which a reader said also works for 2008.

To interactively make SQL Server a file using Enterprise Manager, copy the mdf and ldf file into a folder like:
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

In EM 2005, Database and choose all task and select Attach DB

In the Enterprise Manager 2008 Object Explorer, right Click on Databases, then Attach... If you have a .ldf file with the .mdf file, rather than use sp_attach_db procedure (especially from unknown or untrusted sources), use T-SQL command

CREATE DATABASE ON 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PetShop.mdf' FOR ATTACH

If you don't have an .ldf file, this article describes use of
sp_attach_single_file_db @dbname= 'databasename', @physname= 'C:\...\PetShop.mdf'

Northwind Physical Schema

There are 8 data tables in the NorthWind MDB database.
There are 13 data tables in the NorthWind SQL database.
Click on any of the table names for a data dictionary page generated using CodeSmith. The other columns are KB numbers, generated using SQL Server's EXEC sp_spaceused reporting of the sysindexes table (after running DBCC UPDATEUSAGE(0)).

Table MDB? Rows Reserved data used index_size unused

Categories Y 8 168 112 24 32

CustomerCustomerDemo - 0 0 0 0 0

CustomerDemographics - 0 0 0 0 0

Customers Y 91 104 24 80 0

Employees Y 9 312 232 40 40

EmployeeTerritories - 49 32 8 24 0

Order Details Y 2155 312 72 192 48

Orders Y 830 504 160 320 24

Region - 4 32 8 24 0

Products Y 77 96 8 88 0

Shippers Y 3 16 8 8 0

Suppliers Y 29 64 24 40 0

Territories - 53 32 8 24 0

The above was run on SQL 2008 on a 64-bit machine. Notice the doubling of space versus the
space reported for the same query run on a SQL 2005 on a 32-bit machine

Northwind Unplugged

Click to pop-up a full-size image of this Northwind SQL physical schema diagram:

Click to pop-up a full-size image of this Nwind MDB schema diagram:

NWind MDB Views

This matrix organizes the different views:

Slant Customers Suppliers Products Orders Shippers Employees

list Customer List - Current Product List Order Details Extended - -

Top 5 - - - - - -

by Year - - - Sales by Year - -

Current Year - - Product Sales for 1995 Category Sales for 1995 - -

by Quarter - - Quarterly Orders by Product Quarterly Orders - -

by Category - - - Sales by Category - -

by Country - - - - - Employee Sales by Country

by City Customers and Suppliers by City - - - -

by money - - Most Expensive Products
Products Above Average Price - - -

summary Invoices - Product Sales for 1995 Order Subtotals - -

Pubs Schema

This was obtained by running EXEC sp_spaceused 'author' within SQL 2008 on a 32-bit machine.

Table Rows Reserved data used index_size unused

authors 23 32 8 24 0

discounts 3 16 8 8 0

employee 43 32 8 24 0

jobs 14 16 8 8 0

pub_info 8 128 120 8 0

publishers 8 16 8 8 0

roysched 86 32 8 24 0

sales 21 32 8 24 0

stores 6 16 8 8 0

titleauthor 25 48 8 40 0

titles 18 32 8 24 0

Application Generation

CodeSmith 4 generated the Admin application shown from the Northwind database using .NetTiers 2.0 in the back-end on SQL Server 2005, based on Microsoft's Enterprise Library of reusable software components (application blocks) open source code addressing challenges common to complex, enterprise-level line-of-business development projects.
Correspondence of .NET, VS, and Entlib