In SQL Server, if we set the property of an identity “ON” then we cannot we cannot insert a single value in identity column easily. Further, if you try inserting value in identity column then a message will be displayed:
"Cannot insert explicit value for identity column in table 'Tool' when IDENTITY_INSERT is set to OFF."
But if we try the below command then it is possible to insert the value in the identity column:
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }
In one table, in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a “SET IDENTITY_INSERT ON” statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.
If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.
The setting of SET IDENTITY_INSERT is set at execute on run time and not at parse time.