SQL Server 2005 introduces the OUTPUT clause that allows us to find out the values (original or new) for columns manipulated by any INSERT, UPDATE or DELETE statement. With OUTPUT's help, the previous example can be rewritten like this:

DECLARE @InsertedRows AS TABLE (Id int)
DECLARE @NewId AS INT
INSERT INTO HumanResources.Employees
( /* column names */)
OUTPUT Inserted.Id INTO @InsertedRows
VALUES (/* column values */)
SELECT @NewId = Id FROM @InsertedRows

Note the @InsertedRows temporary table declaration and the use of the OUTPUT clause right before the VALUES clause inside the INSERT statement. This code is not only concurrency-safe, but it allows us to get the values of other columns that could have been generated (e.g. by DEFAULTs or TRIGGERs). Moreover, as I already mentioned, it can also be used with UPDATEs and DELETEs which makes it useful for those who like to have record-level logs (even though I prefer to use business level logs, but that's another story...)

Read comments in article 2!