The IDENTITY property is an extremely useful tool we can use in SQL Server to automate the values in a column. It can be especially useful for primary key columns.
This property will automatically add a new integer value to a column when a new row is inserted into the table. It saves us the headache of needing to keep track of column values ourselves.
If you need to refresh yourself on how the IDENTITY property works, you should take a look at the full tutorial I have on the topic. That tutorial will teach you everything you need to know. It also touches on how we can update an IDENTITY column. Check it out:
IDENTITY Column in SQL Server: Everything you need to know
As we all know so well, things can go bad when inserting or updating data in SQL Server. What if a value was generated by the IDENTITY property, but for whatever reason, we need it to change?
Updating an IDENTITY value is not so simple as running a quick UPDATE statement, as your probably guessed.
Do you need to know how to update an IDENTITY value? Are you struggling to make it work?
In this tutorial, you’ll learn how you can update an IDENTITY column value (sort of….)
Here’s what we’ll discuss:
- The hard truth about IDENTITY column values
- Setting up an IDENTITY value we want to update
- The IDENTITY_INSERT setting
- DBCC CHECKIDENT function
- Final thoughts
Make sure you Download your FREE 1-page Simple SQL Cheat Sheet on the IDENTITY property!
Everything discussed in this tutorial is condensed into a simple reference guide for your convenience. Make sure you check it out.
Without further ado…
1. The hard truth about IDENTITY column values
Ok folks, I need to rip the Band-Aid off quick. Here’s the thing….
You can’t directly update an IDENTITY column value. There isn’t a way to do it easily (like through a simple UPDATE statement) in SQL Server.
To update an IDENTITY value, we basically need to use a couple of useful functions and settings available to us in SQL Server. Instead of updating the column value, we will basically do two things:
- Delete the row with the incorrect column value
- Re-insert the row and manually specify the correct value
Think of it as a poor man’s update. If we can’t update the data, we’re essentially forced to delete the bad row and re-insert a new row with the correct data.
Let’s get some data set up to show you how it’s done.
2. Setting up an IDENTITY value we want to update
Ok, let’s create a simple Customers table that has an IDENTITY column:
CREATE TABLE Customers ( CustID INT PRIMARY KEY IDENTITY, FirstName VARCHAR(15), LastName VARCHAR(15) )
Cool, now let’s add a single row to this table:
INSERT INTO Customers (FirstName, LastName)
VALUES ('Joshua','Porter')
Let’s check the data:
Ok, let’s add another row:
INSERT INTO Customers (FirstName, LastName)
VALUES ('Andrew','Bluefield')
And check the data:
So far so good. Now let’s try to add this row:
INSERT INTO Customers (FirstName, LastName)
VALUES ('Jack','Benjamin-Cloverfield')
Uh oh, did you get this error message?
String or binary data would be truncated.
We received this error because the Last Name we’re trying to insert is larger than the size of our LastName column. The column can only fit 15 characters, but the name “Benjamin-Cloverfield” is 20 characters long.
Well crud. No worries though. Instead of increasing the size of our column, let’s just decrease the Last Name value. This person’s Last Name will just need to be “Cloverfield” in our database. Let’s try again:
INSERT INTO Customers (FirstName, LastName)
VALUES ('Jack','Cloverfield')
Ok, now let’s check the data:
Oh my, now there’s a gap in our CustID values.
Maybe, for whatever reason, Jack Cloverfield needed to have a CustID value of 3. Since our first attempt at inserting his data failed, he now has a CustID of 4.
Sometimes that happens when dealing with the IDENTITY property. A new IDENTITY value can still be generated even if the INSERT statement fails. In that case, we say that IDENTITY value becomes wasted.
In our case, the IDENTITY value of 3 was wasted.
But again, maybe we have a business rule in place that says Jack Cloverfield needs to have a CustID of 3. He presently doesn’t. So what do we do?
3. The IDENTITY_INSERT setting
As I mentioned earlier, we’re basically going to do a poor man’s update:
- Delete the bad row
- Re-insert the row with the correct data
The statement to delete the bad row is simple. We’re just going to run a simple DELETE statement:
DELETE FROM Customers WHERE CustID = 4
And for God’s sake folks, DON’T FORGET THE WHERE CLAUSE!
Ok, that was easy.
So now we need to talk about our new INSERT statement. What we want to run is an INSERT statement like this:
INSERT INTO Customers (CustID, FirstName, LastName) VALUES (3, 'Jack','Cloverfield')
In this statement, we specify the CustID we want this row to have.
But if we try to run this statement, we get the following error message:
Cannot insert explicit value for identity column in table ‘Customers’ when IDENTITY_INSERT is set to OFF.
By default, we can’t simply specify a new IDENTITY value. However, if we change a certain setting in the table, it can be done.
The error message gives us a clue for what that setting is: IDENTITY_INSERT. If this setting is off, which it is by default, we cannot specify an IDENTITY value on inserts. However, if it’s on, we can specify an IDENTITY value on inserts.
Turning this setting to the “on” state is simple. The syntax is this:
SET IDENTITY_INSERT <tableName> ON
So in our case, the statement will be this:
SET IDENTITY_INSERT Customers ON
Ok, so once we run that, we can now perform our INSERT:
Awesome, now it’s good!
Don’t forget to turn IDENTITY_INSERT back OFF when you’re done!
We need to turn the setting back off when we’re done:
SET IDENTITY_INSERT Customers OFF
If we forget to turn this setting off, every insert will require that we provide the CustID value. This defeats the purpose of the IDENTITY property. So don’t forget to turn the thing back off when you’re done!
4. DBCC CHECKIDENT function
Ok, so now our new row has the CustID value it needs to have.
But there is one more tool I need to tell you about that you might need to use.
Internally, the IDENTITY property maintains the last value that was generated for the table. If we remember, the last value that was automatically generated for our Customers table was a value of 4.
We can prove it using the DBCC CHECKIDENT function. The syntax is like this:
DBCC CHECKIDENT (<tableName>, NORESEED)
In our case, the statement would look like this:
DBCC CHECKIDENT ('Customers', NORESEED)
This function very simply shows us the current IDENTITY value for the table in question. Here’s what we get if we run that query:
Ok, so let’s think about what will happen if we try to insert another row into our table. Here’s a reminder of what the table has in it now:
Since the current identity value is 4, that means if we insert another row, our next identity value will be 5.
Maybe that’s ok? But maybe it isn’t. This is why I say we might need to use another tool.
Let’s say our business rule says our CustID values cannot contain gaps.
If we want the next identity value to be 4, we basically need to reset the “current identity value” to a value of 3.
The tool we need to use is the DBCC CHECKIDENT function again. The syntax will be a bit different this time:
DBCC CHECKIDENT(<tableName>, RESEED, <new seed value>)
In our case, it will look like this:
DBCC CHECKIDENT('Customers', RESEED, 3)
So again, we’re hacking the settings to say the last identity value generated was 3.
Great. So if we run that statement, then do another INSERT statement, we see there is no gap in the next identity value:
Awesome. But I need to say it again: You would only need to run the CHECKIDENT function with the RESEED option if you care about your IDENTITY values. Again, if we didn’t do that, there would have been a gap in our CustID values, but maybe that’s ok.
5. Final Thoughts
Folks, in my opinion, if you end up needing to change IDENTITY values regularly, then there is something wrong with the design of your table. Whether it’s a bad business rule or the table was designed poorly, something probably needs to change.
If you have a column whose values might need to change, or if you actually care about what values are in the column, maybe DON’T use the IDENTITY property for that column. You can see how difficult it is if we need to change a value. One could consider it a limitation on the IDENTITY property.
But the IDENTITY property is a good option if you want a column to get automatically populated with an integer value, and you don’t really care what that integer value is.
In our example, all that really matters is that the CustID value is unique. Luckily, the PRIMARY KEY constraint on the column enforces that (the IDENTITY property does not enforce uniqueness). As long as the value in our column is unique, I don’t really care what the number is.
Next Steps:
Leave a comment if you found this tutorial helpful! Also, make sure you…
Download your FREE 1-page Simple SQL Cheat Sheet on the IDENTITY property!
Everything discussed in this tutorial is condensed into a single 1-page document for you to reference. Make sure you get it!
I also have a full tutorial on everything you need to know about the IDENTITY property. Make sure you check it out to fully understand all the different factoids about this tool:
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, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
