Menu
Is it better to use Sequences in oracle and auto-increment a column using Before insert trigger or to use identity columns as it is available in Oracle 12 c?
914
The IDENTITY property is tied to a particular table and cannot be shared among multiple tables since it is a table column property. On the flip side the SEQUENCE object is defined by the user and can be shared by multiple tables since is it is not tied to any table.
What is the Difference Between Sequence Objects and Identity Columns in SQL Server? The Identity property is a column property meaning it is tied to the table, whereas the sequence is a user-defined database object and it is not tied to any specific table meaning its value can be shared by multiple tables.
An INTEGER, LONG, or NUMBER column in a table can be defined as an identity column. The system can automatically generate values for the identity column using a sequence generator. See Sequence Generator section. A value for an identity column is generated during an INSERT, UPSERT, or UPDATE statement.
An Oracle Sequence is a database object, just like a table or view, that represents a sequence of integers that can be used by any table or view in the global database namespace. A Sequence's values can be accessed using the NEXTVAL, and CURRVAL pseudo-columns. A Sequence can be ascending or descending.
Either way you're going to be using Sequences. Identity columns in 12c use the same mechanism behind the scenes.
The benefit - the db is managing these objects, and not you.
The feature was introduced to aid in the migration of systems from places like SQL Server and Sybase ASE where these were popular. Much simpler to migrate as is than create a sequence/trigger pair to maintain going forward.
And that benefit extends to regular Oracle customers as well. The flexibility of the IDENTITY clause includes everything you can set when manually defining a sequence.
93
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
