| Author |
Topic |
|
R
Constraint Violating Yak Guru
328 Posts |
 Posted - 2011-08-25 : 12:07:56
|
| Today I saw an enormous jump in the values of a user table in our app. It's gone from 3500 or so to 20,000.I have discovered that a user repeatedly tried to upload data that wasn't verified/correct, and so the app correctly caught the errors and rolled back the transactions.Now however, I have large 'gaps' in these numbers. Is there any way to get SQL 2005 to go back and fill them in?Thanks |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-08-25 : 12:14:33
|
| My first take is "who cares if there's gaps?"But, just in case there is some valid reason for concern, use DBCC CHECKIDENT(TableName, RESEED, newSeedValue) |
 |
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-08-25 : 12:27:03
|
quote:
My first take is "who cares if there's gaps?"
Because there's only me to fix it and I want to know exactly what's going on in there.Moreso, because our app only allows uploads of data in batches of 300 or less. So, I am absolutely baffled as to why we'd see a jump of 3000-4000 in an IDENTITY(1,1) column. I can only assume that ASP.NET has missed some data error that SQL has picked up on and rolled back? Or, is there another reason why this might happen please? As we've only got 2500 users in the table, and the ID seed is now at 20,000 I'd rather it didn't get any more carried away.quote:
But, just in case there is some valid reason for concern, use DBCC CHECKIDENT(TableName, RESEED, newSeedValue)
Okay great. So I assume that given the following existent ID values...34563457567856795680...that SQL would go back to value 3458 until it reached 5677, then jump out of the 'gap' back to 5681?Secondly, is there a way to know the value of the newSeedValue parameter without looking through all the rows for huge gaps?Thanks. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-08-25 : 12:31:37
|
| No, you'll need to reseed it again once it reaches the upper limit of the "hole." Else you'll get errors. If you really want to fill it in, will need to do so manually.You say there's only you to "fix it" but gaps in identities don't mean "broken" to me.A rolled back transaction will still increment the identity seed.Should have a look at DBCC CHECKIDENT() |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-08-25 : 12:33:08
|
| Also, I would troubleshoot the rolled back transactions, and ignore the gaps in identity values. They won't harm anything unless you have some (poor) code that depends on them all being filled in, or you made the column a smallint and are at risk of running out of numbers.EDIT: By the way, DELETES will cause gaps too. |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-08-25 : 12:44:40
|
| HiThanks for that. I'd actually just finished reading the CHECKIDENT() page and had seen this: "If a PRIMARY KEY or UNIQUE constraint exists on the identity column, error message 2627 will be generated on later insert operations into the table because the generated identity value will conflict with existing values.".I appreciate DELETEs will cause gaps, and now I am aware of ROLLBACKs causing the same. There's no danger of smallInts or (poor [touch-wood]) code, but I was worried whether huge IDs would affect performance in years to come?Other than what you've covered, is there any other reason for identity number jumps? If not, I'm going back to get the app to start emailing me the failed upload attempts so I can see exactly what's happening...Thanks again. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-08-25 : 12:57:12
|
None I can think of. Table isn't a subscriber to a transactional publication is it?As you're doing batches of 300 at a time, you have more than 6.666 million batches before you get close to running out of numbers (assuming int data type)  |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-08-26 : 04:19:24
|
quote:
None I can think of.
Sorry, is that answer to my question about performance, or other factors that might cause the jump?quote:
Table isn't a subscriber to a transactional publication is it?
No.quote:
As you're doing batches of 300 at a time, you have more than 6.666 million batches before you get close to running out of numbers (assuming int data type)
Haha when you put it like that it doesn't seem so stressful. Again, performance is my only concern, so the more I understand how SQL behaves the better enabled I can be to change things when required. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-26 : 05:54:21
|
| Ignore the gap. Whilst you have gaps the IDs numbers are "chronological". I'm sure you don't rely on that, but it can be handy when eyeballing a list. Filling the gaps in will muddy the waters, so to speak.We reset out logging tables when they get near to the INT limit - every 4 or 5 years. That's bad enough ... |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-08-26 : 06:09:01
|
Lovely - thank you all very much for helping to calm my nerves...  |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2011-08-29 : 10:28:07
|
quote:
Originally posted by R
quote:
My first take is "who cares if there's gaps?"
Because there's only me to fix it and I want to know exactly what's going on in there.
Filling in the gaps is not going to fix anything or tell you what's going on in there.Ignore the gaps. Its a surrogate key, right?________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-08-30 : 02:49:46
|
| Yes it is a surrogate key.Reading back through the replies above, could it be possible (or is it advisable) to use DBCC CHECKIDENT() as part of the ROLLBACK function of a stored procedure. E.G. If the procedure rolls back, after the ROLLBACK, use CHECKIDENT() to remove the gap that would otherwise occur? I'm guessing not because if two people executed the procedure for a large INSERT at the same time, there could be issues with conflicting ID's...?!? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-30 : 03:03:28
|
| Nah, don;t bother.But my question would be:Why are you bothered that there are gaps? Is something dependent on the IDs be contiguous? |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-08-30 : 03:31:38
|
quote:
Why are you bothered that there are gaps? Is something dependent on the IDs be contiguous?
After reading the responses, I'm not actually that bothered now. I was surprised to see the gaps at first, and thought there was a problem. Now I understand the reasons behind it its not a big deal. My concern following the initial replies was that there would be a performance hit reading ID values that increased dramatically. I realise that's not the case now. |
|
|
|
|
'Fill in' the gaps after a huge Identity jump?