(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.
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL