SQL IDENTITY KEYWORD

SQL IDENTITY

The IDENTITY keyword is used to perform an auto-increment feature in SQL Server.

Example 1 of SQL IDENTITY Keyword

The below SQL statement classifies the "Invoice_No" column to be an auto-increment primary key in the "Orders" table:

CREATE TABLE Orders
(
Invoice_No int PRIMARY KEY IDENTITY(1,1),
LastName varchar(200) NOT NULL,
FirstName varchar(200),
City varchar(200)
)

The default starting value for IDENTITY is 1, and it will increment by 1 for each new record.

So the above statement can surely be written without any error as:

CREATE TABLE Orders
(
Invoice_No int PRIMARY KEY IDENTITY,
LastName varchar(200) NOT NULL,
FirstName varchar(200),
City varchar(200)
)

IF you want that the "Invoice_No" column should start at value 5 and increment by 2, change the identity to IDENTITY(5,2).


Now lets insert a new record in the Orders table:

INSERT INTO Orders (FirstName,LastName, City)
VALUES ('Ram','Sharma', 'Mumbai) )


To insert a new record into the "Orders" table, we do not have to specify a value for the "Invoice_No" column (a unique value will be automatically added).