Home > Guides > Databases > SQL Server

SQL Server Reference Guide

Buck Woody

Hosted by Buck Woody

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

The SQL Server Sample Databases: NorthWind

Last updated Jul 4, 2008.

We’re in a series that explains the main sample databases that you can use with SQL Server, and in this tutorial I’ll show you how to find, install and work with the second database, Northwind.

NOTE

Starting in SQL Server 2008, Microsoft is no longer including the sample databases on the SQL Server installation media. You have to download them from the “Codeplex” site. In fact, you can get most all of the sample databases there. I’ll include a link to all of that at the end of this article.

The Northwind database was Microsoft’s first attempt at making a unified set of objects available for cross-platform examples. That means Microsoft Access users can start with a database in SQL Server that they are already familiar with, and applications designed to work against this database in Access will work once you point them at SQL Server. This database doesn’t have as many samples in Books Online or on the web as the pubs database does, but that’s OK. I mentioned in this series that I use each sample database for a different reason. I use the pubs database when I want to test out a join, or craft some difficult Transact-SQL statements. I also use it to test the compatibility levels. I do this because the pubs database is small and I understand it well. Of course, that means that more complicated concepts are less useful in this environment — it simply has less of what I need for those constructs.

I use Northwind as another simple database that isn’t too large, but has more complicated relationships and tables in it, so I can use it to explore meta-data. Now, before we even get started on that, I want to be clear about that term. By “meta-data” I mean the internal layout of the tables, views and other objects on the database. I don’t mean the metadata repository, which is a feature that you can use to track things cross-database. We’ll cover that in another tutorial.

So let’s dive in and find out a little more about this sample database. We’ll start with where you can get it and how you install it.

Finding and Installing the Northwind Database

I’ve explained how to install the pubs database in the last tutorial in this series, and the information there holds true here as well. Personally, this is another database I’ve carried so long on my systems that it just gets updated from the last installation. But when I build a new system or I’m at a customer’s site, I need to get it again if they don’t have it installed.

If you have older media like SQL Server 7 lying around, you can get the Northwind database from there, but I normally just go to the web to download it. Microsoft has moved most of its samples for SQL Server to “CodePlex” a web site you can find at the end of this tutorial. For pubs and Northwind, the location is slightly different, since they are older resources. You can find those here.

Once you get to the site, you’ll see the download button. On most systems you can run that, but on my laptop here at home I got a message stating that this package “couldn’t be opened”. If, however, your installation goes fine, you’ll find the files it brings down in the C:\SQL Server 2000 Sample Databases directory created by the installer. In that directory you’ll find a file called instNWnd.sql. Just open SQL Server Management Studio (SSMS) in SQL Server 2005 or higher, or the Query Analyzer (QA) in 2000 and lower, and run that file. It will install everything for you.

You can also install the Northwind database by restoring it from a backup taken on another system. The code for that is quite simple. Sure, you can do this graphically as well by right-clicking the database name and then selecting the All Tasks…| Backup option from the menu, but the code is very simple to type or save. To back up the database to the TEMP directory, I simply type:

USE master;
GO
BACKUP DATABASE pubs TO DISK = ‘c:\temp\Northwind.bak’
WITH INIT;
GO

I use the WITH INIT qualifier to overwrite the file if it is already there. You can have connections while the backup runs. I can then play with the database, and if I make any changes I type this command to get it back:

USE master;
GO
RESTORE DATABASE Northwind FROM DISK = ‘c:\temp\Northwind.bak’
WITH REPLACE;
GO

The WITH REPLACE option overwrites the database that is there, and of course it’s important not to have any connections open to the database when you restore it.

If you’re interested in other ways to install Northwind, see the last tutorial on pubs. The same instructions and information there holds for Northwind as well. You can find the official take on that here.

Also check out the information in that article for the compatibility level and other database options. For my part I normally stick with the SQL Server 2005 compatibility level for this database, since I want to use all of the meta-data discovery commands on this system for 2005.

Now that the database installed, I change the options to what I want and immediately run that backup I mentioned earlier. Now I’m ready for learning, experimenting and testing.

Structure and Contents

The Northwind database is based on a fictional company that imports and sells specialty foods. The database is 4.25 MB on install, has 14 tables, 16 views, 17 stored procedures, and 1 user defined data type.

I found a great detail of the database in addition to the one in SQL Server 2000 Books Online at this site.

Exploring the Meta-Data

In the tutorial on pubs I explained how to find some of the meta-data for the database. Since I’m using this database for that purpose alone, we’ll spend a little more time here. Let’s start with those database diagrams I mentioned in that tutorial.

In SQL Server 2000 and below, you’ll find the database diagram tool in Enterprise Manager. In SQL Server 2005 and higher, you work with database diagrams in SQL Server Management Studio (SSMS). For this tutorial, I’ll stick with SSMS, but the ideas are largely the same between the two tools. The diagrams the tool creates are stored within the system.

To make one, simply navigate to Northwind and click the Database Diagrams node. You might get a message stating that the system needs to create some meta-data to enable the diagrams for the database, and I just answer yes to that. You might also get told that the database doesn’t have an owner — if that happens to you, just navigate to the Security node and add your account as the database owner for the database. If you’re still having issues with that, you probably have an “orphaned” login somewhere — we have an article on that, but make sure you use extreme caution with this process and you’re only doing it on your test system.

When that’s complete, I add all the tables and related tables, and tell the system to create the diagram.

Remember, this tool isn’t standards-compliant. I’ll still run the diagram so that you can see it here, but I normally step up to a tool like ERWin or Visio to create my Entity Relationship Diagrams for this database. Again, this database is small and easily understood, so I’ll run the tool here and create a diagram so that we can check the results against the statements we’re about to run. Here is that diagram:

Figure 1

Figure 1

I won’t cover exploring meta-data through the Enterprise Manager or SQL Server Management Studio interfaces here. It’s pretty intuitive to click the object or right-click it and get the Properties menu. I also won’t cover the 50+ standard reports that come with SQL Server 2005, but you should definitely start there if you’re on that version. These reports are absolutely invaluable for discovering database objects, and come pre-packaged and ready to go.

But I do think even with those graphical helps you should learn about the various commands you have at your disposal to find out about your database. So let’s open that diagram, open a Query Window in SSMS or Query Analyzer, and start taking a look at these commands. I’ll cover the basic commands here — not every meta-data command you have at your disposal. Most of these will run just fine on either version of SQL Server, but a few of them aren’t available in SQL Server 2000. That’s why I’ll show multiple ways to do the same thing most of the time.

We’re concentrating on the database information at this point. I’m not covering the server, replication, full-text indexes and so on. All of those are covered in other places here on InformIT and Safari.

Here are the commands I run on a new system — take a look at each and try them out on your own, verifying it against the graphical diagram:

Scope

Command

Comments

Database

SELECT *

FROM MASTER..sysdatabases;

GO

Shows all of the databases on a system. For SQL Server 2005, you should use sys.sysdatabases

Database

EXEC sp_helpdb 'NorthWind';

GO

Great general stored procedure to show lots of database-specific information. You can also run it without the database name

Backups

SELECT SUBSTRING(s.name,1,40) AS 'Database',

CAST(b.backup_start_date AS CHAR(11)) AS 'Last Backed up on any system',

CASE WHEN b.backup_start_date > DATEADD(dd,-1,GETDATE())

THEN 'Backup is current within one day'

WHEN b.backup_start_date > DATEADD(dd,-7,GETDATE())

THEN 'Backup is current within one week'

ELSE 'No backup recorded on this system for this database for over a week'

END

AS 'Status'

FROM MASTER..sysdatabases s

LEFT OUTER JOIN msdb..backupset b

ON s.name = b.database_name

AND b.backup_start_date = (

SELECT MAX(backup_start_date)

FROM msdb..backupset

WHERE database_name = b.database_name

AND TYPE = 'D') -- full database backups only, not log backups

WHERE s.name <> 'tempdb'

ORDER BY s.name;

GO

Absolutely essential to run on any system when you get it. You can fiddle with the dates if you want to set up the length of time you want to monitor

Devices

EXEC sp_helpdevice;

GO

Shows devices for the system

Devices

SELECT 'Name' = name,

'VDevNo' = CONVERT(tinyint, SUBSTRING(CONVERT(BINARY(4), low), 1,1)),

'Size (MB)' = (high - low + 1)*2/1024

FROM sys.sysdevices;

GO

For SQL Server 2005. Shows sizes

All objects

SELECT * FROM

sysobjects;

GO

For SQL Server 2005, use sys.sysobjects

All Objects

EXEC sp_help 'object name'

GO

Replace the Object Name with something that you find in the last statement – this procedure will show you all the info about it

Tables

EXEC sp_tables;

GO

Shows info on all your tables in a database

Tables

SELECT *

FROM information_schema.tables;

GO

A more compatible way to show some information about your tables

Tables

SELECT *

FROM sys.tables;

GO

For SQL Server 2005

Columns

SELECT *

FROM syscolumns;

GO

SQL Server 2005 should use sys.columns

Columns

EXEC sp_columns 'tablename'

GO

Lots of info on your columns for a given table

Keys

EXEC sp_fkeys 'tablename'

GO

Shows Foreign Keys for given table

Keys

EXEC sp_pkeys 'tablename'

GO

Shows Primary Keys for a given table

Keys

SELECT *

FROM sysdepends;

GO

Base table for the dependencies on a system – you’ll have to link the Objet ID’s manually and for SQL Server 2005, use sys.sysdepends

Views

SELECT *

FROM INFORMATION_SCHEMA.VIEWS;

GO

Shows the views for a database

Views

SELECT *

FROM sys.views;

GO

For SQL Server 2005

Views

EXEC sp_helptext 'View Name';

GO

Shows the statement used to create the view – great learning tool

Stored Procedures

EXEC sp_stored_procedures;

GO

Shows the Stored Procedures in the database

Stored Procedures

SELECT *

FROM INFORMATION_SCHEMA.PARAMETERS;

GO

Shows the Stored Procedures with a nice layout of the parameters

Stored Procedures

SELECT *

FROM sys.procedures;

GO

For SQL Server 2005

Stored Procedures

EXEC sp_helptext 'Stored Procedure Name';

GO

Shows the statements that make up the Stored Procedures – great learning tool

Functions

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES;

GO

Shows both Stored Procedures and Functions

Triggers

SELECT *

FROM sys.triggers;

GO

For SQL Server 2005

Indexes

EXEC sp_indexes 'tablename';

GO

Shows the indexes for a given table

Indexes

EXEC sp_helpindex 'tablename';

GO

More info for a given index

Security

EXEC sp_helpgroup;

GO

Lists the groups (database roles) for a database

Security

EXEC sp_helprolemember;

GO

Shows the role membership in the database

Security

EXEC sp_helpuser;

GO

Shows the users in a database – you can also type in a user name to show just one user’s information

Security

EXEC sp_helprotect 'objectname';

GO

Shows permissions for a given object – great thing to script out periodically

Security

EXEC sp_table_privileges 'Table Name';

GO

Table Permissions for a given table

Security

EXEC sp_column_privileges 'Table Name';

GO

Column Permissions for a given table

We’ll use these processes — and more — to find out more about our next few sample databases as well.

InformIT Articles and Sample Chapters

An oldie but a goodie – this sample chapter from all the way back in SQL Server 7.0 talks about restoring databases.

Books and eBooks

Using Excel Visual Basic for Applications is a great book that uses pubs to teach you about Visual Basic for Applications.

To search for more info yourself, check out the search engine on Safari, the online book resource here at InformIT.

You can search for print books here on InformIT as well.

Online Resources

This is the link for the SQL Server 2000 sample databases (pubs and Northwind).

This is the link for the SQL Server 2005 sample databases (all the AdventureWorks flavors).

This is the link for the SQL Server 2008 sample databases.

Digg Digg Del.icio.us Del.icio.us Save to Your AccountSave To Your Account

Related Resources

There are currently no related podcasts. Please check back later.

Rachel BaylessInformIT's 17 Days of Giveaways
By Rachel BaylessJuly 3, 2012Comments

Get ready for a month full of giveaways. From July 9 through the end of the month, InformIT will be having 17 days of giveaways. Each week has a theme to make sure that there’s something YOU will be excited to win!

Buck WoodyCareful with those NULLs
By Buck WoodyOctober 13, 2010Comments

Many folks (including me) use NULL values in their databases. There's actually a bit of controversy on even having them - but I don't fall on the side of never using the. But you do need to exercise some care...

Buck WoodyThe true value of conferences
By Buck WoodyOctober 5, 2010Comments

I recently returned from the "SQLBits" conference in York, England. I met a lot of folks that I've seen before at other conferences, but I also met a fair amount that had not been to a SQL Server conference before.

See More Blogs

Cloud Computing Reference Architecture: Data Ingress and Storage Access
By Buck Woody
Administering Storage, I/O, and Partitioning in Microsoft SQL Server 2012
By Ross Mistry
Synchronization with Microsoft SQL Server Analysis Services
By Baya Dewald

See All Related Articles

Microsoft SQL Server 2012 Management and Administration, 2nd EditionMicrosoft SQL Server 2012 Management and Administration, 2nd Edition
By Ross Mistry, Shirmattie Seenarine
Physical Product$39.99
Microsoft SQL Server 2012 Management and Administration, 2nd EditionMicrosoft SQL Server 2012 Management and Administration, 2nd Edition
By Ross Mistry, Shirmattie Seenarine
eBook Multiformat$31.99
Essential App Engine: Building High-Performance Java Apps with Google App EngineEssential App Engine: Building High-Performance Java Apps with Google App Engine
By Adriaan de Jonge
Physical Product$31.99

See All Products