How To Get Last Inserted ID On SQL Server
Many people use identity column as primary key in table. Identity column can increase value automatically whenever new row is added. After adding a new row, it is very common problem to find an ID of last inserted record. Depending of your case, there are some common solutions too.
I inserted a new record and want to know its ID
To get an ID of last inserted record, you can use this T-SQL:
INSERT INTO Persons (FirstName) VALUES ('Joe');
SELECT ID AS LastID FROM Persons WHERE ID = @@Identity;
You can use query like this inside stored procedure or as an ad-hoc query. After new row is inserted, second query will return only one number. Because only one number is returned, you can use this query with ExecuteScalar method of SqlCommand object.
I just need last table ID, but don't want to insert new row
If you use just second query from last example, @@Identity value will be NULL. To get last ID in this case, you can use MAX function with code like this:
SELECT MAX(ID) AS LastID FROM Persons
Related articles:
1. SQL Queries For Paging In ASP.NET
2. Ad Hoc Queries in ASP.NET
