What is an Identity Column?
If a column is marked as an identity column, then the value for this column are automatically generated, when you inserted a new row in the table.
Create an Identity column
Create a table tblemployee, this statement marks PersonId as an identity column with seed = 1 and Identity Increment = 1. Seed and Increment values are optional. If you do not specify the identity and seed they both default to
Create Table tblemployee
(
EmpId int Identity(1,1) Primary Key,
Name nvarchar(20)
)
(
EmpId int Identity(1,1) Primary Key,
Name nvarchar(20)
)
In the following 2 insert statements, we only supply values for Name column and not for empid column.
Insert into tblemployee values ('Ankit')
Insert into tblemployee values ('Anand')
If you select all the rows from tblemployee table, you will see that, 'Ankit' and 'Anand' rows have got 1 and 2 as empid.
Now, if I try to execute the following question, I am asked stating an error - an explicit value for the identity column in the table 'tblemployee' can only be specified when the column list is used And IDENTITY_INSERT is on.
Insert values into employee values (1, 'ank')
so if you mark a column as an identity column, you don't need to explicitly supply the value for that column when inserting a new row. The value is automatically calculated and provided by the SQL Server. Therefore, to insert a row in the tblemployee table, simply offer a value for the name column.
Insert into Tblemployee values ('ank')
