skip to main content
Corticon Server: Data Integration Guide : Advanced EDC Topics : Relational database concepts in the Enterprise Data Connector (EDC) : Advantages of using Identity Strategy rather than Sequence Strategy
 

Try Corticon Now
Advantages of using Identity Strategy rather than Sequence Strategy
EDC offers options for assigning primary keys. For SQL Server databases, you might want an Identity strategy. For an Oracle database, you might choose a Sequence strategy. Consider the following points when deciding whether to use identity strategy or sequence strategy:
*When using the Create/Update Database Schema function in the Vocabulary, the sequences are generated automatically and tied to the table id fields on the database side. On the other hand, when using sequence strategy, the sequences are not generated during the Create/Update Database Schema process. If Corticon, at runtime, attempts to access a sequence and finds it missing, it will try to create it on the fly. But such a dynamic creation of sequences is tricky and does not always work properly.
*Using identity strategy should result in better performance when inserting a large number of records into the database. This is simply because the database I/O is cut in half since there is no need to retrieve the next unique id from the database prior to adding a new record.
*Using sequence strategy tends to not be compatible with read-only database access which may result in runtime exceptions.
*Using identity strategy makes a Vocabulary more portable across databases since not all databases support sequences.
Hibernate supports Sequence strategy for all databases; in a case where the database does not support it -- such as SQL Server -- Hibernate emulates it. However, in a case where the database does not support Identity strategy -- such as Oracle -- there is no emulation. This makes Sequence more portable.