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.

IDENTITY (starting value, Increment by value) 

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 -

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 –

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 -

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 –

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 -