Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sherbert99
Starting Member
3 Posts |
 Posted - 2006-11-06 : 12:17:48
|
| I can't figure out why my identity column randomly skips numbers while I have it set as increment 1. I have a sql table that contains an identity column that is my primary key. I have it set as "int" type with Identity as yes, with identity increment of 1, and Identity seed 1. I noticed once it got to 43 the id is now skipping numbers every now and then. For example when I look at the data input I notice it just skipped "104 and 105 so it's like this 102,103, 106, 107,108... It doesn't always skip numbers it's like every 15 rows or so. No definite pattern though, it happens at random times.Here is what I have done that may have caused the problem?I use asp code to insert data into it which works fine. I ran about 80 tests so my ID was on 80 so I wanted to start it over so I deleted all 80 rows. then I deleted the "Identity: column and made a new one so it would start from one again. Maybe that would have caused it to do this? Any thoughts on why this might be happing? I don't really have a problem with the numbers being skipped. But I do want to make sure I am not losing data that people are inputing. Thanks |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-06 : 12:22:19
|
| You're not losing data - SQL Server just increments the number every time it is used and it tracks the number independently of the actual rows in the table, so it will never re-use a number. If you insert and delete that number is gone forever, if you insert and the insert fails, the numbers that would have been used are never re-used and so on. |
 |
|
|
sherbert99
Starting Member
3 Posts |
Posted - 2006-11-06 : 12:36:43
|
| Thanks for the replyOk so an insert failing does use a number. That I wasn't sure of. If failing on insert is the case could that just be because of the server or would it be something with my insert code? Maybe people are entering a bad character or something is what I am thinking. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-06 : 12:37:02
|
| Plus: Don't use IDENTITY where you must have contiguous numbers - that scenario needs to be handled a different way.If the gaps are only a "cosmetic" problem then live with them!Kristen |
|
|
|
sherbert99
Starting Member
3 Posts |
Posted - 2006-11-06 : 13:29:13
|
| Ya that's a good idea for the future if I want it to increment. I didn't think of that this time. Next time I'll great a column that inrements not using identiy. This way I wont have to reset id column like I did on this one after I had my test data in it. Thanks |
|
|
|
|
|
|
Identity column with increment is skipping numbers