I was recently presented with the task of examining a database, from historic backups, to determine transactions applied over the past 6 years. Unfortunately, the only background/source data available was a few weeks of full backups and, to make this more of a challenge, many conclusions had already been reached. The most notable conclusion focused on alleged malicious deletions.
After some discussion I found that the primary basis for concluding that deletions had been made were the “gaps” in the IDENTITY seed (I am hoping to get this submitted to ESPN’s “C’MON MAN!” for next season)
An IDENTITY seed is not meant and CAN NOT provide proof of a deletion. To provide a simplistic example that details some similarities to the data structure in question the below code creates two tables:
- Category table
- Primary key is an IDENTITY seed
- Has a one to many relationship to the primarydetails table
- Primarydetails table
- Primary key is an IDENTITY seed
- catID column has a foreign key restraint to the category(catID) table
USE tempdb; GO CREATE TABLE category( catID INT IDENTITY PRIMARY KEY, catdescrip VARCHAR(20)
); CREATE TABLE primarydetails( ID INT IDENTITY PRIMARY KEY, descr VARCHAR(20), catID INT REFERENCES category(catID)
For each INSERT on a table with an IDENTITY column the value is incremented based on the increment value, in this example the default seed and increment values of 1 are used.
*if a seed and increment are not provided then the default is that the column begins at 1, the seed, and increments by 1
The following code will insert a row into the category table and a row into the primarydetails table:
INSERT category VALUES('A plan to fail'); GO INSERT primarydetails VALUES('This''''ll work', 1); GO
SELECTing from either the category or primarydetails table will show that the IDENTITY column for each is set to 1, the single row.
The IDENTITY value is incremented at the beginning of the INSERT and will not decrement on error. This can be replicated by attempting to INSERT data that would result in truncation in the primarydetails(descr) column:
INSERT primarydetails VALUES('This is way too long to be inserted in to this data type and char length', 1); GO
The above statement will fail, but will result in the IDENTITY column being incremented. To display this “missing” value another successful INSERT can be made and then SELECT from the primarydetails table to show the results.
INSERT primarydetails VALUES('2 was already used', 1); GO SELECT * FROM primarydetails p JOIN category c ON p.catID = c.catID; GO
The results should like like this:
From the results it is obvious that there is a gap in the IDENTITY, but this is not the result of the “2” ID row being deleted.
The truncation error that I demonstrated can easily be handled on the client side, which was quickly brought up, but keep in mind that the more constraints and keys on the table can also result in a failed transaction and incremented IDENTITY if not handled client side.
After reviewing the target table I found that there was a cascading trigger for AFTER INSERT, UPDATE, just one out of the 71 triggers in the database, that was more than 200 lines of t-sql code consisting of numerous IF statements to handle control flow. Apparently the client side validation was being handled server side in the trigger (after all triggers are like cursors and puppies and behave better if you have more than one so that they entertain each other).
So for arguments sake let’s assume that the INSERT does not terminate early based on violation of referential integrity, truncation, check constraints etc. The INSERT command fires the trigger, again all is right in the world and there are no issues buried in the 200 lines of t-sql, and based on the IF statements another transaction is begun to another table that contains a trigger, which in turn fires another trigger, which……..
Enough venting. I wont delve into how many levels this trigger dives, suffice it to say that a failure at any level results in a failed transaction and a “gap” in the IDENTITY.
Amazingly enough the question was asked about what happens to those “lost” identities? Are they gone forever? I never thought of it that way. It is a bit sad that they never even had a chance to establish themselves before becoming extinct in their table.
The only way to explicitly assign an IDENTITY value is to SET IDENTITY_INSERT schema.table ON for the transaction.
SET IDENTITY_INSERT dbo.primarydetails ON GO INSERT primarydetails(ID, descr, catID) VALUES(2, '2 is now used', 1); GO SELECT * FROM primarydetails p JOIN category c ON p.catID = c.catID; GO
Easy as that number 2 is back from the abyss. There are a few limitations, such as the specified table being used for replication and that only one table can have IDENTITY_INSERT set to ON in a session.