New ID for record in SQL Server
In a recent technical mentoring session that we hosted, a question came up that crops up regularly, so I thought I’d post a quick summary here.
The question is “How do I retrieve the id of a newly created record in a SQL Server table from my application?”
For this example, we’ll assume that the table in question is called tutor, and has just two fields, id and name, where the id field is a primary key with an identity field. The table definition would be
CREATE TABLE tutor (id INT PRIMARY KEY IDENTITY(1,1), full_name VARCHAR(100) NOT NULL)
Firstly I’d like to warn against the common use (miss-use) of a well known system variable called @@IDENTITY. This variable gets back the value of the most recently updated identity field, which on the face of it seems perfect. However, there are two key things to watch.
Firstly, you always need to wrap the code that does the INSERT and the code that gets the value of the id in a transaction (either in your client code or in a stored procedure). If you don’t, it is possible that the value you retrieve could be that of a record that somebody else inserted and not the one that you inserted. This applies whether you use @@IDENTITY or SELECT MAX(id) FROM tutor.
The second thing to watch out for are triggers (even if you aren’t using them now). Let’s suppose that you had written code such as
INSERT INTO tutor (full_name) VALUES (’Mr F Bloggs’)
and then did SELECT @@IDENTITY and this was all wrapped in a transaction - all looks good and it would work fine. Now suppose that somebody else comes a long in the future and adds a trigger to the tutor table that adds a log entry to another table with an identity field. Now your code wouldn’t work as expected. You would actually be retrieving the value of the id in the log table, rather than your new tutor’s id - not what you want at all. The secret is to use IDENT_CURRENT, which allows you to specify the name of the table and gets back the most recent id added. In this case you would use SELECT IDENT_CURRENT(’tutor’).
If you create a stored procedure to wrap up the code, then either use an OUT parameter to retrieve the value or use return value.
Miles
Daniel Thomas:
Connected with the above post, when I do the following I sometimes get different results - why?
SELECT IDENT_CURRENT(’my_table’)
SELECT MAX(id) FROM my_table
also, which is more efficient?
Cheers
15 April 2008, 9:16 amDan
Miles Dennis:
The reason the two queries sometimes return different values is due to what happens when an insert fails.
Suppose your table has two records in it, with Id’s 1 and 2 and no attempt has been made to add a 3rd record. In this case the two queries above will return the same values i.e. 2. Now let’s insert another record, but we’ll effectively cause it to fail (by starting and rolling back a transaction).
BEGIN TRANSACTION
INSERT INTO mytables VALUES (’xxx’) /* not sure what your fields are */
ROLLBACK TRANSACTION
Now, SELECT MAX(id) will return 2 as before, but IDENT_CURRENT will return 3 as this was the last attempted value.
If you now do an successful INSERT, both methods will return 4.
As you can see, it’s vital that you perform all operations including the selection of the new id in a transaction.
From a performance point of view, the IDENT_CURRENT should generally be faster.
Miles
15 April 2008, 3:21 pm