Workaround to have null value in Identity column

When we are trying to explicitly enter null values it to an identity column we get an error message saying not allowed. Lets try to stimulate the problem.

Here we are taking a table with id column

create table Tab_nullableIdentity

(id int identity(1,1),

name varchar(10)

)

Command(s) completed successfully.

Let now insert null value, but to explicitly insert value we need to set IDENTITY_INSERT property to ON

set identity_insert Tab_nullableIdentity  on

go

insert into Tab_nullableIdentity(id,name) values(null,'Rinny')

Msg 339, Level 16, State 1, Line 31

NULL are not allowed as explicit identity values.

Workaround

Lets create a new table without identity property set on id column

create table Tab_nullableIdentityWithSeQ

(id int ,

name varchar(10)

)

Command(s) completed successfully.

Now let’s create a sequence with incremental factor of 1 then use this sequence to populate the table data

create sequence seq1 as int

start with 1

increment by 1

Command(s) completed successfully.

Now lets insert few rows in the table

insert into Tab_nullableIdentityWithSeQ(id,name) values(null,'Rinny')

insert into Tab_nullableIdentityWithSeQ(id,name) values(next value for seq1,'Binny')

lets query the table data

select * from Tab_nullableIdentityWithSeQ

id          name

----------- ----------

NULL        Rinny

1           Binny

So here we can we are able to generate sequence auto generated number as well as we are able to insert null value to  the id column.

That’s it J

Conclusion: we can Stimulating null value insert identity property column through Sequence Object of SQL Server 2012.

Have your tea and sit for code :)

 

Post Reference: Vikram Aristocratic Elfin Share