IDENT_CURRENT
Problem:
1. You insert into "UserTable" and the row's identity is 2005.
2. Another user inserts into 'UserTable' and the row's identity is 2006.
3. You call IDENT_CURRENT() and it returns 2006. The wrong ID.
INSERT INTO Table (aColumn) VALUES (aValue)
--after your insert but before your select another user executes the same insert.
SELECT Ident_Current
So the solution for using IDENT_CURRENT would be to wrap the Insert/Select in a translaction and call (TABLOCKX) on the table and lock everyone else out to ensure you get your identity. This will also block other users from even calling a Select on the table and can create deadlocks on your system and cause the queries to hang until the lock is released, slowing down your system. To get around this you could call (NOLOCK) on your selects but then you're ready dirty data. The list goes on and on... Does this sound like a good solution to you? If it does then go back to using flat files for databases.
@@IDENTITY
Problem:
1. You insert a record in to UserTable
2. You select back @@IDENTITY
3. Everything is good.
Two years later your boss wants you to implement logging in your application so you add a trigger to UserTable to log what user modified which fields.
So at this point we have the trigger implemented.
1. You insert in to the usertable and get ID 2074.
2. The trigger fires off and inserts an entry in "UserTableLog" which gets the record id of 1000
3. You call Select @@IDENTITY and the value 1000 is returned.
4. Your application breaks.
Unless you are intending to locate a record created by a trigger then this is unsafe. It will work but on down the road if you change something and have @@IDENTITY calls throughout your application you will break it. Better yet you hire a new DBA and he does this without telling you. You're not aware of the DB changes, hes not aware how your application works, so nobody has half a clue what happened. Does this sound like a good solution?
SCOPE_IDENTITY()
1. You insert a record in to UserTable and it generates record 8751.
2. Another user inserts a record a in the table creating id #8752
3. triggers fire off from both inserts creating ids #1,#2
4. You call SCOPE_IDENTITY and it returns 8751, the expected value.
5. Life is good.
IF you are using SQL Server Enterprise edition (not express, msde, or developer)
and you have more than one logical processor
and you have the sql server's max degree of parallelism configured to >1
and you write a complex insert query that generates a parallel execution problem then you
may have a problem. That being said it is rather unique circumstances in which this command will fail and there are three published workarounds for it.
Offline 
Unsolved 
