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.
INT IDENTITY
as the primary (and clustered) key on almost any table. It's a well recommend best practice and usually works just fine. Cases where this is not a good idea are relatively rare in my opinion.