Looking for some OLD SQL Server sample databases?

Back in the day, I’m talking up to SQL Server 2000, SQL Server installed two databases by default: Pubs and Northwind.  Then they went away.  Well, you can still download them from Microsoft’s Technet website.  You get a tiny little 1.5 meg MSI package, when you run it a directory called C:\SQL Server 2000 Sample Databases will be created that contains two MDF and LDF files for the two databases, an HTML ReadMe, and two .SQL files that are complete build scripts including populating the tables with data: the scripts have a copyright date of 1994-2000!

And they run under SQL Server 2014: I know because I just ran them and created the two databases.  I thought it would be more interesting to run the installer scripts rather than trying to attach old databases, though I may try it tomorrow to see if SQL 2014 can attach older databases like these.

It’s kind of amusing seeing things like the use of quoted identifiers and views with spaces in the name, I’d be include to take a ClueBy4 to the person who created such a monstrosity.  But they are valuable for study just to see how other people design databases.  Pubs shows creating user data types before creating tables so they can use the UDT in the table definition, which is interesting even if it isn’t really a data dictionary.  It also shows some bad coding techniques, such as having a varchar(30) field for Country rather than normalizing it in to a lookup table: that was a normalization violation even back in the mid ’90s.

The design of Northwind does not escape criticism.  Again, Country is stored directly in the Suppliers table rather than a lookup, but here’s the interesting bit: Country and the Phone and Fax numbers are stored in nvarchar rather than varchar.  As far as I’ve seen, country names and phone/fax numbers don’t contain non-Latin character set characters.  My guess is that they went with nvarchar over varchar because the rest of the table uses nvarchar.  As far as I can see, the only accented character is in Québec, which you can store in regular varchar.  I don’t know if it is an international postal regulation that country/city names be in Latinate character sets, so it’s just a little oddity.

Their views are interesting.  Overall, they tend to be fairly basic selects, but two things pop out.  First, the occasional use of SELECT *.  That’s a definite no-no.  Also, they don’t use table aliases in views!  I couldn’t believe the following example from the Northwind script:

create view Invoices AS
 SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,
 Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City,
 Customers.Region, Customers.PostalCode, Customers.Country,
 (FirstName + ' ' + LastName) AS Salesperson,
 Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName,
 "Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity,
 "Order Details".Discount,
 (CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight
 FROM Shippers INNER JOIN
 (Products INNER JOIN
 (
 (Employees INNER JOIN
 (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)
 ON Employees.EmployeeID = Orders.EmployeeID)
 INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
 ON Products.ProductID = "Order Details".ProductID)
 ON Shippers.ShipperID = Orders.ShipVia
 GO

It was indented, that was lost and I’m not inclined to work through that mess.  So you have both quoted identifiers (and double-quotes, rather than single-quotes), and spaces in object names — both tables and views!  The lack of aliasing greatly increases the reading difficulty.

My brain hurts just looking at that!  I mean, I’ve seen some pretty horrible stuff perpetrated in Access in my days, but oddly I haven’t had to deal with a lot of T-SQL code written by other people.  But this?  Wow.

The Pubs script had this gem of a view:

CREATE VIEW titleview
AS
select title, au_ord, au_lname, price, ytd_sales, pub_id
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
 AND titles.title_id = titleauthor.title_id
GO

So not only do we have no table aliasing, we have an example of deprecated join syntax.  In the first days of T-SQL, we used *= and =* for LEFT and RIGHT JOINs.  At least the Northwind example was using JOIN syntax, even if it wasn’t aliasing.  To be fair, the only sin was not using table aliasing, but I’m not going to give them a pass on that because the book A Guide To Sybase and SQL Server by D. McGoveran with C.J. Date, as in Chris Date, one of the foundational minds of relational database, was published in 1992 and demonstrates alias syntax!  There’s no excuse for them not using table aliases, this book was fundamental and one of the very first good books on learning SQL Server when it first appeared under the Microsoft label.

The big question is why — why would I load sample databases that are so old?  It’s easy enough to load Adventureworks, and I have.  The reason is simple: I’m using Access 2010 to do development, and the Help VBA samples are all using Pubs.  Loading that old database will make it easier to use the sample code to try and figure out some things that I’m weak on.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s