In this article we will discuss, how to use Identity Column property to add in table’s column in SQL Server. Let’s go ahead and discuss the same with real-time example.
Identity Column
If a column is marked as an identity column, then the values for this column are automatically generated, When you insert a new row into the table.
Identity(seed, increment)
Seed and increment values are optional, by default value is one for both parameters.
Seed – Starting number
Increment – It is the incremental value added to the identity value of the previous row.
Example –
CREATE TABLE STUDENT(
ID INT IDENTITY(1,1),
NAME VARCHAR(50) NOT NULL
)
Now it’s time to insert some records in our table. Please follow the below insert statement.
insert into student values ('Shipun')
insert into student values ('Mithun')
The above statements are executed on our database and two records are inserted into Student table. ID column data are inserted automatically such as 1 and 2. Please into the below table.
insert into student values (null)
This insert statement is rejected by our database because Name column have Not Null Constraint. So it will not allow to insert null values into this column. But the seed value of student table is increased by one that means it’s current value is 3.
insert into student values ('Dilip')
The above statement is executed successfully and 1 row affected in our Student table.
