SCOPE_IDENTITY(), @@IDENTITY, and IDENT_CURRENT('tablename')

this came up today, want to make sure everyone understands how these work:

• SCOPE_IDENTITY() returns the value of the Identity column for the last insert, for the current session (i.e. sproc calling SCOPE_IDENTITY()) AND current scope (i.e. trigger inserts caused by your insert are not considered)
• @@IDENTITY returns the value of the Identity column for the last insert, for the current session but REGARDLESS of scope (i.e. trigger inserts caused by your insert are considered)
• IDENT_CURRENT('tablename') returns the value of the Identity for the last insert, REGARDLESS of session or scope

so in general, you probably want to be using SCOPE_IDENTITY() to get the last inserted value for an Identity column. if there are no triggers on the table being inserted to, @@IDENTITY and SCOPE_IDENTITY() are identical, but if a trigger is later added your sproc will need to be changed. you almost never want to use IDENT_CURRENT(‘tablename’), since the current value can easily change between the time you make your call to this function and you do something with the result--