The purpose to create an identity column in a table to provide auto-generate
incremental values. When you marked a column as an identity column, the values
for that column is automatically inserted based on a provided seed (starting
point) and increment, when you insert a new record into the table.
Syntax:
IDENTITY [( seed, increment)]Seed: It defines the starting value for a column. If you does not provide any value for it then it will take 1 by default.
Increment: It defines the incremental value that is added to the identity value of the previous row that was loaded. The default incremental value is 1.
Example: Create a table with identity column
In the given example tblStudent table define StudentId column
as identity column with seed=100 and increment=1. Seed and Increment value are
optional. if you does not provide any value for seed and increment then it
will take 1 for both by default.
CREATE TABLE TblStudent
(
StudentId int Identity(100,1) Primary Key,
SName nvarchar(20)
)
When you insert the first record into tblStudent table, The value for the
StudentId column starts from 100 and then increment it 1 by 1 for the next
inserted records. Let's insert few records in this table and see the identity
values
INSERT INTO tblStudent (SName) VALUES ('Harish')
INSERT INTO tblStudent (SName) VALUES ('Kamal')
So we have inserted the above two records, Identity values for these
records are 100 and 101. Refer screenshot
Do not provide an explicit value for the identity column.
if you provide an explicit value for identity column as given in below
example.
INSERT INTO tblStudent (StudentId,SName) VALUES (103,'Ramesh')
It will throw an error like
Cannot insert explicit value for identity column in table 'tblStudent' when
IDENTITY_INSERT is set to OFF.
But in some scenario we have to explicitly provide a value for identity
column.
Now we have total two records in tblStudent table with StudentId 100 and 101. Delete the record whose StudentId is 101 and insert a new record. SQL engine will will automatically generate a value 102 in StudentId IDENTITY column for new inserted record. As we have already delete a record with StudentId = 101 and it does not exist in the tblStudent table. To fill this gap we have to provide an explicit value for identity column. To do this
Now we have total two records in tblStudent table with StudentId 100 and 101. Delete the record whose StudentId is 101 and insert a new record. SQL engine will will automatically generate a value 102 in StudentId IDENTITY column for new inserted record. As we have already delete a record with StudentId = 101 and it does not exist in the tblStudent table. To fill this gap we have to provide an explicit value for identity column. To do this
1. First turn on identity insert by using given command
SET Identity_Insert tblStudent ON
2. In the insert query provide the value for identity columnINSERT INTO tblStudent (StudentId,SName) VALUES (101,'Harish')
As long as the Identity_Insert is turned on for a table, you need to
explicitly provide the value for that column. If you don't provide the
value, you get an error -
Explicit value must be specified for identity column in table 'tblStudent'
either when IDENTITY_INSERT is set to ON or when a replication user is
inserting into a NOT FOR REPLICATION identity column.
After, filling the gap in the tblStudent table for the identity column, if again you want to automatically generate value for identity column then you have to turn off Identity_Insert using the below command
SET Identity_Insert tblPerson OFF
Reset identity column
Reset identity column
Use DBCC CHECKIDENT command to reset the identity column after deleting all records from tblStudnet. This command will reset StudentId identity column.
DBCC CHECKIDENT(tblStudnet, RESEED, 0)
0 Comments