Why is use IDENTITY() in SQL Server, how to make an auto-incremented table in SQL Server?

Question

Ashutosh Kumar Verma · Answer

SQL Identity: 
and Auto Increment allows a unique number which is automatically generated every time a new record is inserted in the table. SQL Auto Increment is mainly used in Primary Field.
Or, we can say that when a new record is inserted in database table then the identity value increased automatically for that record. Often it created as in primary key that I would like to created automatically when a new record is inserted. In other word Identity is called as auto-increment. 
Syntax- 
CREATE TABLE table_name (
Column_name datatype IDENTITY(‘start from’, ‘increment by number’) Constraint,
Column_name2 datatype,
Column_nameN datatype );
Example: 
There is database table is created with Identity Constraint in primary as like below,
create table Student(
stuID int IDENTITY(101,1) primary key,
stuRollNO int,
stuName varchar(255),
stuCourse varchar(255),
stuAge int );
Now, records is inserted in the above created table, 
insert into Student
Values(123,'Ravi Vishwakarma','MCA',23),
(124,'Shriyam','Marketing',22);
It you observe that in above SQL statement in which record is insert in database table column ‘stuID’ is not inserted through command, it is automatically generated. So stuID in automatically generated when I insert a new record into that table because ‘stuID’ is defined as Identity.

forum

Why is use IDENTITY() in SQL Server, how to make an auto-incremented table in SQL Server?

Ravi Vishwakarma

1 Answers

Liked By