Identity column - good or bad

[redlam]

Hi all.
I work for a company that is developing a client/server application with a rather large and complicated database. I am told that we should not use identity columns as primary keys because "There are no advantages of using them with related tables" and "They are designed for use in small projects/simple databases". Personally, I'm inclined to use identity columns as primary keys but that's just my personal preference. Can anyone else provide a general opinion on this? What are the potential disadvantages to using identity columns?
Thank you in advance for any and all comments.

 

[karluk]

I think the biggest advantage of identity columns is supposed to be in performance - they provide an extremely compact key, which in turn reduces the size of related indexes and foreign keys. In this sense the advice you are getting is exactly the opposite of what you should do. You would get the maximum advantage from using identity columns on large tables in big projects.

You would probably also get a boost from the fact that identity columns are monotonically increasing. That should reduce the frequency of page splits from inserts to the middle of an index or clustered table.

One disadvantage of identity columns also arises from the fact that they are increasing sequence numbers. Database update activity tends to be restricted to certain "hot pages" that gets updated repeatedly, rather than having a more even distribution of updates. That can lead to bottlenecks and contention.

I personally tend to prefer a practical approach. If there is available an externally assigned unique identifier like employee number or medical id, I use it. That prevents me from needing to maintain the overhead of both a primary key on an identity column and a unique constraint on the external identifier. On the other hand, if the alternative is a cumbersome compound key, I tend to use an identity column.

 

[redlam]

Thanks for the info. Its helpful.