SQL Server - AUTO INCREMENT

The SQL Server (Transact-SQL) AUTO INCREMENT statement is used to create a unique numerical value for each additional record inserted into a table. Generally, it is used to create the numerical primary key field.

SQL Server AUTO INCREMENT Statement

In SQL Server (Transact-SQL), the IDENTITY keyword is used to perform an auto-increment feature. For example - the below mentioned query creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which auto-increment is applied on column EmpID.

CREATE TABLE Employee (
  EmpID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  PRIMARY KEY(EmpID)
);

Setting or Changing the AUTO INCREMENT Value

In the example above, the start and increment values of IDENTITY keyword are 1. The start value and increment value can be changed as required. For example - when IDENTITY(100, 5) is used, the start value will be 100, and increment value will be 5.

Inserting Records

While inserting a new record, EmpID should not be specified. A unique value of EmpID will be added automatically.

INSERT INTO Employee (Name, City, Age, Salary)
VALUES ('John', 'London', 27, 2800);

This query will insert a new record into the "Employee" table. The "EmpID" column will be assigned a unique value automatically. The "Name" column will be set to "John", the "City" column will be set to "London", the "Age" column will be set to 27 and the "Salary" column will be set to 2800.