Date: 12/12/06 (SQL Server)    Keywords: database, sql

x-posted to databases

As part of our current SQL setup we've got an extended proc that calls a C++ DLL to generate a unique id for all db calls. This unique id is use as a primary key on a logging table that is inserted into (along with user id, destination stored proc and any params) prior to running the users call. Also this unique id is added to the params list and passed on to the destinition stored procedure. Finally the logging table is updated with the return code of the call.


Unfortunately this DLL won't work under SQL 2005, therefore I'm rewriting as part of the upgrade. That isn't the problem. The problem is one of the DBAs asking why we just don't use an Identity column on the logging table. TBH I couldn't think of a reason why not, especially if we use SCOPE_IDENTITY rather than @@IDENTITY. The only reason I could think of is historical, ie the original developers didn't trust SQL Server to handle the load (the DLL has been used since at least SQL 6). Can you think of situation where the use of an Identity column would fail?

Source: http://community.livejournal.com/sqlserver/54552.html