Identity Functions

 This article does a good job of summarizing the various functions you can use to grab the primary key value of the row just inserted into a table.

Here's a blurb from the post that summarizes findings:

So what are the differences, and which one should we use?

@@Identity, Scope_Identity(), and Ident_Current() are all similar functions because they return values that are inserted into identity columns. Output is not a function, but a clause we add directly into the DML statement we use.

The @@Identity function will return the last identity value inserted in the current session, in any table and in any scope.

The Scope_Identity() function will return the last identity value inserted in the current scope (and session), in any table.

The Ident_Current() function takes in a table (or view) name and returns the last identity value generated for that table, regardless of session or scope.

The output clause returns the values inserted directly from the DML statement it's a part of.