Workaround to have Default value in Identity column

If you try to define default value for identity column, it will result in error saying “Not Allowed. Lets try to stimulate the problem.

Here we are taking a table with id column

create table Tab_DefaultIdentity

(id int identity(1,1) default 1000,

name varchar(10)

)

Msg 1754, Level 16, State 0, Line 47

Defaults cannot be created on columns with an IDENTITY attribute. Table 'Tab_DefaultIdentity', column 'id'.

Msg 1750, Level 16, State 0, Line 47

Could not create constraint or index. See previous errors.

So what is the workaround for the same, lets check out with Sequence feature of SQL Server 2012

Workaround

Here we creating a new table without Default constraint set at id field.

create table Tab_DefaultIdentity

(id int default 1000,

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_DefaultIdentity(name) values('Rinny')

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

lets query the table data

select * from Tab_DefaultIdentity

id          name

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

1000        Rinny

1           Binny

So here we can we can see, by using Sequence feature one can add default value as well as can get the incremental identity for a column.

No SQL no Night :)

Post Reference: Vikram Aristocratic Elfin Share

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

How to have identity property set for multiple column in a table

Lets try out with a small demo, here we are creating table with three column, out of which two column has identity property set.

create table dupIdentity

(col1 int identity(1,1),

col2 int identity(1,1),

col3 varchar(10))

Msg 2744, Level 16, State 2, Line 1

Multiple identity columns specified for table 'dupIdentity'. Only one identity column per table is allowed.

Identity property is not allowing me to have on multiple columns.

So what’s the alternative, apparently SQL Server 2012 feature SEQUENCE Object? Lets try out. First we will create table without any identity property column

create table dupIdentity

(col1 int,

col2 int,

col3 varchar(10))

Command(s) completed successfully.

Now lets create two sequence object seq1 and seq1, one which will starts from 1 and another will starts from 10 and incremented by 1 after every insert.

create sequence seq1 as int

start with 1

increment by 1

create sequence seq2 as int

start with 10

increment by 1

Command(s) completed successfully.

Now since we have sequence object in place, lets apply it to our table

insert into dupIdentity values ( next value for seq1, next value for seq2, 'Pragyan')

insert into dupIdentity values ( next value for seq1, next value for seq2, 'Lopamudra')

(2 row(s) affected)

Lets query the table to find the data inserted.

select * from dupIdentity

col1        col2        col3

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

1           10          Pragyan

2           11          Lopamudra

(2 row(s) affected)

That’s it J

Conclusion: we can stimulate multiple identity property through Sequence Object of SQL Server 2012.

Nights are bore to tears when I am far from you, my SQL Server  :)

Post Reference: Vikram Aristocratic Elfin Share