The 'Identity Increment ' property is used to insert a new row of data in a column in a sequential manner.
The Identity Increment
The 'Identity Increment " option helps in uniquely identifying each row of data in a table. Normally in a table we would have
identified a column as a primary key. To satisfy this condition in 'Employees' table, we create the 'EmployeeID' column to hold this value.
To achieve this purpose, this column will contain the unique values and it can’t contains the NULL or empty values or duplicate values.
So each time we enter a new record, we have to remember & enter a new number to uniquely identify this row. Sometimes this
method is error prone and we could end up entering a duplicate value and the record will not get saved. To ease this
issue, we have the 'Identity' Property in SQL Server. This is an inherent property of SQL Server wherein the server
itself will remember the last number inserted and increment it by 1 whenever a new record is being added.
SQL Identity Increment Syntax
CREATE Table tablename
(
column1 datatype Identity (1, 1) NOT NULL,
column2 datatype,
column3 datatype,
column4 datatype,
.
.
.
columnn datatype
)
The above example shows that column1 is an identity column which starts with value 1 and increments by 1 also.
The Identity Increment Example
You can add the identity specification property as shown below while creating a table.
CREATE TABLE Employees
(
EmployeeID smallint Identity (1,1) NOT NULL,
EmployeeName nvarchar(50) NOT NULL,
DateOfBirth smalldatetime NOT NULL,
DesignationID smallint NULL,
DeptID smallint NULL,
PhoneNo nvarchar(12) NULL,
City nvarchar(50) NOT NULL,
Salary decimal(5, 2) NULL );