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 |
|
Jusvistin
Yak Posting Veteran
81 Posts |
 Posted - 2003-08-16 : 05:54:56
|
| Ok, seems odd to me.Why is the identity field incremented even when an attempted insert is rejected ? I was playing around with a constraint and noticed that the identity field in my table was incremented, even though the inserts were rejected.Anybody know why it works like that ?Kevin |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-08-16 : 09:51:37
|
| I think that before deciding to reject/to accept changes the systemfirstly forms the entire row (so it must generate new identity value),then it inserts the row and after the inserting it checks for constraintsviolations. And if any are there then the system deletes that row.In some sense that bad row is inserted but right after that it's deleted. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-16 : 13:33:27
|
| The identity is allocated before the insert so if it fails the seed is already updated.Note that there is no guarantee that identities will be consequtive or even unique - just that the next one from the seed will be allocated.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Ident field incremented - though record rejects ?