Why @@Identity May Not Return the Actual New Row ID Value in SQL Server

Posted 14 January 2011

While I was reading the excellent topic “Database Development Mistakes Made by Application Developers” over on Stack Overflow, I followed a link to “What are the most common SQL anti-patterns?” and came across this tip that made me wonder if I’ve been handling the creation of new identity values all wrong:

This article from a SQL Server MVP pretty much repeats that information.

Now, I don’t use triggers in my application building. I prefer to have the business logic in the application layer, not the database layer. I’ve not run across any identity scope issues before, but that doesn’t necessarily mean I’m safe, or that one of my team members won’t use triggers in their application development.

If you do an insert via <cfquery> in ColdFusion 8 and 9, it returns the ID of the inserted row (or rows) in the result attribute of the <cfquery> tag. For example, if you’re using SQL Server, result_name.IDENTITYCOL is the reference to the ID of the inserted row. If you’re using MySQL, it’s result_name.GENERATED_KEY.

Does anyone know if ColdFusion uses @@IDENTITY or SCOPE_IDENTITY()?

Categories: SQL