-- PRIMARY KEY - don't CREATE TABLE without it. Doesn't matter how temp it is. Even in-memory table variables.

-- IDENTITY - a wonderful alternative to 200+ byte PKs that aren't required to ensure data integrity. You may have a natural PK, but do you really want to use it when it's five varchar(100) fields that change? This is even more important if other tables need to reference records in the table. Always consider "lookup" tables and other "extreme relational design" techniques and super-normalized schemas -- SQL Server is tuned much better for large Star-schema queries such that performance gains from denormalization aren't as easy to acheive.

-- and CLUSTERED INDEXES: If you are using SQL Server 2K (or even 7), you have no excuse to not apply a clustered index to each and every table. Period. Understand that SQL Server handles tables with no clustered index quite differently in many respects to clustered tables. If you have more than 100 pages of data and you have significant modifications to existing rows, you will soon die under the I/O overhead. Only the rarest of circumstance could find the sole benefit of non-clustered tables (SQL Server's ability to reuse space created by deleted rows) more compelling than the many benefits of clustering a PK.

As a general rule, OLTP-type usage will demand the PK be clustered, and OLAP-type usage many benefit from another set of columns for range searches. But don't forget that you can never go wrong with a clustered PK, but you can go very wrong with some other index clustered. Also, remember that a covering index is just as effective in improving query performance as the best clustered index. (I have one table with an index that has every table field, but with a different order.)

Any body have examples where a non-clustered table is the best solution, particularly on SQL2K? I'd love to see 'em...