Cannot create default constraint with sequence object on global temporary table

I was toying with SQL sequence object and all of sudden I was surprise to find that we cannot create default constraint with sequence object on Global temporary table. Let me demonstrate my finding with an example.

Here we are creating a global temporary table with two fields

create table ##temoAddSequence

(id int,

name varchar(20))

Command(s) completed successfully.

Lets insert few records in it.

insert into ##temoAddSequence values(2,'Amrapali')

insert into ##temoAddSequence values(5,'Mahin')

lets create a sequence object

create sequence seq_IdCounter

as int

       minvalue 1

       no maxvalue

       start with 4

       increment by 1

       ;

go

Command(s) completed successfully.

Lets try to associate this sequence object to Id column of ##temoAddSequence table

alter table ##temoAddSequence

add constraint id_default_sequence

default (next value for seq_Counter) for id;

Msg 208, Level 16, State 1, Line 19

Invalid object name 'seq_Counter'.

Conclusion: We cannot create default constraint with sequence object on Global temporary table.

When you are on my head SQL, I abscond from all tasks, to shower my love to you  :)

Post Reference: Vikram Aristocratic Elfin Share

Changing Column to use Default as sequence object

Here we are going to check, how we can create default constraint, as a sequence to an existing column on a table object. For our demonstration we are creating a small table with just two column id and name. ID is the field on which we will create default constraint later in this article.

create table tempAddSequence

(id int,

name varchar(20))

Command(s) completed successfully.

Lets insert few rows into tempAddSequence table object.

insert into tempAddSequence values(2,'Amrapali')

insert into tempAddSequence values(5,'Mahin')

select * from tempAddSequence

id          name

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

2           Amrapali

5           Mahin

(2 row(s) affected)

Now our table is populated with some rows, lets create a sequence object starts with 4 and increment by 1.

create sequence seq_Counter

as int

       minvalue 1

       no maxvalue

       start with 4

       increment by 1

       ;

go

Command(s) completed successfully.

Our Sequence object is ready to consume, its now turn to create default constraint on ID column of tempAddSequence table.

alter table tempAddSequence

add constraint id_default_sequence

default (next value for seq_Counter) for id;

Command(s) completed successfully.

Our Default constraint is now in place, associated with column ID. Now we are ready to insert few more records in tempAddSequence table, here won’t insert explicit value for Id field of table.

insert into tempAddSequence(name)

select 'Aashiyana' union all

select 'Anvi'

(2 row(s) affected)

Lets check out the insertion by querying the table

select * from tempAddSequence

id          name

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

2           Amrapali

5           Mahin

4           Aashiyana

5           Anvi

(4 row(s) affected)

Conclusion: we can create default constraint with sequence object on existing table with existing data.

Go Go, its your SQL soul calling :)

  

Post Reference: Vikram Aristocratic Elfin Share

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