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 |
|
ballabhoks
Starting Member
18 Posts |
 Posted - 2004-03-19 : 01:09:34
|
| Dear All,I have a table in which I have an identity column filed. If I try to insert a record in table and if records is not inserting. Again if i try to insert a record in the same table and record getting inserted successfully, but value in identity column is getting incremented by one. i.e. :- Before failure of a record the value in identity column was 45. but after successful insertion, the value in identity column becomme 47 not 46.Please help......., since this is very urgent for me.Thanks in advance. |
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-19 : 01:43:57
|
This is expected behaviour. Identity values won't get recycled, as you've found out. So sooner or later you will almost always have gaps in that sequence.Is this a problem for you?--Frankhttp://www.insidesql.deApologize for editing. Shouldn't write before drinking coffee  |
 |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-03-19 : 08:03:57
|
| You could change your identity column to a regular Int.Then create a SP that inserts the record.(if you are not already useing one) That will look at the ID column and Incriment.Example Declare @tempID IntSelect @tempID = Max(IDfield) + 1from dbo.tableInsert Into Dbo.table(Idfield,...,...,...ext)Select @tempID,...,...ext)From (......)This will prevent skipsJimUsers <> Logic |
|
|
|
|
|
|
identity column