Can we insert explicit values into IDENTITY column in MS SQL

This is one of my favorite interview question. I hope you also would have faced this question, if not then there is a high possibility that you may face it soon!
Answer to the question Can we insert explicit values into IDENTITY column is MS SQL is YES. You can insert values into the IDENTITY column as you require.

Lets start step by step.

First lets create a table for the demo purpose,

CREATE TABLE DemoIdentity

(

    Id int IDENTITY (1, 1) not null,

    Name varchar(50) not null,

    [Address] varchar (100) not null,

    Phone char (15) not null

)

 

Table is ready, now we will insert some values to the above table.

INSERT INTO [DemoIdentity] ([Name] ,[Address] ,[Phone]) VALUES ('Asheej','Bangalore','9916123456')

INSERT INTO [DemoIdentity] ([Name] ,[Address] ,[Phone]) VALUES ('David','Birmingham','7543789234')

INSERT INTO [DemoIdentity] ([Name] ,[Address] ,[Phone]) VALUES ('Cliff','Columbus','4081234567')

Above is the standard way to insert the values into the table which has IDENTITY column.

Now lets try to insert a value with identity column value,

 

INSERT INTO [DemoIdentity] ([ID], [Name] ,[Address] ,[Phone]) VALUES (4,'Johnson','Perth','61478976782')

If you execute above SQL statement you will get below error,

Msg 544, Level 16, State 1, Line 11
Cannot insert explicit value for identity column in table 'DemoIdentity' when IDENTITY_INSERT is set to OFF.

Yes, now we will see the property which MS SQL provides you to insert values in IDENTITY column.

In SQL we have the property named IDENTITY_INSERT. Whenever you wanted to insert values to an IDENTITY column we just need to enable the property IDENTITY_INSERT.

Lets enable this property and insert a value in above table.

Syntax for enabling the IDENTITY_INSERT is,

SET IDENTITY_INSERT [DemoIdentity] OFF

Now we will insert the value by executing the same above statement by specifying the IDENTITY column value and see the result.
 
One important thing you have to remember is, in order to enable or disable the IDENTITY_INSERT  property you should be part of the sysadmin group.

Make sure you disable the IDENTITY_INSERT ON once you are done with the insert statement as this column is not intended to insert the values explicitly.

Syntax for disabling the IDENTITY_INSERT is,

SET IDENTITY_INSERT [DemoIdentity] ON

 

I hope now you understood the whole concept of the property IDENTITY_INSERT and how you can insert explicit values into an IDENTITY column.