Thursday, May 08, 2008

Upgraded Northwind.sdf File for SSCE v3.5 Available for Download

Updated 5/18/2008: See below.

Microsoft India's Northwind.sdf sample database file for SQL Server Compact [Edition] (SSCE) v3.5 that ships with Visual Studio 2008 has a defect: Its column names and two foreign-key constraints (Order DetailsFK00 and Order DetailsFK01) contain spaces. As far as I can determine, this is the first sample database from Microsoft since Access 2.0's Northwind.mdb to have spaces in column names.

It might be acceptable to include spaces in column and constraint names in Bangalore but it's not considered a good database design practice in western countries. For example, when you use the original Northwind.sdf sample database in an ADO.NET Entity Framework project, the two Order DetailsFK0# constraints each raise 11 compile-time errors for the Northwind.ssdl file.

Update 5/11/2008: The compile-time errors aren't fatal and disappear when you compile and run the project. I call these "ghost errors."

Update 5/15/2008: In his New Entity Framework designer bugs in SP1 beta of May 14, 2008, Ryan Hauert finds the following bug most frustrating:

The Error List window now lists fake errors for all of our subtypes that are children of abstract entities with a message similar to “Entity types B, C, D are mapped to table A without discriminating conditions on all mappings to the table.”

I'm also seeing "ghost" or "fake" errors when creating sample models from plain old Northwind (PONW) tables with ADO.NET Entity Framework SP1 Beta.

Update 5/16/2008: VS 2008 SP1 Beta appears to solve the "ghost error" problem with spaces in FKConstraint names. My problem with SP1 showing 27 "ghost errors" for a simple EDM from Northwind via SQL Server Express doesn't reproduce today. If it does later, I'll update this post.

The EDM Designer replaces spaces in column names with underscores but not foreign-key constraints. You can delete and replace the offending constraint names in VS 2008's Server/Database Explorer (but not in SQL Server 2008's Management Studio). However, you can't use the same Entity SQL or T-SQL test queries with Northwind.sdf and Northwind.mdf.

I needed compatible SQL Server 2005+ and SSCE v3.5 Northwind versions for my forthcoming Professional ADO.NET 3.5 LINQ and Entity Framework book for WROX/Wiley. So I modified the InstNwnd.sql script from the SQL Server 2000 Sample Databases installer to create with SSCE v3.5 the original eight Northwind tables with the same indexes, as well identical constraint and column names as the SQL Server 2005 version. The Employees and Categories tables include the original bitmaps. The only difference from the SQL Server version is that the Order Details table is named Order_Details. Hopefully, VS 2008 SP1 will contain the fix for LINQ to SQL's O/R Designer to enable visual design with SSCE v3.5 databases.

You can download, which contains the upgraded 1.5-MB Northwind.sdf file, from SkyDrive by clicking here.

Note: Scott Hanselman says in his ASP.NET MVC Cheesy Northwind Sample Code post of March 11, 2008:

I known it's cheesy to do demos with Northwind, and we're actively working on more real world, more interesting samples.

OK, Scott, it's been two months. Where are the "more interesting samples?"

Northwind FAQ: How old is Margaret Peacock? Answer: 70 years. She was born 9/19/1937 12:00:00 AM. Time to retire?


friism said...

The Bangalore database-design-practice jab seems superfluous...

Anonymous said...

Thank you very much! Northwind.sdf it's very useful.