identity fields - losing values

Hi,
I've an application that consists of a of main table. Each record requires a
numeric reference and these references must be sequential with no gaps.
At the moment, this reference field is of an identity type. This works fine
most of the time, however every now and again the identity field 'loses' a
number (for example goes from 58 to 60) which is a right pain for me.
From my limited understanding of TSQL I see two alternatives
1. Have a manual identity field which I increment manually and use locking
and error trapping to ensure that no values are 'lost'. However, I'm not
really sure how to achieve this...
2. Even if when an identity value is 'lost' it is marked as such is fine, I
simply cannot have any gaps. Can I keep the identity value in place but use
error trapping to ensure that when an insert fails the identity is marked
appropriately rather than simply 'lost'.
Which of the above methods is the recommended one (or are there any other
alternatives) and can any kind soul point me in the right direction as to
how to achieve this (for example, links, etc).
Any and all advice is gratefully received.
Kind regards
Chris.You can't really avoid gaps with an IDENTITY column. If you care about
the value inserted then IDENTITY is the wrong solution.
You haven't explained how this value is to be used. If it is to be
purely based on insertion order then maybe you don't even need it in
the table. Just add a Creation Date column, order by that and display
the row number client-side or in a query.
You can increment a value yourself on the INSERT:
INSERT INTO YourTable (x, ...)
SELECT COALESCE(MAX(x),0)+1, ...
FROM YourTable
but this effectively serialises every INSERT, which may not be
acceptable in a multi-user environment. Logically there isn't a way out
of this: You can't allow concurrent updates if you need to maintain a
serial key in real-time because a rolled-back transaction will always
leave a gap.
David Portas
SQL Server MVP
--|||> 2. Even if when an identity value is 'lost' it is marked as such is
fine, I
> simply cannot have any gaps. Can I keep the identity value in place
but use
> error trapping to ensure that when an insert fails the identity is
marked
> appropriately rather than simply 'lost'.
If you mean that you just want to show the missing values in the data,
you can do so like this:
SELECT
N.num AS id, T.*
FROM Numbers AS N
LEFT JOIN YourTable AS T
ON N.num = T.id
WHERE N.num BETWEEN 1 AND 999999999
Where Numbers is a table containing the total range of IDs.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1112956106.268466.113170@.f14g2000cwb.googlegroups.com...
> You can't really avoid gaps with an IDENTITY column. If you care about
> the value inserted then IDENTITY is the wrong solution.
> You haven't explained how this value is to be used. If it is to be
> purely based on insertion order then maybe you don't even need it in
> the table. Just add a Creation Date column, order by that and display
> the row number client-side or in a query.
> You can increment a value yourself on the INSERT:
> INSERT INTO YourTable (x, ...)
> SELECT COALESCE(MAX(x),0)+1, ...
> FROM YourTable
> but this effectively serialises every INSERT, which may not be
> acceptable in a multi-user environment. Logically there isn't a way out
> of this: You can't allow concurrent updates if you need to maintain a
> serial key in real-time because a rolled-back transaction will always
> leave a gap.
> --
> David Portas
> SQL Server MVP
> --
>
David,
Many thanks for your reply. For some background, the reference is simply a
way of identifying each record which as you suggested is based on insertion
order (although to be honest - this isn't terribly important - it wouldn't
be a problem for record X to have a lower ID than Y even if X was inserted
after Y).
I realise that ideally this wouldn't be a requirement as long as the ID is
unique but there is some inertia from another department who see gaps as
indicating missing records (and despite my best efforts can't be convinced
otherwise).
Based on what you've said, how does this sound:
Have a IDStore table. Two fields, "ID" and "committed". When an insert is
attempted, the first ID is found which is uncommitted and this is used. If
no uncommitted ID's are found then a row is inserted into the IDStore table,
with the next free ID and an uncommitted value. When the insert is
successful, the committed field is marked as committed. If it is
unsuccessful it is marked as uncommitted. And so on.
Assuming that I've managed to explain it clearly, does this sound like a
reasonable approach? I realise that I'll have to investigate locking and so
on but at least its something I can work towards..
Once again, your advice is gratefully received.
Chris.|||This is a risk whenever you expose IDENTITY to users. The IDENTITY
value acquires business meaning for the users and then you are lost
because there are too many scenarios in which you can't control the
IDENTITY value. The best policy is not to expose IDENTITY to users at
all.
Assuming the users don't have direct access to the tables (not usually
a good idea to allow this anyway), just don't display the IDENTITY,
which after all should only be used as an artificial key. I assume your
table has an alternative, business key as well. If not then you have a
more serious design problem. IDENTITY should never be the only key of a
table.
If the users need the "comfort factor" of seeing the numbers then see
my other reply on how to fill in the gaps. As you suggest, there are
alternative strategies but they all involve serializing the inserts, or
accepting that there will be gaps.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1112958821.065794.173610@.o13g2000cwo.googlegroups.com...
> This is a risk whenever you expose IDENTITY to users. The IDENTITY
> value acquires business meaning for the users and then you are lost
> because there are too many scenarios in which you can't control the
> IDENTITY value. The best policy is not to expose IDENTITY to users at
> all.
> Assuming the users don't have direct access to the tables (not usually
> a good idea to allow this anyway), just don't display the IDENTITY,
> which after all should only be used as an artificial key. I assume your
> table has an alternative, business key as well. If not then you have a
> more serious design problem. IDENTITY should never be the only key of a
> table.
> If the users need the "comfort factor" of seeing the numbers then see
> my other reply on how to fill in the gaps. As you suggest, there are
> alternative strategies but they all involve serializing the inserts, or
> accepting that there will be gaps.
> --
> David Portas
> SQL Server MVP
> --
>
David,
Thanks again for your reply. Based on your advice, I have redesigned the
table in question so that the IDENTITY field is internal to the system and
not visible by the user.
I have created an additional, INT field in this table which will be used for
the reference and will be gapless and sequential - although I've yet to
figure out exactly how to achieve this (but I see triggers in use)...
Thanks once again,
Chris.|||CHris,
Does this new field (we say "column" in SQL btw) have to be immutable?
Will they throw a fit if the record 227 becomes record 226 after someone
deletes record 200?
Will it be impossible to delete records from the table?
If any of these answers are NO, And if the performance hit is acceptable,
then I might suggest that you not persist a value in this new column at all,
Just calculate it on display, as the Count of all other records in the table
with Identity Value <= IDentity value of the record you're displaying
Select <Other COls>,
(Select Count(*) From Table
Where ID <= T.ID) As RowID
From Table T
"Chris Strug" wrote:

> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1112958821.065794.173610@.o13g2000cwo.googlegroups.com...
> David,
> Thanks again for your reply. Based on your advice, I have redesigned the
> table in question so that the IDENTITY field is internal to the system and
> not visible by the user.
> I have created an additional, INT field in this table which will be used f
or
> the reference and will be gapless and sequential - although I've yet to
> figure out exactly how to achieve this (but I see triggers in use)...
> Thanks once again,
> Chris.
>
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:992173F7-F444-4C7E-821A-A18B4F385944@.microsoft.com...
> CHris,
> Does this new field (we say "column" in SQL btw) have to be immutable?
> Will they throw a fit if the record 227 becomes record 226 after someone
> deletes record 200?
> Will it be impossible to delete records from the table?
> If any of these answers are NO, And if the performance hit is acceptable,
> then I might suggest that you not persist a value in this new column at
all,
> Just calculate it on display, as the Count of all other records in the
table
> with Identity Value <= IDentity value of the record you're displaying
> Select <Other COls>,
> (Select Count(*) From Table
> Where ID <= T.ID) As RowID
> From Table T
Hi!
Unfortunately the answer to all your questions is a resounding "yes!".
However, the example you mention is something that I've crossed before and I
feel will be very useful sooner or later.
Thanks for your advice though!
Chris.