TSQL ID Columns in Tables

Over my development years, I’ve seen many tables designed with useless ID columns.  The columns serve no purpose.  They are not reference by anything.  Then to make matters worse, a primary clustered key is created on the ID column.

My recommendation is to only add ID columns when you need to create a relationship between tables.  For example, if you have a customer table and a customer orders table, you need the ID field on the customer table to reference on the order table.  But you don’t need the ID on the orders table.  If you have an orderdetails table, then you would need the ID on the orders table.

The extra ID column adds unnecessary storage overhead and you waste your clustered index when you make it a primary clustered key.

It is always beneficial to ponder a little longer about your table designs and determine whether you really need an additional ID column as well which column(s) are the best to create your clustered index on.

Advertisements

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