This isn’t something new but I believe it worth a sharing.
If you are a programmer or a database administrator, you must face this question either before or after reading this.
I am going to explain the way(s) to retrieve seed identity value after insert into a database table. Always, with the very handy feature of the up-to-date relational database systems, we have the auto-increment primary key/field. More complex situation that is we needs this auto-increment primary key/field to be a foreign key/field for another table which may create a 1-to-1 relationship, 1-to-Many relationship or Many-to-Many relationship for these tables.
In your task, assignment or coding design, you are require to acquire the last generated auto-increment index/key value to be a foreign key of another table. For example, you have a Sales table to store every sales order. Another table called SalesItem to store items belongs to every sales order. It is link to Sales table by the foreign key called SalesID. Each sales order may contains more than 1 item but 1 item can belong to a sales order only. Refer the diagrams below.
Now, the difficult part! We knows how to Insert into both tables but how do you ensure the consistency in a heavy-loaded multi-user environment. I mean heavy-loaded in terms of more than thousands of transactions per minutes or seconds!
I admit that 7 years ago. I was so stupid to used the following approaches
Insert Into Sales(… field name …) Values(… values …); Select Max(SalesID) as SalesID from Sales; Inserts Into SalesItem(… field names…) Values(…values…’SalesID’…)
or
Insert Into Sales(… field name …) Values(… values …);
Select SalesID from Sales order by SalesID desc;
[Store the first SalesID result into a variable and continue with the insertion to the SalesItem table]
Inserts Into SalesItem(… field names…) Values(…values…’SalesID’…)
Both approaches works fine in a single user environment which equal to the development environment where you do all the testing by your lonely self… What if more users do the same transaction at the same time or concurrently ?! I can foresee an identity crisis will happens between these tables sooner or later!
Hold on! Stay calm, young one! No worry at all! We have the solution for now! We can solve this easily! Thanks to the mighty technology enhancement!!
Now we have 3 functions to retrieve seed identity value after insert. Namely the Scope_Identity, @@Identity and Ident_Current.
Scope_Identity, @@Identity and Ident_Current are similar functions because they return values that are inserted into identity columns.
Ident_Current is not limited by scope and session; it is limited to a specified table. Ident_Current returns the value generated for a specific table in any session and any scope. This is highly dangerous in multi-user environment because it might be more than 1 users try to insert into the same table concurrently…
Scope_Identity and @@Identity return the last identity values that are generated in any table in the current session.
However, Scope_Identity returns values inserted only within the current scope. While @@Identity is not limited to a specific scope.
The above is quoted from here, here and here!
@@Identity and Ident_Current can goes pretty wild if you do not know how manage it. Most likely, if you cannot make very good and precise assumptions, you better avoid using these. Scope_Identity has the advantages to give you only the value of current scope only. The risk is minimized by Scope_Identity but it can be messy if you have a very bad table design and coding strategy. Let’s forget it 😀 . There is no good or bad design. Only working or not working design, am I right?
I am not saying that Scope_Identity is a must-apply function for identity retrieving. You must consider the other two functions depends on the situation. There must be a reason for their presence. 🙂
Happy Identity Seeking!
