identity column

Hi,
I have a quesion. When I create a indentity colunm named
as [ID] in one new table, I think the number should be
consecutive, like "1, 2, 3, ...". However some users
reported that the number jumped, like "1, 2, 3, 5, 6 ...",
4 is skipped.
I asked one of my friends good at SQL, and he said that
the identity field isn't reliable. Sometimes cancel action
or roll back will cause the number skipped. Is that true?
What's the best way that I could get the consecutive
number? We really need it. Thanks.
CindyYour friend is correct - an insert that is rolled back will still consume
the next identity value. This is a FAQ - a newsgroup search (suggest
.programming rather than .server) will yield some alternatives. I think
there is a KB on the topic in MSDN.
"Cindy" <cindy@.atfreeweb.com> wrote in message
news:018801c3a56e$472fdad0$a601280a@.phx.gbl...
> Hi,
> I have a quesion. When I create a indentity colunm named
> as [ID] in one new table, I think the number should be
> consecutive, like "1, 2, 3, ...". However some users
> reported that the number jumped, like "1, 2, 3, 5, 6 ...",
> 4 is skipped.
> I asked one of my friends good at SQL, and he said that
> the identity field isn't reliable. Sometimes cancel action
> or roll back will cause the number skipped. Is that true?
> What's the best way that I could get the consecutive
> number? We really need it. Thanks.
> Cindy