SQL Server Table Design Rules of Thumb

These are not meant to be comprehensive (and don’t get into advanced normalization or intentional denormalization) but will result in a decent table design without too much thought in most situations. A case can be made for natural primary keys but going the surrogate route is easier and works well too.

  1. Always have a primary key. Typically a surrogate key using an identity column and not necessarily clustered.
  2. Almost always have at least one unique constraint if you used a surrogate primary key
    • consider making this/one of these the clustered index as they may be the most common way rows are selected
    • these will frequently save you from bugs/errors that would result in duplicate data
    • name and abbreviation in a state table are a good example — only one state should have the abbreviation TX and only one should have the name Texas
  3. Always include create date and created by columns. If rows can be updated, include updated date, and updated by columns as well.
  4. Consider effective and expiration date columns in place of “active” flags.
  5. Consider a timestamp (not datetime, but timestamp) field if multiple simultaneous edits are possible
  6. Avoid nullable columns – they result in ISNULL/COALESCES — and use defaults instead – EG ‘’ (empty string) for varchar fields, 0 or -1 for ints, etc. A large number of necessarily nullable columns suggest the table may need to be split.
  7. Avoid nullable foreign keys — they result in outer joins
  8. Consider indexes on any foreign keys
  9. Do not store compound values in one column.
  10. If you know in advance how your table will be queried and none of the indexes created from rules 1, 2, or 7 meet requirements, consider creating indexes on those columns if they are selective (have a wide distribution of values).
  11. You can break any rule except number 1 with good reason after very careful consideration
Advertisements

4 Responses to “SQL Server Table Design Rules of Thumb”

  1. Mafia Wars Fan Says:

    Nice Job. For what Its worth you have at least one fan in Kansas City

  2. cromartee Says:

    I like your rule #11 best. I think your list is all good things to be considered but even #1 isn’t so necessary. Many tables can perform perfectly in all measurable and practical ways without primary keys defined, and there’s no security reason to do it. And there’s this from http://www.simple-talk.com/sql/learn-sql-server/database-design-workbench—keys/#primarykey which says more:

    Primary key
    the default candidate key for a table. Primary keys are simply the default keys for a table. They are no longer required by relational theory for a table, just a candidate key, and are, according to CJ Date, included only for historical reasons. However, they should be used as there is no other way to specify to other processes which key should be used by default.
    A Primary key is no more than an indexed key whose value, taken together across all component columns, is unique and known. SQL Server creates a clustered unique index when you specify a primary key, but this is not always the best solution, since a clustered index works best when it is not unique, not null and when the values are reasonably evenly distributed”

    • jopincar Says:

      The vast majority of tables should have a primary key, artificial, or otherwise if for no other reason than to make a deterministic update possible. Unique constraints on non-artificial value are generally very useful strictly as indexes and also frequently expose bugs that would otherwise go undetected.


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

%d bloggers like this: