Jaa

Revisited: The Story of Pubs, Northwind, and AdventureWorks

  • Artikkeli

Matt Stroshane has recently taken down his Phone to Cloud blog (sad to see that), but he asked me to republish this, his most popular post, for posterity sake, which I am glad to do.

On a personal note of reflection, I had just arrived in SQL Server and at Microsoft after we shipped SQL Server 2000 (code named “Shiloh”). We were hard at work on “Yukon,” which would ship as SQL Server 2005, and I was privileged to get to sit in on schema definition meetings for AdventureWorks (mostly the Sales schema) with such SQL Server luminaries as Tom Casey, who was running the Replication team at the time, and long time colleague Alan Brewer...ah, nostalgia.

Anyway, without further ado, here’s the original post by Matt Stroshane….

The Story of Pubs, Northwind, and AdventureWorks

If you’ve read SQL Server documentation, chances are, the names Pubs, Northwind, and AdventureWorks sound familiar. These are the names of sample databases that have shipped with Microsoft SQL Server (and other products) over the last decade. In fact, one of these databases, Northwind, has even made it to the cloud.

While writing another post that uses the Northwind OData service, I began writing a bit of background about the Northwind database – I thought that I had a firm grasp on the Northwind story. However, upon clarifying a few details, I quickly found that I had the story all wrong.

This post is what I could piece together from the halls of the SQL Server team…

Enter the Sample Database

In the documentation world, sample databases have been around a long time. Just as programming documentation needs code samples, database documentation needs database samples. They are the vehicle on which to demonstrate the new features of database software.

A good sample database is not too big and not too small. It demonstrates new features and does not contain any personally identifiable information. Within the scope of a small team with a fairly basic product, that may seem pretty straightforward. Add ten years of innovation and multiple organizations within a large company, and you have the story of Pubs, Northwind, and AdventureWorks.

Pubs

For roughly the first ten years of SQL Server, when documentation was still printed on paper—a lot of paper— the Pubs database was the database for SQL Server documentation. Pubs was originally developed by Sybase and came to Microsoft in the early 90s via the Microsoft-Sybase partnership.

Although this database served its purpose for documentation for many years, it wasn’t free of complaints. People thought that Pubs was too simple and that the database needs of a book publishing company had nothing to do with the database needs of real SQL Server customers. After almost ten years in the market, it was time for something different.

Northwind

While developing SQL Server 2000, the SQL Server team learned that a PM from the Access team had created a Transact-SQL script from their new sample database, a database named Northwind. By using this newly discovered Transact-SQL script, Northwind could be created in SQL Server too.

Northwind was a good database for SQL Server – a better fit than Pubs. By also appearing in the Access documentation, it would help show customers how Access and SQL Server could accomplish many of the same tasks. To illustrate new SQL Server features, the team was also free to make additive changes to the database. The summation of that work is the Northwind database script that shipped with SQL Server 2000.

By the time SQL Server 2000 released, much of the SQL Server documentation still referenced Pubs. This is why both databases shipped with the product.  

Already planning the features for SQL-next, the business intelligence (BI) teams were quickly disappointed with the lack of size and complexity in Pubs and Northwind. To showcase the features of Integration Services and Analysis Services, a “real world” sample database would be required.

AdventureWorks

SQL Server 2005 was a transformational release for everyone, including the documentation team. It was then that a project formed to build the next sample database, AdventureWorks.

AdventureWorks was a tremendous undertaking. The data mining team needed trending data and the legal department needed fictitious data. All of the data had to be carefully reverse engineered to have the right trends. At the same time, all of the thousands of pages of SQL Server documentation were updated to reference AdventureWorks instead of Pubs or Northwind.

AdventureWorks evolved into a family of databases and is maintained to this day for SQL Server documentation and hosted on CodePlex. The most recent is version is AdventureWorks for SQL Server 2012 .

The Pubs and Northwind Legacy

When AdventureWorks was being created, Pubs and Northwind were already being used by other teams. Not everyone wanted to go through the effort of changing their documentation to reference this new database.

Back when Northwind came out, the Visual Studio team began using it in their documentation. They felt that AdventureWorks was too big and complicated for their needs, so they didn’t adopt it. As a result, some Visual Studio documentation still refers to Northwind. You may find other teams still using Pubs too.

Although no longer used by the SQL Server team, Pubs and Northwind live on in the Microsoft Download Center for our partner teams. You can find it under Northwind and Pubs Sample Databases for SQL Server 2000 .

Northwind in the Cloud

This story ends where my other post begins, the Northwind OData Service located at:

https://services.odata.org/Northwind/Northwind.svc/

This implementation exposes the Northwind database via the Open Data Protocol (OData). It was built using WCF Data Services, Entity Framework 4.0, and the same Northwind database script that is still available in the Download Center. You can build one too. Step-by-step directions are located in the WCF Data Services Quickstart .

The Northwind OData service was most recently featured in the Windows Phone documentation titled How to: Consume an OData Service for Windows Phone

One final note, as I mentioned before, the published version of the Northwind sample database no longer can be attached in SQL Server 2012 and later versions of the server. If you still want to use Northwind (I used it in all of the OData documentation, and I still leverage it heavily in my blog posts), you can try the steps in Installing the Northwind Sample Database. This never worked for me, due to APIs in the script that have been deprecated. I published an updated script that worked for me in my previous post: Running WCF Data Services on Windows 8 Consumer Preview: Part 1.

 

Cheers,

Glenn Gailey

Comments

  • Anonymous
    September 15, 2013
    Where does "Contoso" fit in the history/family?

  • Anonymous
    September 17, 2013
    Thanks for the heads-up on Contoso. I'm not actually familiar with this sample database (it was not used/designed by the SQL Server team), but I'll poke around and see what I can find out.