An Identity is a column that is used to generate key values.
The values are based on a seed (starting value) and an incremental value.
Identity is often called autonumber in databases other than SQL Server.
A table can have only one Identity column.
This example creates a table with an Identity column.
CREATE TABLE Customer (
Id INT IDENTITY(1,1),
FirstName NVARCHAR(40) NOT NULL,
LastName NVARCHAR(40) NOT NULL,
City NVARCHAR(40) NULL,
Country NVARCHAR(40) NULL,
Phone NVARCHAR(40) NULL,
CONSTRAINT PK_Customer PRIMARY KEY (Id)
)
The Id column is an identity which will autogenerate unique values.
The values start at 1 and increment by 1 with each new row.
Syntax to add an identity column to a table.
IDENTITY(seed, increment)
seed - identifies the starting point from where the value will be autogenerated.
increment - defines the incremental value to add from the previous row.
These parameters are optional. They both default to 1.
This global function gets the last identity value that was generated.
SELECT @@IDENTITY AS 'Identity'
@@IDENTITY only returns a value when executed in the context of a transaction.
In all other cases it returns NULL.
Another example in which the first record starts at 50 and increments by 5.
The values will be 50, 55, 60, 65, and so on, with each added row.
CREATE TABLE Customer (
Id INT IDENTITY(50,5),
FirstName NVARCHAR(40) NOT NULL,
LastName NVARCHAR(40) NOT NULL,
City NVARCHAR(40) NULL,
Country NVARCHAR(40) NULL,
Phone NVARCHAR(40) NULL,
CONSTRAINT PK_Customer PRIMARY KEY (Id)
)
