AUTO INCREMENT statement is used to increase a column value by user defined value on particular column to generate unique value in a table to find rows easily. The value in primary key column get increased to generate unique value by default when we insert new rows the table. It helps to find individual rows easily. As per our Requirement we can change the starting value and how much value we should increase. AUTO INCREMENT statement is used while creating table on CREATE TABLE statement. We are unable to insert values on AUTO INCREMENT column. AUTO INCREMENT syntax vary for different databases. In SQL we can use IDENTITY command for AUTO INCREMENT.

CREATE TABLE table_name 
		( 
         column1 DATA TYPE IDENTITY (starting value, increment by),
         column2 DATA TYPE,
          …………………………., 
         columnN DATA TYPE ); 

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, 'Employee 2 ','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.