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