May 7, 2017 at 8:45 am
Hi all,
I have something that does not make sense to me. If anyone can give me clues that will be helpful.
- I have a table called LiveGame, and first column is LiveGameID which is IDENTITY (1,1)
- I have a table called LiveGameOff (which is kind of deleted table) where I store row from LiveGame before I deleted
- LiveGameOff also has its own idenity, and of course it has LiveGameID as well
- I only have 1 single stored procedure that fill LiveGameOff, which is SPLiveGame_Delete, no where else
- Before I delete a row in LiveGame, I copy the row to LiveGameOff, then delete it
- But I have line of code in the process, to check if LiveGameID already exists in LiveGameOff. Yes this is not necessary code because LiveGameID is always unique.
The application is running ok for mote then 3 years fine no problem in this part with 3000+ rows moving from LiveGame to LiveGameOff. Today I woke up, trying to delete (using the app) a row in LiveGame. The SP did not delete it because the LiveGameID is already in LiveGameOff. So, after I scrambling around, I found that LiveGameID pick an old number that has been assign to different data. I found that this old number has been assign to 9 days ago data. So far only 1 table!
Now here my question:
- Is it possible that an IDENTITY in a table reset to old number because technical/administration reasons? Like backup-restore, damage, etc. I am the only developer and I guaranty there is no code that put LiveGameID back to certain number. I do not think the whole DB has been restored to 9 days ago backup because if that in case, I won't have the "newer data" in LiveGameOff table.
any help will be appreciated
regards
dejava
May 7, 2017 at 9:03 am
I can think of no way that the IDENTITY would have been reset 'accidentally' by some system operation. Check out DBCC CHECKIDENT for details of how it can be reset manually.
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
May 7, 2017 at 9:11 am
Sounds like your IDENTITY column has been reseeded at some point. This, like it sounds, resets the seed. So it might have reset back to a prior available number. This smal (but very unlikely)l chance might have happened when you upgraded to SQL 2016 for example (considering you said it's been operating fine for 3 years, I assume you've upgraded recently(ish)), but more than likely someone did something silly and did it manually (as Phil implied while I wrote this post!).
Personally, I think the easiest solution would be to not have an IDENTITY column on your history table, or at least the ID the row had in its original table should not be. That means you can simply insert the data as it is into our history table, without having to worry that the IDENTITY column will end up with the same value.
The IDENTITY setting should only really be used if you don't care about the numbering. If you do, then I don't think it's the right choice as you can't dictate what number will be used. For example, you have the problem of the number being reseeded. There have also means Bug (features) on prior version of SQL Server (can't recall if is was 2008 or 2012), where the IDENTITY seed value was increased by 1,000 after a server restart. I recall that one causes me a few problems. >_<
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 7, 2017 at 9:16 am
Thank for your reply. Correct, there is manual reset with:
DBCC CHECKIDENT ('[TestTable]', RESEED, X)
Where X is the new number, but I did not do it any where in my code. There is not reason I put it in my code, and there is no reason to reset to a number from 9 days ago. I have another table that saves the LiveGameID (select with DESC order)
EmailOutgoingID Subject
2393 New Live Game 3447
2392 New Live Game 3446
2391 New Live Game 3445
2390 New Live Game 3444
2389 New Live Game 3462
2388 New Live Game 3461
2387 New Live Game 3460
2386 New Live Game 3459
2385 New Live Game 3458
You see there is a number 3462 then which was 9 days ago actually, then today it started again with 3444. But here the thing. 3443 was the highest number in LiveGame table. So it is like instead of picking 3463 (the highest +1 ever created) the system picked the highest number in the table and add 1, which is 3444.
May 7, 2017 at 9:21 am
Thanks Thom. Yes, I won't worry about inserting not unique in deleted table, but we are all have assumption that IDENTITY should be unique increment numbers always 🙂
The database version is MS SQL 2008 R2. I do not know if it has been upgraded/patched recently. It is hosted by hosting company, so I will ask them if that is the case.
regards
dejava
May 7, 2017 at 9:33 am
dejava - Sunday, May 7, 2017 9:21 AMThanks Thom. Yes, I won't worry about inserting not unique in deleted table, but we are all have assumption that IDENTITY should be unique increment numbers always 🙂The database version is MS SQL 2008 R2. I do not know if it has been upgraded/patched recently. It is hosted by hosting company, so I will ask them if that is the case.
regards
dejava
My statement in regards to your upgrade is that you have posted in the SQL Server 2016 forums. SQL Server 2016 wasn't out 3 years ago, so thus there was an implied upgrade in your post. It seems, instead, you have posted in the wrong forum.
On another note, no the IDENTITY column should not be relied on to provide an incremental number. For example, during a transaction the seed might be incremented. If that transaction is then rolled back, for whatever reason, the seed will not be.
As simple example:CREATE TABLE #Sample (id INT IDENTITY,
smallnumber tinyint);
GO
INSERT INTO #Sample (smallnumber)
VALUES (1);
GO
SELECT *
FROM #Sample;
GO
--This will fail
INSERT INTO #Sample (smallnumber)
VALUES (1000);
GO
SELECT *
FROM #Sample;
GO
INSERT INTO #Sample (smallnumber)
VALUES (4);
GO
SELECT *
FROM #Sample;
GO
DROP TABLE #Sample;
GO
Notice that the ID for the smallnumber 4 is 3, even though only 2 rows exist in the table. That is because when the attempt to insert the value 1000 was made, the seed was increased, even though the insertion failed.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 7, 2017 at 9:47 am
Sorry I did not see it is for SQL Server 2016 - Administration. If anyone/admin can move it to SQL Server 2008 that will helps.
Thanks for the explanation the seed can jump. I noticed that. There was almost 0% possibility someone did reseeded because I am in the only developer/admin in this DB and of course the hosting company.
May 7, 2017 at 10:58 am
May be this one was the problem:
https://documentation.red-gate.com/pages/viewpage.action?pageId=20185801
I use SQL Compare 10, but I never had this problem before, and I do not keep the scripts that I have deployed 🙁
May 9, 2017 at 9:28 am
Your incorrect design has no data integrity, and no way to really have any. For example, what attribute in your data model is an identity? The answer is, it's not even part of the data model. It's a count of physical storage insertions based on the original Sybase mag tape system. Over the last few decades. What I've been teaching SQL, I tell people using identity is like identifying your automobile by the parking space number in one particular building, in one particular parking lot instead of using the VIN. Yes, it's that kind of silly.
Why don't you follow the forum rules, and post your DDL so we can redesign this?
Please post DDL and follow ANSI/ISO standards when asking for help.
May 9, 2017 at 9:47 am
jcelko212 32090 - Tuesday, May 9, 2017 9:28 AMI tell people using identity is like identifying your automobile by the parking space number in one particular building, in one particular parking lot instead of using the VIN
A VIN is put on a car in the factory when it's made, an identity value is put on a row in a table when it's made (inserted). Rebuilding the indexes on a table doesn't change its identity value. What you're describing in the parking lot example sounds more like the Oracle RowID, which can change value over the lifetime of the data row, not an identity or sequence number, which persists.
May 9, 2017 at 5:12 pm
No, that's not how industrial identifiers work.This is a totally different concept. Let me pull for my own personal experience. I had publishers who wanted me to write database books, so they went out and got ISBN identifiers for books that had not even been written yet. Those ISBN identifiers or retired when the book was never written.. The book literally has to exist before it has ISBN to be on back cover. However, the ISBN can exist without a book in a sort of wonderful nebulous world of "empty space lots in the catalog" of planned and on written books?
However, the VIN on a car has to physically exists, and be put in databases. If the car is never built, then this then is illegal and has a whole bunch of criminal justice stuff associated with it. Yes, I used to work criminal justice.
However, the IDENTITY Sybase table property has nothing to do with the logical data model. It was literally based on the UNIX tape filesystems in the 1970s were there was a counter of every physical record sequentially inserted into a tape drive;; later this was actually a disk oriented image of said tape drive. Trust me, I'm old 🙂
This was the major advance that RDBMS made. A unit of work (row) had an identity based on the values of the columns that made up the row. This had nothing to do with the physical low location in storage of the data; ideally, it had nothing to do the representation the scaler values of the columns of this data.
It's just so damn hard for old programmers (or really badly trained new programmers) to think and abstractions.
>>Rebuilding the indexes on a table doesn't change its identity value. What you're describing in the parking lot example sounds more like the Oracle RowID, which can change value over the lifetime of the data row, not an identity or sequence number, which persists<<
You\ missed the point of RDBMS.,
Please post DDL and follow ANSI/ISO standards when asking for help.
May 9, 2017 at 6:36 pm
jcelko212 32090 - Tuesday, May 9, 2017 9:28 AM>> VIN is put on a car in the factory when it's made, an identity value is put on a row in a table when it's made (inserted).<<
No, that's not how industrial identifiers work.This is a totally different concept. Let me pull for my own personal experience. I had publishers who wanted me to write database books, so they went out and got ISBN identifiers for books that had not even been written yet. Those ISBN identifiers or retired when the book was never written.. The book literally has to exist before it has ISBN to be on back cover. However, the ISBN can exist without a book in a sort of wonderful nebulous world of "empty space lots in the catalog" of planned and on written books?
However, the VIN on a car has to physically exists, and be put in databases. If the car is never built, then this then is illegal and has a whole bunch of criminal justice stuff associated with it. Yes, I used to work criminal justice.
However, the IDENTITY Sybase table property has nothing to do with the logical data model. It was literally based on the UNIX tape filesystems in the 1970s were there was a counter of every physical record sequentially inserted into a tape drive;; later this was actually a disk oriented image of said tape drive. Trust me, I'm old 🙂
This was the major advance that RDBMS made. A unit of work (row) had an identity based on the values of the columns that made up the row. This had nothing to do with the physical low location in storage of the data; ideally, it had nothing to do the representation the scaler values of the columns of this data.
It's just so damn hard for old programmers (or really badly trained new programmers) to think and abstractions.
>>Rebuilding the indexes on a table doesn't change its identity value. What you're describing in the parking lot example sounds more like the Oracle RowID, which can change value over the lifetime of the data row, not an identity or sequence number, which persists<<
You\ missed the point of RDBMS.,
Unless some artificial unique number or partitioned sequential number is introduced, "natural" keys for things like Employee or Customer tables will not withstand the test of time for uniqueness.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2017 at 10:03 am
jcelko212 32090 - Tuesday, May 9, 2017 9:28 AM
Unless some artificial unique number or partitioned sequential number is introduced, "natural" keys for things like Employee or Customer tables will not withstand the test of time for uniqueness.
Industry standards also don't stand the test of time 🙂 but the advantages and are maintained by some organization, they get updated. Have you started using the new version 6 ISP numbers? Have you bought a new car that has the extended VIN numbers? I wish we do something about the Social Security numbers in the United States; they are now completely random because the original design was so bad they couldn't be extended or repaired.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 11, 2017 at 11:11 am
However, the IDENTITY Sybase table property has nothing to do with the logical data model. It was literally based on the UNIX tape filesystems in the 1970s were there was a counter of every physical record sequentially inserted into a tape drive;; later this was actually a disk oriented image of said tape drive. Trust me, I'm old 🙂
I love reading about UNIX history and UNIX seems famously agnostic about file content from the get go. Sure, the OS had to deal with blocks at the device level, but unlike say VMS, UNIX just never exposed "record management services" to application programs, in fact, I don't remember UNIX ever presenting anything else other than a simple stream of bytes. Sure, you could seek and could specify a block size during your reads and writes, but you are never constrained into any particular VALUE of that block size, in fact you could read and write using a block size of 1, your program's performance would just get penalized by all the overhead of calling those system routines for each byte.
Furthermore, I don't recall ever seeing that we should treat IDENTITY as a "count" of anything, in fact I'm guessing that the best we can hope for is that for each generated IDENTITY value is that it at least can be predicted to be greater or lesser than any previously inserted value depending on settings for the table that we associate an IDENTITY column with.
May 11, 2017 at 7:46 pm
patrickmcginnis59 10839 - Thursday, May 11, 2017 11:11 AMFurthermore, I don't recall ever seeing that we should treat IDENTITY as a "count" of anything, in fact I'm guessing that the best we can hope for is that for each generated IDENTITY value is that it at least can be predicted to be greater or lesser than any previously inserted value depending on settings for the table that we associate an IDENTITY column with.
I am about to do one of my "ships of wood, men of iron" rants :). In the old days, tape drives wrote punch card images on the tape and the read write heads could only move forward. The way the early systems found the records was to start from the front and count off the position of the record on the tape (yes, things really were that primitive). This count was the basis for the identity table property. Notice the term "table property" which let you know that this was never a column, never had any meaning inside the data, and was only used as a physical locator.
In fact, if you really old SQL Server programmer, you might remember that cursors (which we very deliberately based on the IBM mag tape systems when we wrote standards) could only read forward. Later, when physical tape drives got better, cursors could then move backwards or move relative so many records, plus or minus from the current position. . Obviously with this model, identity values had to be unique, ascending when issued, and not reused in the same table. In some of the tape systems, a record would have a bit flag in front of record which would tell you whether or not the record had been deleted. Think about it; if you physically deleted things and tried to close up the gap in the tape, your performance would go to hell in a hand basket. Every now and then, you had to run the garbage collection routine to close those gaps or simply's right the active records over to a new tape.
It's a good programming exercise to sit down and figure out how to do a "polyphase merge" or and in place sort given the limits of tape. Were kind of spoiled these days with random-access files.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply