Identity column as clustered index bad idea? Its just been a few months of programming in SQL Server for me so my knowledge is not good in many regards. In an already existing project at work I came across many tables with large composite primary keys with clustered index. From what I have gathered, a large column/composite column with clustered index hits performance very hard, and at times the logical solution is an identity column. But at the same time I have come across many people flaming the over-usage of identity columns.

But I have never came across an example where identity column is a bad idea.

Recently we have standardized that every table should have an identity column as the clustered index - whether we use it as PK or not, as we require it for some export purposes.

So I would like some examples, in real life scenarios, where using an identity column as a clustered index is a bad idea.

Though at times it makes our life easy I have never encountered a scenario where it will be considered bad.

PS: I think my question is a bit naive but it is bugging me so much so I had to ask about it.
I usually use an identity column as clustered primary key. However in some (rare?) cases this is not ideal because of the LastPageInsertLatchContention. This happens if a table is heavely filled with data. Because of the identity key all this INSERT's wants to write the last page of the table (index). So this page can be locked and the performance may be better with another solution.

See 


http://dangerousdba.blogspot.ch/2011/10/bit-reversion.html
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/monotonically-increasing-clustered-index-keys-can-cause-latch-contention.aspx
http://www.sqlpassion.at/archive/2014/04/15/an-ever-increasing-clustered-key-value-doesnt-scale/


for details.