I always look through the SQL Server 2005 samples with each new incantation, and always seem to find things in there that are new and interesting. In the SP2 version, the samples included a new sample database, “AdventureWorks Light”. Also known as AdventureWorksLT.

Because folks that teach SQL Server do have a bunch of canned queries for exposition and know exactly how those queries are supposed to behave, sample databases really never disappear. Pubs was the sample database inherited from Sybase, although Sybase itself moved on to “Pubs2″ last time I looked. The problem with Pubs was that it really didn’t have much data at all. I always point out that, of the 8 publishers in Pubs, only 3 have actually published books. Northwind was inherited from Microsoft Access, being the original Access sample database. It had more data, but not a lot.

In SQL Server 2005, no sample databases are installed with the product by default, which is a good thing. But the “AdventureWorks series” is the new sample database, including an OLTP, data warehouse, and analysis databases, as well as case-senstive and case-insensitive collation versions. It has a reasonable amount of data and its design is said to reflect current thoughts on best practices. It is built around multiple departments in a putative organization, including Sales, HR, and Manufacturing. This allows it to include a employee/manager hierarchy and a bill-of-materials table.

But the Adventure Works database is a nice 3NF database, with lots of tables (~70) and it sometimes difficult to use for exposition, because of the long multipart object names and lack of familiarity. AdventureWorks Light is a bit more approachable, weighing in at about 3MB, but still over available as an MSI (no simple create script). It contains:

10 tables and 3 views
500 or so rows in each table
All in single object schema SalesLT
No stored procs, but 1 scalar and 2 table-valued functions
A single XML schema collection

It even includes a version of Sara Tahir’s uspPrinterror and uspLogerror procedures for error handling, but interestingly uspRethrowError is left out.

We’ll see if this new sample database is simple enough for widespread pedagogical use. At least it may get folks more use to using (memorizing) the “AdventureWorks family” table and column names.

PS: The SQL Server Samples now have their own Codeplex project at http://codeplex.com/SQLServerSamples