The IDENTITY property is one of the most useful tools available to us in Microsoft SQL Server. We basically use it to automatically populate an integer column with a new value on inserts. It’s a great way to let SQL Server manage the integer values placed in a column so we don’t have to worry about it!
Most of the time, the IDENTITY property works just fine without interference from us, but sometimes we need to step in and make a quick change.
In this very brief tutorial, we’ll discuss how you can reseed an IDENTITY value. It’s very simple.
To reseed an IDENTITY value, run this statement:
DBCC CHECKIDENT(‘<tableName>’, RESEED, <Reseed value>)
To demonstrate how to reseed an IDENTITY value, let’s take a look at a very simple table that stores information about movies. The MovieID column has the IDENTITY property placed on it:
Here’s the CREATE TABLE and INSERT statements if you want to follow along:
CREATE TABLE Movies
(
MovieID INT IDENTITY PRIMARY KEY,
MovieTitle VARCHAR(15),
DurationInMinutes INT
)
INSERT INTO Movies (MovieTitle, DurationInMinutes)
VALUES ('Contagion',106)
INSERT INTO Movies (MovieTitle, DurationInMinutes)
VALUES ('Fight Club',139)
INSERT INTO Movies (MovieTitle, DurationInMinutes)
VALUES ('Skyfall', 143)
So everything is hunky-dory so far. But let’s try to insert a row where the MovieTitle value is longer than what the column can hold (which is only 15 characters):
We get an error message. We’ll deal with that later.
The important thing to understand is that even though the INSERT statement failed, it still tried to use the next IDENTITY value, which I suppose would have been 4. That value of 4 has been essentially wasted.
Let’s fix the column to allow more than 15 characters. We’ll increase it to 30:
Now let’s try our INSERT statement again:
Great, our row was inserted, but now there’s a gap!
Maybe for whatever reason we needed this movie to have an ID of 4.
We’ll delete this new row and see what we need to do to reseed the IDENTITY value:
DELETE FROM Movies WHERE MovieID = 5
An example of reseeding an IDENTITY value:
Ok, so after that last successful INSERT statement for ‘No Country for Old Men‘, what is our IDENTITY property currently set to? We can find out using the simple IDENT_CURRENT system function. You can use this system function in a simple SELECT statement. The syntax is as follows:
SELECT IDENT_CURRENT('<table_name>')
Here’s our example:
So this number represents the last IDENTITY value that was used in an INSERT statement into the table. Since the last ID was 5, the next insert statement will use an ID of 6.
But we don’t want that. We want to reseed the IDENTITY property to have a value of 3, so that the next INSERT statement will have an ID of 4.
You still with me?
Here’s the very simple statement we run to reseed our IDENTITY property to 3 for our Movies table:
Pretty simple.
Now if we run our INSERT statement again for our “No Country for Old Men” movie, we see it gets an ID of 4:
Superb!
Automatic reseeding can occur
Under certain circumstances, SQL Server will automatically reseed an IDENTITY value. This happens when you turn on IDENTITY_INSERT for the table, then insert a row with an ID that is higher than the current ID. The IDENTITY seed value will be reset to that higher ID value you outlined.
Let’s look at an example. First we’ll double check what the current ID is for our IDENTITY property:
Then we’ll turn on IDENTITY_INSERT for our Movies table:
Cool. Now we’ll manually insert a row that has a higher ID than what is the current ID for our IDENTITY property:
Since this ID is higher, it becomes the new seed value of the IDENTITY property! We can run our simple check to verify:
Now I’ll turn off IDENTITY_INSERT and insert another row:
So understand that if you want your new seed value to be higher than what it is currently, you don’t have to reset it manually. You can just insert a new row with the new specific ID you want and SQL Server will reset the IDENTITY seed value to that number for you automatically.
Next Steps:
Leave a comment if you found this tutorial helpful!
You should also download the following FREE GUIDE:
FREE 1-page Simple SQL Cheat Sheet on the IDENTITY property!
This guide contains all the key details you need to know about the IDENTITY property in SQL Server, condensed into a simple 1-page guide. It will definitely be a great resource for you to reference during your career as a database professional. Download it today!
If you missed the introduction to the IDENTITY property, you can check it out here:
IDENTITY Column in SQL Server: Everything you need to know
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
