I read last week a KB article on Microsoft's web site (sorry, I don't have the URL handy) that outlined a problem using Identity columns in SQL Server.

Situation: you have 2 tables, one parent one child. Both have Identity columns as PK and of course the child table has an FK column that is the PK of the parent table.

When you insert a record in the parent table, you need it's PK for the child table - to link the child records. You can get that PK using SELECT @@IDENTITY. So far so good.

Now, the DBA decides to log modifications to the parent table. He therefore creates another table with it's own Identity column and adds an Insert Trigger to the parent table, which inserts a record into the log table.

Now the problem is that SELECT @@IDENTITY will return the PK of the log table, not the parent table, therefore your links no longer work. A workaround would be to handle the PKs ourselves, but wouldn't that create a contention (lock)problem on the table that stores PKs?

Anyone has suggestions?

---

>>>Microsoft said that having sql automatically provide key values (Identity Column) can reduce costs and improve performance and reduces user-transaction bottlenecks.
>>>
>>>Does this mean that all tables created should have an identity column field?