Another difference


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Another difference

Hybrid View

  1. #1
    ajenny Guest

    Another difference


    (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.

  2. #2
    Chris Hylton Guest

    Re: Another difference


    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.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center