[DUG] MSSQL Question

[Neven MacEwan]

Kyley

my experience is with postgreqsl, it has sequences (I assume from its 
oracleness), which get tied to a column via the serial type.

I'm puzzled as to where you would use a Sequence/Generator in a non-pk 
situation, unless you
are talking about your 'documentno' column, which as shown can be done 
via a stored proc

I wonder is mssql eventually will allow functions to update tables (or 
code a primative sequence generation)
which would allow this

Neven

> The main difference is that a Generator is more like an Oracle Sequence, 
> It is table independant, which allows you to use the same sequence 
> accross more than one table,
> and you can also have more than one per table. I find the identity is 
> only useful if it is to be a primary key, not useful as a data element.
>
> On Mon, Dec 1, 2008 at 5:12 PM, Neven MacEwan <neven at mwk.co.nz 
> <mailto:neven at mwk.co.nz>> wrote:
>
>     John
>
>     Really interbase generators are more akin to the mssql identity column
>     type, the problem with mssql identity columns WAS
>     with the @@Identity variable which was per connection (and hence
>     triggers that inserted other identity columns not return the identity
>     you would expect),  this has been 'fixed' with the
>     SCOPE_IDENTITY() function
>
>     Stangely identity columns and generators behave the same way, ie they
>     are both not rolled back by a trans
>
>     PostgeSQL, uses a combo of both a column type of 'serial', which auto
>     generates a 'sequence'
>
>     Of course I could be spouting BS.
>
>     Neven
>     //
>     > Whats others opinion on the merits of trggers etc as discussed
>     here vs the
>     > Firebird/Interbase method of using generators?
>     >
>     > I have often wondered if this is an area where
>     Firebird/Interbase has a less
>     > convenient but more transparent mechanism - where you have to fire a
>     > generator to get a new key value before you start putting data
>     in a new
>     > record.
>     >
>     > And the generator never rolls back even if the transaction does
>     - avoiding
>     > the lock problem.  Mostly in
>     > my experience this is fine, except some times when the ID
>     numbers (eg batch
>     > numbers) are supposed to be strictly sequential and then you
>     have to get the
>     > next value from a SQL query, and make sure no-one else is
>     running another of
>     > the same batch at the same time.  (In those situations the main
>     thing that
>     > protects the database is that only one staff member ever runs
>     this batch
>     > operation - I bet thats how a lot of databases run in practice)
>     >
>     >
>     > John
>     >
>     > _______________________________________________
>     > NZ Borland Developers Group - Delphi mailing list
>     > Post: delphi at delphi.org.nz <mailto:delphi at delphi.org.nz>
>     > Admin: http://delphi.org.nz/mailman/listinfo/delphi
>     > Unsubscribe: send an email to delphi-request at delphi.org.nz
>     <mailto:delphi-request at delphi.org.nz> with Subject: unsubscribe
>     >
>     >
>     >
>
>     _______________________________________________
>     NZ Borland Developers Group - Delphi mailing list
>     Post: delphi at delphi.org.nz <mailto:delphi at delphi.org.nz>
>     Admin: http://delphi.org.nz/mailman/listinfo/delphi
>     Unsubscribe: send an email to delphi-request at delphi.org.nz
>     <mailto:delphi-request at delphi.org.nz> with Subject: unsubscribe
>
>
>
>
> -- 
> Kyley Harris
> Harris Software
> +64-21-671-821
> ------------------------------------------------------------------------
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject: unsubscribe