Another difference

[ajenny]

(MSSQL/SYBASE) Identities and (Oracle) Sequences look interchangable at first
glance, but there is a not so obvious difference. A single sequence can be
used to provide unique key values across several tables. It is one sequence
of numbers. If a single sequence is used to generate keys for 2 tables, there
will never be a key collision either within or between the tables. If identities
are used, collisions are likely (between tables). Consider a case where identities
are used in a foreign key column that includes IDs from two tables. Such
practice requires the use of a table identifier of some sort... Don't laugh.
This is not just a "poor design". I have seen cases where this sort of difference
significantly affected porting time of perfectly functional systems.

[Chris Hylton]

Just to add one small item to this discussion...SQL Server 2000 does support
globally unique identifiers across the database...so that you can technically
identify a distinct row regardless of the table...it's a new datatype that
wasn't supported (afaik) in prior versions...

I would guess it's primary use would be to assist in maintain state across
a stateless design for a web app...but it could also be used to achieve the
same functionality as a sequence in Oracle...

Although...Oracle would allow you to have multiple sequences...whereas the
GUID in SQL Server is just one block of identifiers.

Chris

"ajenny" <ajenny@vertisinc.com> wrote:
>
>(MSSQL/SYBASE) Identities and (Oracle) Sequences look interchangable at
first
>glance, but there is a not so obvious difference. A single sequence can
be
>used to provide unique key values across several tables. It is one sequence
>of numbers. If a single sequence is used to generate keys for 2 tables,
there
>will never be a key collision either within or between the tables. If identities
>are used, collisions are likely (between tables). Consider a case where
identities
>are used in a foreign key column that includes IDs from two tables. Such
>practice requires the use of a table identifier of some sort... Don't laugh.
>This is not just a "poor design". I have seen cases where this sort of difference
>significantly affected porting time of perfectly functional systems.