How to get the last inserted row number?

Question

This question may seem very very easy (Select COUNT(Id), but in my case is different. I developed a web application with 15 to 25 concurrent users. every time they open the program, I am checking the last row inserted into the database and in C# I created a serial number based on the last row inserted + 1. So say for example below entry: User1 opens up the program: DB count = 1 C# Series = Ser-00001 (displayed in UI) User1 inserts a row. success User2 opens up the program: DB count = 2 C# Series = Ser-00002 (displayed in UI) User2 inserts a row. success User3 opens up the program: DB count = 3 C# Series = Ser-00003 (displayed in UI) User3 inserts a row. success No issue right? But, there will be a time that 2 or more users will open the program at the same time. Now, below is the scenario User7 opens up the program: DB count = 4 C# Series = Ser-00004 (displayed in UI) User7 inserts a row. success User8 opens up the program: DB count = 4 C# Series = Ser-00004 (displayed in UI) User8 inserts a row. failed User9 opens up the program: DB count = 4 C# Series = Ser-00004 (displayed in UI) User9 inserts a row. failed This time a big problem suddenly occur! Now my question is, how do you do it to avoid this type of duplication? Thank you in advance for any posible solution.

Siddharth Gajbhiye · Answer

By definition, a table is an unordered bag of rows (see #3 here). There is no way to ask SQL Server which row was inserted last unless you are doing so in the same batch as the insert. For example, if your table has an IDENTITY column, you can use SCOPE_IDENTITY() (never use @@IDENTITY, since that can be unreliable if you have or will ever add triggers to the source table): INSERT dbo.table(column) SELECT 1; SELECT SCOPE_IDENTITY(); More generally, you can use the OUTPUT clause, which doesn't rely on an IDENTITY column (but will still make it difficult to identify which row(s) the clause identifies if there is no PK): INSERT dbo.table(column) OUTPUT inserted.* SELECT 1; If you're not talking about the same batch, then the only real way to identify the last row inserted is to use a date/time column where the timestamp of insertion is recorded. Otherwise it is like you emptied a bag of marbles on the floor, then asked someone to enter the room and identify which one hit the floor last. You may be tempted or even advised to use the IDENT_CURRENT() function, but I explain here why this is unreliable too. You could add a column to track this going forward: ALTER TABLE dbo.table ADD DateInserted DEFAULT CURRENT_TIMESTAMP; Now you can find the last row(s) inserted by simply: ;WITH x AS (SELECT *, r = RANK() OVER (ORDER BY DateInserted DESC) FROM dbo.table) SELECT * FROM x WHERE r = 1; (If you don't want ties, you can add a tie-breaking column to the ORDER BY, or you can simply change RANK() to ROW_NUMBER() if you don't care which of the tied rows you get.) You might make the assumption that the last row inserted is the highest identity value, but this isn't necessarily the case. The identity can be reseeded and it can also be overridden using SET IDENTITY_INSERT ON;. Reference :https://dba.stackexchange.com/questions/53071/how-to-extract-the-last-inserted-row-in-sql-server

Sachin Singh · Answer

if you are use Ado.Net then use @SCOPE_IDENTITY()function, if using Entity framework then directly retrieved the Id after calling SaveChange(); context.Employees.Add(emp); context.SaveChange(); var lastId=emp.Id;

Sundaram Subramanian · Answer

Hi, I would like to suggest you use GUID in the table as a new column and mark your existing column or make your existing column or set the column to Primary Key and set autoincrement which holds the serial number. So that no longer required to check that logic in C# code. Or even you can refer to the columns using the GUID as well. GUID is a 16 byte binary SQL Server data type that is globally unique across tables, databases, and servers.

Jes Sie · Answer

Thank you all for your responses. I tested all your suggestions, which brings me to the decision of making my column a unique key and implement Siddharth suggestion with a little bit of twist: Select top 1 CustomerID + 1 FROM fmCustomer order by getLogs DESC Thank you everyone for your beautiful and very helpful suggestions.

Jignesh Kumar · Answer

Hi, You can make you id column auto Increment for id coulmn so while insert you no need to insert that coulmn in your insert statement. SQL server maintain auto increment for that column. Please refer this link, https://www.c-sharpcorner.com/forums/insert-values-to-sql-server-databse-if-id-is-auto-increment

Salman Beg · Answer

Either apply primary or unique key constraint so which will not allow to insert duplicate values into database. Also it throws a exception in your code. Accordingly you can handle and show the message to the user or you can re-insert the record with a new series id. Thanks

How to get the last inserted row number?

Answers (6)