Auto-generated numeric keys are everywhere in relational databases. In SQL Server, two features dominate this space:
IDENTITY columns
SEQUENCE objects
Both generate numbers. Both are fast. Both are widely used. Today, though, IDENTITY columns are by far the most common – yet in our client work, we tend to use SEQUENCE objects almost exclusively. Everything I can do with an IDENTITY column, I can also do with a SEQUENCE object, and we find them more flexible.
As a simple example, if you’ve ever tried to perform a SET IDENTITY_INSERT ON across a linked server, you’d know this doesn’t