T-SQL Identity

This is probably as old as the hills but I still see this everywhere. What’s wrong with this line of T-SQL

Declare @PrimaryKeyVariable int
Set @PrimaryKeyVariable =  @@IDENTITY

Nothing until it gets deployed into a high volume usage situation and the number of users ramps up after a few months. Then it will blow up and someone will be in at 3a.m. desperately trying to restore the referential integrity of the database.

The problem is that it will set the variable to the most recently set identity flag in the database no matter who has done it. That might be you or it might be another connection – thus linking up/deleting/updating the wrong records. If you’re lucky the transaction will fail. If not then you’ll be digging around in those two hourly transactional backups to get it back. Oops.

And the answer is really straight forward. Just use this instead

Declare @PrimaryKeyVariable int
Set @ PrimaryKeyVariable = Scope_Identity()

The identity is now only taken from current connection. It’s scoped – just like mother always used to scope her identities. Lovely.