identity value gaps

Hi,
In production system we have a table which takes orders, order-id is defined
with identity(1, 1) as part of table defination. Problem is that most of the
time they see order-id's in sequence, but some time they see gaps in
order-id's , so busines rep asking me what happened with gaps. I asked
developers, are there any deletes or is it transactionalised. But they told
me that it is very simple application, have one straight forward Insert
command. I am wondering how can i track id gaps. I dont want to go with
alternate solution, only i need is an answer to business rep why there are
gaps. It is happening once in a month. Let me know if you need more info
Thanks,
Subbu.This is not a problem. The identity does not guarantee that you will get no
gaps. In the event that an INSERT occurs in a transaction, the identity it
would have taken does not get re-used.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uVWDAo9ZGHA.3532@.TK2MSFTNGP05.phx.gbl...
Hi,
In production system we have a table which takes orders, order-id is defined
with identity(1, 1) as part of table defination. Problem is that most of the
time they see order-id's in sequence, but some time they see gaps in
order-id's , so busines rep asking me what happened with gaps. I asked
developers, are there any deletes or is it transactionalised. But they told
me that it is very simple application, have one straight forward Insert
command. I am wondering how can i track id gaps. I dont want to go with
alternate solution, only i need is an answer to business rep why there are
gaps. It is happening once in a month. Let me know if you need more info
Thanks,
Subbu.|||"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uVWDAo9ZGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Hi,
> In production system we have a table which takes orders, order-id is
defined
> with identity(1, 1) as part of table defination. Problem is that most of
the
> time they see order-id's in sequence, but some time they see gaps in
> order-id's , so busines rep asking me what happened with gaps. I asked
> developers, are there any deletes or is it transactionalised. But they
told
> me that it is very simple application, have one straight forward Insert
> command. I am wondering how can i track id gaps. I dont want to go with
> alternate solution, only i need is an answer to business rep why there are
> gaps. It is happening once in a month. Let me know if you need more info
>
Basically any time an IDENTITY is generated, it's "used up".
So if someone generates an order, but say cancels it before it's completed,
that IDENTITY value is still used up.
Identity columns should NOT be used in almost all cases for an externally
used identifier for this and other reasons.
Imagine for example you BCP the data out, truncate the table and then BCP it
in (very rare to do, but not unheard of).
Your data will NOT have gaps now (or if it does, they may be different.)
This means all your Order-IDs are now "wrong".

> Thanks,
> Subbu.
>