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

Related Topics:

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 which loads NORTHWND.MDF and NORTHWND.LDF
instpubs.sql which loads PUBS.MDF and PUBS_LOG.LDF

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:


cd C:SQL Server 2000 Sample Databases
sqlcmd -i instnwnd.sql
Changed database context to 'master'.
Changed database context to 'Northwind'.
sqlcmd -i instpubs.sql
Changed database context to 'master'.
Beginning InstPubs.SQL at 03 Jan 200X 19:03:21:470 ....
Creating pubs database....
Changed database context to 'pubs'.
Now at the create table section ....
Now at the create trigger section ...
Now at the inserts to authors ....
Now at the inserts to publishers ....
Now at the inserts to pub_info ....
Now at the inserts to titles ....
Now at the inserts to titleauthor ....
Now at the inserts to stores ....
Now at the inserts to sales ....
Now at the inserts to roysched ....
Now at the inserts to discounts ....
Now at the inserts to jobs ....
Now at the inserts to employee ....
Now at the create index section ....
Now at the create view section ....
Now at the create procedure section ....
Changed database context to 'master'.
Ending InstPubs.SQL at 03 Jan 200X 19:03:30:217 ....

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 FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA

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 FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAPetShop.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

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

Share This

Next

Why I love my web hosting provider
(Cooperative Systems)

Human verify:
Please retype: