Identity column of a table is a column whose value increases automatically. A user generally cannot insert a value into an identity column. A table can have only one column that is defined with the identity attribute.
Syntax :
IDENTITY [ ( seed , increment ) ]
Default value of identity is IDENTITY (1,1).
Seed : The seed represents the starting value of an ID and the default value of seed is 1.
Increment : It will represent the incremental value of the ID and the default value of increment is 1.
For Example :
Step 1 : Create a table named school.
CREATE TABLE school ( student_id INT IDENTITY, student_name VARCHAR(200), marks INT );
Here, the ‘student_id’ column of the table starts from 1 as the default value of seed is 1 and each row is incremented by 1.
Step 2 : Insert some value into a table.
INSERT INTO school (student_name, marks) VALUES ('Sahil', 100); INSERT INTO school (student_name, marks) VALUES ('Raj', 78); INSERT INTO school (student_name, marks) VALUES ('Navneet', 80); INSERT INTO school (student_name, marks) VALUES ('Rahul', 75); INSERT INTO school (student_name, marks) VALUES ('Sudeep', 82); INSERT INTO school (student_name, marks) VALUES ('Azaan', 75);
Step 3 : To see the records in the table ‘school’ , we can use the following code:
SELECT * FROM school;
Output :
student_id | student_name | marks |
---|---|---|
1 | Sahil | 100 |
2 | Raj | 78 |
3 | Navneet | 80 |
4 | Rahul | 75 |
5 | Sudeep | 82 |
6 | Azaan | 75 |
Step 4 : Lets delete a record.
DELETE FROM school WHERE student_id = 4;
Step 5 : To see the records in the table.
SELECT * FROM school;
Output :
student_id | student_name | marks |
---|---|---|
1 | Sahil | 100 |
2 | Raj | 78 |
3 | Navneet | 80 |
5 | Sudeep | 82 |
6 | Azaan | 75 |
Now, you can see that the student_id column is not in order, So you have to reset the Identity Column.
Here, to reset the Identity column in SQL Server you can use DBCC CHECKIDENT method.
Syntax :
DBCC CHECKIDENT ('table_name', RESEED, new_value);