SCOPE_IDENTITY() return the id from the database on insert

As a .NET programmer most of my time is spent coding in C# and I try to avoid writing SQL where possible. Recently I had to write an insert stored procedure and needed to return the ID of the row I was inserting. While writing my usual bad SQL I came across a fascinating function I’ve never used before, SCOPE_IDENTITY().