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 |
|
poser
Posting Yak Master
124 Posts |
 Posted - 2008-09-23 : 10:59:34
|
| Hello,I have an identity column but when I add a new entry it skips a number.It had been working before and 1 thru 8 are ok.When I add a new entry through the query analyzer like this:Say Employee table has 2 columnsEmpID(Identity) LastName INSERT INTO dbo.Employee (LastName)VALUES ('Anderson')When I do this it skips identity value 9 and goes to 10Any reason why this is happening?Thanks for your helpR/P |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-23 : 11:21:43
|
| Look at DBCC Checkident to reseed in BOL. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-23 : 11:37:58
|
quote:
Originally posted by poser
Hello,I have an identity column but when I add a new entry it skips a number.It had been working before and 1 thru 8 are ok.When I add a new entry through the query analyzer like this:Say Employee table has 2 columnsEmpID(Identity) LastName INSERT INTO dbo.Employee (LastName)VALUES ('Anderson')When I do this it skips identity value 9 and goes to 10Any reason why this is happening?Thanks for your helpR/P
That means somebody had inserted a record in between which got 9 and there after deleted it. so next record will only get 10.Once an id is generated, it will always continue with next value even when record has been deleted. |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2008-09-23 : 11:55:54
|
| yes as posted above its because someone has inserted a record which has used the 9 identity value and then deleted it, therefore this value will not be used again....if you really want to have the 9 value look at "set identity_insert on" in BOL and insert one record and then this value will appear, or even more extreme load all the data into a temp table then truncate the table in question and then reload all the data back into that table as truncating the table resets the identity value.there are various ways or doing it but it depends on how much of a perfectionist you really are..personally i would just leave it alone.**************************Check Check and double check it's the safest way |
|
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2008-09-23 : 12:53:09
|
| Thanks to all you for the information.I see now how it happened and it's not really a big deal.R/P |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-23 : 12:59:19
|
quote:
Originally posted by poser
Thanks to all you for the information.I see now how it happened and it's not really a big deal.R/P
welcome  AS you told its not a big deal. but if you're really concerned on it, you may use solution NeilG suggested. |
|
|
|
|
|
|
Identity Not Working