Return last inserted ID without using a second query

Question

I'm working on an ASP.NET project (C#) with SQL Server 2008.

When I insert a row into a table in the database, I would like to get the last inserted ID, which is the table's IDENTITY (Auto Incremented).

I do not wish to use another query, and do something like...

SELECT MAX(ID) FROM USERS;

Because - even though it's only one query - it feels lame...

When I insert something I usually use ExecuteNonQuery(), which returns the number of affected rows.

int y = Command.ExecuteNonQuery();

Isn't there a way to return the last inserted ID without using another query?

Answer 1

Most folks do this in the following way:

INSERT dbo.Users(Username)
VALUES('my new name');

SELECT NewID = SCOPE_IDENTITY();

(Or instead of a query, assigning that to a variable.)

So it's not really two queries against the table...

However there is also the following way:

INSERT dbo.Users(Username)
OUTPUT inserted.ID
VALUES('my new name');

You won't really be able to retrieve this with ExecuteNonQuery, though.