Summary -

In this topic, we described about the IDENTITY Statement with detailed example.

IDENTITY statement is used to increase the value on specific column to get unique values on that column to easily identify rows in table. The PRIMARY KEY column is used generally for the IDENTITY on table. We can not insert values on IDENTITY COLUMNS the SQL provides UNIQUE values on IDENTITY PRIMARY KEY column. We can create IDENTITY property on PRIMARY KEY column while creating table by using CREATE TABLE statement. The Default value for both starting value ang Increment by value is 1.

Syntax -

IDENTITY (starting value, Increment by value) 

Example –

Scenario – Creating table by using IDENTITY statement.

Requirement – Creating employee_details table by using IDENTITY statement. The query was as follows –

CREATE TABLE employee_details (
     emp_id NOT NULL  IDENTITY(1,1) PRIMARY KEY,
     emp_name VARCHAR(20), 
     designation VARCHAR(20),
     manager_id VARCHAR(20),
     date_of_hire VARCHAR(20),
     salary INT,
     dept_id INT);  

By executing above query, we can create employee_details table. The output was as follows -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id

Scenario – Incrementing value on PRIMARY KEY column in existing table without specifying values while inserting rows.

Requirement - Incrementing value on PRIMARY KEY emp_id column in employee_details table without specifying values on emp_id column while inserting rows in table. The query was as follows –

INSERT INTO employee_details(emp_name,designation,date_of_hire,
salary,dept_id)VALUES('Employee1','Director','2019-07-11',45000,1000),
('Employee2', 'Director', '2019-07-11', 40000, 2000); 

By executing above query, we can get emp_id by default without inserting emp_id values on INSERT statement. The query was as follows –

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000

Scenario – Creating table by using IDENTITY statement with some other values.

Requirement – Creating employee_details table by using IDENTITY statement starting value with 100 and increment by 10. The query was as follows –

CREATE TABLE employee_details (
       emp_id NOT NULL  IDENTITY(100,10) PRIMARY KEY,
       emp_name VARCHAR(20),
       designation VARCHAR(20),
       manager_id VARCHAR(20),
       date_of_hire VARCHAR(20),
       salary INT,
       dept_id INT);  

By executing above query, we can create employee_details table. The output was as follows -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id

Scenario – Incrementing value with mentioned values on PRIMARY KEY column in existing table without specifying values while inserting rows.

Requirement - Incrementing value on PRIMARY KEY emp_id column with starting value 100 and increment by 10 in employee_details table without specifying values on emp_id column while inserting rows in table. The query was as follows –

INSERT INTO employee_details(emp_name,designation,date_of_hire,
salary,dept_id)VALUES('Employee1','Director','2019-07-11',45000,1000),
('Employee2','Director','2019-07-11',40000, 2000); 

By executing above query, we can get emp_id starting value with 100 and each row is increased by 10 by default without inserting emp_id values on INSERT statement. The query was as follows –

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
100 Employee1 Director 2019-07-11 45000.00 1000
110 Employee2 Director 2019-07-11 40000.00 2000

Scenario – Trying to insert value on AUTO INCREMENT column.

Requirement- Trying to insert value on AUTO INCREMENT emp_id column on employee_details table. The query was as follows –

INSERT INTO employee_details
(emp_id,emp_name,designation,date_of_hire,salary,dept_id)
VALUES (120,'Employee1','Director','2019-07-11', 45000, 1000),
(130,'Employee2','Director','2019-07-11', 40000, 2000); 

We get output like as follows –

An explicit value for the identity column in table ' employee_details ' can only be specified when a column list is used and IDENTITY_INSERT is ON.


Scenario – Trying to insert value on AUTO INCREMENT column with IDENTITY INSERT ON statement.

Requirement - Trying to insert value on AUTO INCREMENT emp_id column on employee_details table with IDENTITY INSERT ON statement. The query was as follows –

SET IDENTITY_INSERT employee_details ON
INSERT INTO employee_details(emp_id,emp_name,designation,manager_id,
date_of_hire,salary,dept_id) VALUES(111,'Employee3','Manager','Employee1',
'2019-07-11', 27000,1000)
SET IDENTITY_INSERT employee_details OFF; 

By executing above query, we can insert our required emp_id value on employee_details table. The output was as follows -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
100 Employee1 Director 2019-07-11 45000.00 1000
110 Employee2 Director 2019-07-11 40000.00 2000
111 Employee3 Manager Employee1 2019-07-11 27000.00 1000