O'Reilly logo

Discovering SQL: A Hands-On Guide for Beginners by Alex Kriegel

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

COMPARING IDENTITY COLUMNS AND SEQUENCES

While there are similarities between identity columns and sequences, there are also differences. The major difference is that the sequence object is independent of any particular table and can be used by multiple tables; another distinction is that a sequence can be used in any SQL statement, including SELECT, UPDATE, and DELETE; whereas an identity column cannot.

images Both sequences and identity columns generate unique numbers within their respective scopes, sequence objects, and tables. What if you need to generate a sequence of identities that are truly unique globally (the scope of the RDBMS or even the world)? Some databases provide built-in facilities; some leave you on your own to cobble up a solution. GUID values are represented in a hexadecimal string (for example, {780B9621-F5FB-4C31-8698-C92C9FFC7D37}) and, at least in theory, should never be generated twice. There are 3.4 × 10^38 total unique keys that can be generated (by comparison, there are only 1.33 × 10^50 atoms on Earth), which makes it highly unlikely (though not impossible) that the same number can be produced more than once. By their very nature, GUIDs are not sequential, and therefore are not the best choice for primary keys despite their assured uniqueness. The functions to generate GUIDs, also known as Universally Unique Identifiers (UUIDs) are vendor-dependent. Oracle ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required