Trying to determine what might be causing skipped values in an IDENTITY column
-
I've got a tough problem which comes back occasionally to plague me. I have to help maintain a legacy MS Access app that's used as a front-end to a SQL Server database. For reasons I don't understand the manager for the department has a fixation on the numbering in one of the tables. The table has an IDENTITY column in it and its that IDENTITY column that he gets upset about. He gets very bent out of shape if the numbering goes from 1007 to 1009, for example. I've told him this will happen if someone deletes a record from the table. Since telling him that I've learned that SQL Server may also skip a number if an attempt to insert a record into the table fails. The attempt of inserting a record into a table with an IDENTITY column causes SQL Server to increment the counter for the IDENTITY column, even if it doesn't get saved to the table, for whatever reason. That can explain some of the "missing" data. (From this guy's point of view the data is missing. More likely, if the user tried to enter data and it then raised an error, they would try to enter the data again correcting whatever it was they left out, this time succeeding. Nothing is really "lost", its just this guy gets bent out of shape if there's a break in the sequence of numbers.)
However, this week he hit me with something I've got no explanation for. He claims that there's a gape of 1000 in the numbering. (Maybe its 10,000, I don't remember.) I've not had a chance to verify this, because he's then thrown 3 other unrelated issues at me, hit the panic button, brought attention to this to the CIO, etc., so I'm fighting 4 fires at once, all from him. But, if his claim is correct, and I've no reason to doubt it, that seems very fishy to me. How does 1000 (10,000?) records or potential records, suddenly go missing?
So I thought, why not make another table, an audit table, that is comprised of all of the columns from the original table, but with some additional columns. This new audit table would take the IDENTITY column from the original table, make it just an INT column in the audit table and have the record written into the audit table via a delete trigger on the original table. The audit table would have its own ID column that would be an IDENTITY column, and it would also have 2 more additional columns, one for the user who performed the deletion and another for the date/time that the record was deleted from the original table. I am sure that for this to happen in a delete trigger on the original table, the user must have INSERT permissions granted on the audit table. I thought it might also be a good idea to allow SELECT, but that's all. I don't want to give the users either UPDATE or DELETE permissions on the records of the audit table. I hate to sound a bit paranoid, but these users can get into the database directly via the MS Access app. I know for a fact that the manager will do that if he thinks the data in the tables are incorrect for some reason and the app doesn't give him the ability to make corrections through the app. (I am not an Access developer; someone long before I came on the scene, wrote this thing up. I have to live with it.) Now, I'll be quick to say that I'm not saying that someone is being malicious and deleting records at will. More likely, if they get into the Access app, then while they're in it they bang out of the app but stay within MS Access to get access to the tables directly. They could easily open up the table in table view (I think that's what MS Access calls it), accidentally select a huge selection of records and hit some key that deletes the records.
So, am I correct about setting the permissions on the audit table? Is it the case that all they need is INSERT (at the very least) and SELECT permissions, for a delete trigger to write deleted records into the audit table?
(One last thing, I realize that an audit table will not capture failures to insert a record into the original table. I'm just trying to do baby steps here.)
Kindest Regards, Rod Connect with me on LinkedIn.
-
Could it be this?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Thursday, August 17, 2017 11:35 AM
Very likely.
Essentially, SQL has a cache of identity values, and when SQL restarts, that cache is discarded and the next number is used. You'll have a gap of about 1000 as a result.NOTHING about identity guarantees gapless sequence. It doesn't guarantee uniqueness either.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - Phil Parkin - Thursday, August 17, 2017 11:35 AM
Good question. Although in my original post I just came up with 1007 and 1009. The actual values were more like 20550 and 20552.
Also, I just checked and there is 17,770 records in total in the table.
Kindest Regards, Rod Connect with me on LinkedIn.
- Rod at work - Thursday, August 17, 2017 11:56 AM
Comply with what? Phil posted a link to Microsoft's Connect site (bug report/feature request)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Thursday, August 17, 2017 12:00 PM
I made a mistake and tried to delete my reply. I couldn't, so I modified it. Between the time I did that and you posted this, I modified my reply.
Kindest Regards, Rod Connect with me on LinkedIn.
- GilaMonster - Thursday, August 17, 2017 11:52 AM
Now this is very interesting. That would explain that huge gape easily. Would this cache of identity values being lost due to a rebooting of the server happen regardless of how many records are in the table and what the highest value of the IDENTITY column is?
Kindest Regards, Rod Connect with me on LinkedIn.
- Rod at work - Thursday, August 17, 2017 12:08 PM
Yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
So, your bossy man doesn't want any gaps in this sequential ID column, even after rows are deleted? The next time you catch him in a relaxed mood, ask him to explain how this ID column is being used. It's possible the application isn't really using it as an actual identiifer but rather for some functional purpose like pagination. If the only requirement is that a resultset selected from the table have this ID column populated with sequential and contiguous numbers, then consider implementing a view containing a computed ROW_NUMBER column. For example, the sys.objects table contains an identity column, but there are gaps created whenever objects are deleted. Below I've created a view called vObjects that includes a new column row_id.
CREATE VIEW vObjects AS
SELECT name, object_id
, ROW_NUMBER() OVER (ORDER BY object_id) AS row_id
FROM sys.objects;
GO
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply