0

Let's assume the following simple table in SQL Server 2012:

CREATE TABLE Person (
   Id INT IDENTITY(1, 1) PRIMARY KEY,
   Name NVARCHAR(50)
)

And the following corresponding class in C#:

public class Person 
{
    public int Id {get; set;}
    public string Name {get; set;}
}

Consider now the following CreatePerson method (pseudo code):

public static Person CreatePerson(string name) {
    ...
    DB.Execute("INSERT INTO Person(Name) VALUES (name)", name);
    int lastId = DB.Get("SELECT MAX(Id) FROM Person");
    return new Person {Id = lastId, Name=name};
}

This method creates a new person row in the db, builds a new Person object and then it returns the created object that is supposed to contain the database generated Id.

Is it safe and/or correct (from a concurrency perspective) to get the last generated Id by using the following SELECT statement?

SELECT MAX(Id) FROM Person

I am concerned about the following scenario:

  1. USER A Inserts a person 1
  2. USER B Inserts a person 2
  3. USER A reads the last Id and thinks that it is the Id of person 1

Note that USER A and USER B here refers to threads handling the two separate queries to the database.

Chedy2149
  • 2,821
  • 4
  • 33
  • 56
  • What are you trying to do? In a distributed system, such information can easily become outdated - what use do you expect out of the value that you get? – xxbbcc Aug 30 '16 at 14:29
  • If you want user A to just see his inserted people (or in this case person), you could add a field to the table that indicates which user inserted the person. With a fitting `WHERE` statement added to your `SELECT` you shouldn't have any problems with `MAX(Id)`. – diiN__________ Aug 30 '16 at 14:31
  • 2
    Possible duplicate of [Best way to get identity of inserted row?](http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row) – Igor Aug 30 '16 at 14:31
  • 2
    No, it is not safe. SCOPE_IDENTITY() et al exist for this. – Alex K. Aug 30 '16 at 14:31
  • You can get it (or scope_identity) but next moment it's already not valid. – Serg Aug 30 '16 at 14:34
  • 1
    No, it's not safe. –  Aug 30 '16 at 14:37
  • please check the edited version – Chedy2149 Aug 30 '16 at 21:33

2 Answers2

4

If you wanted to get the last identity value inserted by a person on that particular scope, use Scope_Identity().

If you wanted to get the last identity value inserted to a particular table across the scope, you can go with @@identity.

If you wanted to get the current identity value of a particular table, use Ident_Current ('Yourtablename').

Unnikrishnan R
  • 4,965
  • 1
  • 12
  • 21
2

No it is not safe,you can use this procedure for data insert and get your lastId by using scopre_Identity function

create PROCEDURE Your_procedure_for_insert
@Name      VARCHAR(50),
@lastID      INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Person
            (
            Name                
            )
VALUES  
            (
            @Name 
            );

SET @lastID = SCOPE_IDENTITY();

END;
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63