The first part of this series discussed the differences between Oracle Database and Microsoft SQL Server in their implementation of transactions, with a focus on pitfalls one may encounter during Oracle to SQL Server migrations and vice-versa. This next installment will cover a number of commonly used SQL syntax elements that have no match or have a completely different meaning or usage across the Oracle–SQL Server divide.

Sequences in Oracle and Identity Columns in SQL Server
There’s a long-standing divide in the database community between two camps: the patriots of natural keys and the proponents of artificial (or “surrogate”) keys.

I myself defend natural keys but often find myself creating surrogates for one reason or another. But leaving the substance of this debate aside, let’s look at the standard mechanisms for generating artificial keys: Oracle sequences and SQL Server identity columns.

An Oracle sequence is a first-class database-level object. In contrast, an SQL Server identity column is a column type, not an object.

When an Oracle sequence is used to generate a table key—usually a primary key—it’s guaranteed to increment, and therefore be unique. But it’s not guaranteed to be consecutive. In fact, even in well-designed implementations, it’s most likely to have some gaps. So no Oracle implementation should ever rely on sequence-generated values to be consecutive.

Also, a sequence is managed through an Oracle database’s data dictionary, so it would be too resource-consuming (and cumbersome) to create a dedicated sequence to support each and every surrogate key. A single sequence object can support multiple or even all surrogate keys.

On the other hand, when multiple processes need to access NEXTVAL (the next incremental value) from a sequence, the sequence will become a critical, single-access resource. It will effectively make all processes accessing it strictly sequential, turning any multi-threaded (single or multi-server) implementation into a single-threaded process, with long wait times and high memory/low CPU use.

Such implementations do actually happen. The solution for this issue is to define the sequence object in question with reasonable cache value—meaning that a defined range of values (be it 100 or 100 thousand) are selected into a cache for a calling process, recorded in the data dictionary as used, and become available for this particular process without the need to access data dictionary every time the NEXTVAL is called.

But this is exactly why gaps will be created as not all cached values are likely to be used. It also means that across multiple processes in parallel sessions, some recorded sequence values can be chronologically inverted. This inversion can’t happen within a single process unless a sequence value was reset or backshifted. But this last scenario amounts to looking for trouble: It should be unnecessary, and if implemented incorrectly, it can result in generating duplicate values.

So, the only correct way to use Oracle sequences is for the generation of surrogate keys: keys that are unique but are not assumed to hold any other reliably usable information.

#oracle