using SQL Server 2000;
using insane data modeling standards;

First, do not have a compound primary key if one of the fields in the table is an Identity.  An identity is, by definition, unique.  All you are doing is adding more work and aggravation for everyone (except maybe yourself, if you only do the data modeling) for no benefit.  Therefore, if you have a compound primary key for a table and one of the columns anywhere in the table is an Identity:

• You are wasting disk space by copying all the rest of the primary key as foreign keys in other tables when a single, simple integer will do.
• You are degrading performance by forcing joins on 5 columns (yes, in my case it was 4 columns PLUS an Identity column).
• You are forcing developers to have to code around their a** to get to their elbow, since they must code in joins that are 3-4 fields too many.

Second, primary key != clustered key.  The problem here is that what was designated as the primary key was also the clustered key.  While this puts all of the important fields in a clustered key and thus should theoretically improve performance, this is still a bad design.  The clustered key should include the fields most often searched on and which narrow the result set substantially.  This speeds up queries dramatically.  But this does not have to be the primary key.  I can join on an Identity (integer) field very easily, quickly, and still set the clustered key to 4-5 columns no sweat. Plus the majority of the time, performance is almost as fast for a nonclustered index on an identity field as for a clustered index on an identity field.

Scott Ambler writes about assigning keys effectively (a link to the page where this is from can be found in the More Info section below):

1. Avoid “smart” keys

2. Consider assigning natural keys for simple “look up” tables

3. Natural keys don’t always work for “look up” tables

4. Your applications must still support “natural key searches”

More Info

• Data Modeling 101 - by Scott Ambler on the AgileData site.
• SQL-Server-Performance.com posted an excellent interview with David Gugick, SQL Server performance expert.  If you read both pages of this interview and follow everything in it, then you will be 80%+ of the way "there."
• Kalen Delaney's book Inside Microsoft SQL Server 2000 (Amazon Associates link) is the classic treatise on SQL Server's inner workings. If you are a DBA or database developer, buy this book.
• Kalen Delaney also had a series of 14 articles in SQL Server Magazine ending in October 2002 about query tuning (including tons on indexes, clustered and non-clustered keys, etc.).  Unfortunately, after July 1st you will need to be a subscriber to access the article archive.