Northwind vs. AdventureWorks as a Sample Database

Microsoft "strongly urges" book authors to abandon Northwind as the default sample database in favor of SQL Server (SQLS) 2005's updated AdventureWorks (AW) OLTP database. AW has a very complex, highly normalized structure of 68 tables. The Visio 2003 and HTML versions of an AW database diagram require printing to at least a 17-inch x 22-inch sheet to be readable. AW uses SQLS 2005's new user-schema separation feature, which lets you substitute an arbitrary prefix for the traditional database owner's name (dbo by default). Thus the fully-qualified name changes from SQLS 2000 and earlier's ServerName.DatabaseName.OwnerName.TableName to ServerName.DatabaseName.SchemaName.TableName. The SchemaName lets multiple users own a single schema based on their membership in roles or Windows groups. AW has five schemas: HumanResources, Person, Production, Purchasing, and Sales.

Note: An Accounting schema and Accounting.Invoices table are conspicuous by their absence. Apparently, the firm accepts and fulfills orders but doesn't issue invoices or charge credit cards for goods shipped. Northwind also lacks an Invoices table.)

The Person.AddressType table has six types of addresses: Billing, Home, Main Office, Primary, Shipping, and Archive. The table contains foreign-key values for StateProvince and CountryRegion tables. Thus a T-SQL query to return all columns for an individual customer address is (from the Sales.vIndividual view):

SELECT i.[CustomerID], c.[Title], c.[FirstName], c.[MiddleName], c.[LastName], c.[Suffix], c.[Phone], c.[EmailAddress], c.EmailPromotion], a.[AddressLine1], a.[AddressLine2], a.[City], StateProvinceName = sp.[Name], a.[PostalCode], CountryRegionName = cr.[Name], i.[Demographics], c.[AdditionalContactInfo]

FROM [Sales].[Individual] i INNER JOIN [Person].[Contact] c ON c.[ContactID] = i.[ContactID] INNER JOIN [Sales].[CustomerAddress] ca ON ca.[CustomerID] = i.[CustomerID] INNER JOIN [Person].[Address] a ON a.[AddressID] = ca.[AddressID] INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID] INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]

WHERE i.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID] FROM [Sales].[Customer] WHERE [Sales].[Customer].[CustomerType] = 'I')

Creating (or attempting to create) an updatable typed DataSet for individual customers requires six DataTables and generates a DataSet designer file of more than 6,000 lines. I haven't yet attempted to write FillBy... methods for a parameterized details form that would enable updates and inserts with bound text boxes and dropdown lists.

Following is a simple DataGridView of the Sales.vIndividual view. Click the thumbnail to open it in a resizable window.

Without a parameterized FillBy... method, the Fill method returns 18,508 records and populates the lookup lists. Thus, it takes about 10 seconds to open the release version of AppName.exe with the client app and SQLS 2005 instance running under Windows 2003 Server on a 2.6-GHz Pentium 4 box with 1 GB RAM. TaskMan shows that the AppName.exe process consumes 101 MB. Parameterizing the seven SELECT queries with individual FillBy... methods can solve the opening time and resource-consumption problems but doesn't address the complexity issue.

Northwind's dbo.Customers table provides readable BillTo addresses, and the dbo.Orders table supplies ShipTo information without requiring a series of INNER JOINs. It's my contention that AW's complexity will distract readers when learning typical tasks, such as creating a Customers-Orders-LineItems master-details-subdetails form. Thus, I'm using old-timey Northwind for most design and coding examples in my book. Another reason for using Northwind is it's popularity in VS 2005's offline help files. Searching the February CTP with 'Northwind and Walkthrough' returns 62 hits; 'AdventureWorks and Walkthrough' returns 3. Similarly, 'Northwind and HOWTO' returns 47 hits; 'AdventureWorks and HOWTO' returns 0. If the SQLS team "strongly urge[d]" the User Ed(ucation) folks who write the VS 2005 help files to use AW, the recommendation appears to have fallen on deaf ears.

Note: One of the book's sample projects lets readers populate a Northwind.SalesOrders table—an updated clone of the Northwind.Orders table—with an unlimited number of rows containg randomized data for sequential dates, a specified average number of line items per order, and random ProductID values.