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

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

Last updated:9/22/2021 12:54:36 AM

1 Answers

Ashutosh Kumar Verma
Ashutosh Kumar Verma

SQL Identity:

 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 );

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

Now, records is inserted in the above created table,

insert into Student

Values(123,'Ravi Vishwakarma','MCA',23),
(124,'Shriyam','Marketing',22);

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

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.


Answer