Saturday, February 26, 2011

IDENTITY Crisis

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) 

C’MON MAN!

 

 

 

 

 

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)
);
GO

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:

image

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

image

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.

2 comments:

  1. Thank you for sharing your tips in this SQL safety. I have researching about this topic. By the way, an additonal identity protection system (877)-871-1295 can supplement SQL safety methods.

    ReplyDelete