Identity column

In the Ultra Transcenders books

DP-750DP-600DP-700

Each book explains Identity column in context, with comparison tables and the common traps.

Terms in this definition

• Event

  Table in Log Analytics where entries from Windows event logs are kept.

• SEQUENCE

  A schema-bound object that generates values on request via NEXT VALUE FOR without belonging to a table. One can feed several tables, restart, cycle or set aside a block of values; any value drawn inside a transaction that rolls back is gone.

• 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.

• REBUILD

  Drops and recreates an index to clear fragmentation. Columnstore indexes also have every delta row pushed into compressed rowgroups during this operation.

• Full refresh

  A pipeline run that wipes the stored data and state of a streaming table or materialized view and then rebuilds it from every source record; anything already deleted at the source cannot come back.

Related terms

• DBCC CHECKIDENT

  Checks an IDENTITY column's current value and resets its seed. In Fabric Data Warehouse only plain RESEED is allowed (you can't give it a new value), and you should run it once IDENTITY_INSERT loads have finished.

• Logic Apps SQL Server built-in triggers

  Triggers built into the SQL Server connector of Standard Azure Logic Apps (When a row is inserted is one example). They check for changes using change tracking, whereas the managed connector's triggers look at a rowversion or identity column.

• Surrogate key

  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.