Stephen
I assume that one of the advantages of NewID() is that it is fast (or should
be)
So you could use NewID() to generate the ID and then just use the
top 4 bytes (these are sequential) in your stored proc
(in SQL2000 can a user defined procedure be called as a default?)
ie Create Table test (ID integer default foo())
As for returning the 'unused' ID's to a pool, this requires a 2 phase commit
(or a 'last gasp' check before the commit) and it is quite difficult and
performance sapping as you only have 'tables' as a global medium
Neven
----- Original Message -----
From: Stephen Bertram <[EMAIL PROTECTED]>
To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
Sent: Thursday, 11 January 2001 16:55
Subject: RE: [DUG]: [DUG-DB]: Unique IDs
> The discussion so far has brought up 2 issues for me -
> Why not use GUIDs
> Size
> Difficulty in manual SQL manipulation
> Lack of a directly mapable datatype in Delphi
> Unavailability in some databases - we interface to Ingres
> Why use integers?
> Habit
> Sequencing the order of row creation
> Size
>
> The suggestion that Gary has put forward is exactly what I was thinking
of,
> but I still don't see how to "remember" rolled back ID's.
>
> The main problem is that if I use a single row table as the basis for the
> generator, a rollback will reset the row back to the state before the
start
> of the operation, regardless of the fact that the row has been used to
> generate new ID's by other processes in the meantime. This would mean
that
> any IDs generated after this point may duplicate an ID generated during
the
> rolled back transaction. Locking the row until the transaction completes
> would introduce an unacceptable delay. I have not heard of anyway of
> exempting a table update from the current transaction, but that is really
> what I need to do.
>
> Of course GUIDs are the answer if I can find a simple way of handling
these
> in Ingres and am prepared to type in huge banks of guff in SQL when
tracing
> rows. I'll think about it.
>
> Stephen
>
>
> -----Original Message-----
> From: Gary T. Benner [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, 11 January 2001 3:48 p.m.
> To: Multiple recipients of list delphi
> Subject: RE: [DUG]: [DUG-DB]: Unique IDs
>
>
> [Reply]
>
> Your requirement needs to have a process that is not available in most
> databases. You need some logic to not only give you a
> sequential number, but to also remember "roll backed" ID's and issue those
> before generating new ID's.
>
> This usually requires the creation of a pool of available ID's, and a
> process to issue them to calling processes, and to have the issue
> confirmed (after a commit), or returned to the pool (after a roll back).
>
> You can do this primarily in the database (stored proc), as part of a
> middle tier, or in your client as appropriate.
>
> A stored procedure returning an ID value sounds the best option. It could
> contain the code to housekeep the overall process.
>
> Good Luck.
>
>
> BTW, I am curious as to why the ID's have to be sequential. As a general
> rule, primary keys in databases (sometimes called surrogate
> keys) should have no meaningful value, only be unique. If you wish to have
> some meaning associated with data, then it should be a
> separate field, eg invoice number in a financial application, where it is
> obvious that the number must be unique, and generally sequential
> - although in some cases not essential.
>
> If you are using your unique ID for some process (such as replication or
> briefcasing), then would it not be better to have the relevant
> process deal with gaps in the sequential number rather than insist upon it
> in the first place?
>
> BTW, rules are meant to be broken, and if there is a good reason for the
> uniqueness, and sequentiality, then certainly go down the
> track you are.
>
> Kind Regards
>
> Gary
>
>
>
> At 15:29 on 11/01/2001 Stephen wrote
>
> >To :
> >CC :
> >From: Stephen Bertram, [EMAIL PROTECTED]
> >I need a bulletproof routine to create unique integer IDs for inserting
> rows
> >into multiple tables in a MSSQL 7 database.
> >
> >The catches are that I want the ID's to be unique in the database, not
just
> >each table, and the generator must be available to stored procedures.
> >
> >Also the generator needs to unaffected by transactions - rollbacks should
> >not reset the sequence of values - and the generator must not be locked
by
> a
> >slow transaction.
> >
> >Any bright ideas?
> >
> >Stephen
> >
> >
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
> To UnSub, send email to: [EMAIL PROTECTED]
> with body of "unsubscribe delphi"
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
