SQL Identity

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.

Example

#

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.

Note: Do not insert identity values yourself -- it is the database itself that assigns new values.

Syntax

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.


Retrieve Identity

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.


More Examples

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)
)

You may also like



Guides


vsn 3.1