The Identity Column

Incremental columns are either columns of UniqueIdentifier data type or BigInt with default function AutoIdentity(). Both data types are 64 bit integers.

Identity columns are always unique. In Embedded and Server the sequence begins with 1 and every time incremented by 1. In distributed DB each server will generate incremental value based on its ID in a cluster and increment by the number of Severs. For instance, 3 servers in cluster will generate sequence with following values:

Server 0: 0,3,6,9,…

Server 1: 1,4,7,10,…

Server 2: 2,5,8,11,…

Limitations

UniqueIdentifier data type cannot be used in distributed DB when table is replicated, i.e. not set PARTITION column. Instead of, use BigInt with default function AutoIdentity().

Optimization hint

Secondary indexes with identity column(s) does not need to be set as UNIQUE, since they always be. It allows avoiding unique constraint check during row insert and in distributed DB may drastically benefit the performance.

Use Scope_identity() function to return the last identity value inserted into identity column in the same scope.

Example

-- create table with 2 identity columns

create table product (

id1 uniqueidentifier not null primary key,

id2 bigint not null default(AutoIdentity()),

name varchar);



-- create table to store products transaction

create table [log] (

id1 bigint,

id2 bigint,

[time] datetime default(getdate()));



go; --execute create tables


-- insert into products, and to log with the identity value of the products row

insert into product(name) values('myproduct');

insert into [log](id1,id2)
values(scope_identity(),scope_identity());

Related