Instead of the source system's natural key, a generated value such as an identity column (BIGINT GENERATED ALWAYS AS IDENTITY). Values are unique, gaps are possible, and an identity column rules out concurrent transactions on the table.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains Surrogate key in context, with comparison tables and the common traps.
Terms in this definition
- Chat message roles
Labels on chat messages: instructions go under system, the person's input under user, the model's previous answers under assistant, and results returned by a called tool under tool (or function).
- Natural key
Uses an identifier the source already carries, an order number for instance, rather than one generated later. Learn prefers natural keys, if stable, to generated surrogates, since they work well for joins and clustering.
- Identity column
Assigns a unique
BIGINTto every row inserted into a Delta table, though not always in an unbroken sequence; it is declared asGENERATED ALWAYS AS IDENTITYorGENERATED BY DEFAULT AS IDENTITY. Concurrent writes are disabled and a rebuild can change the numbers, so it suits append-only tables that no full refresh will ever touch. - IDENTITY
A column property, written IDENTITY(seed, increment), that gives each new row the next number in a rising sequence. SCOPE_IDENTITY reports the latest value created in the current scope, and a rolled-back transaction still uses up the numbers it took.
- VALUES
Returns in DAX the distinct column values, or table rows, still visible after filters are applied, sometimes with an extra blank entry. CALCULATE often takes the result as a table filter.
- Event
Table in Log Analytics where entries from Windows event logs are kept.
Related terms
- Index column
Added through Add column > Index column, this Power Query column numbers rows in sequence, commonly so each row is unique or to serve as a surrogate key.
- Junk dimension
Groups many minor attributes with only a handful of possible values, flags and statuses for instance, into a single table keyed by a surrogate key. The fact table then carries one key in place of many.
- Unknown member
A placeholder row in a dimension, usually given the surrogate key -1, that a fact load points to whenever it can't find the matching dimension key. The fact row is kept rather than lost, and its key is corrected later.