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

Insert Multiple Null value in an Unique Field through NONCLUSTERED Index

Read Previous Post: Allowing only two null values for a column in SQL table

Problem Statement: We want a unique constraint in such a way that, it maintains uniqueness of the values in the field along with it; it allows entering multiple NULL value in the field.

There is an exception with unique constraint, which evaluate two null values as duplicate whereas, ANSI says any operation with NULL evaluate to UNKNOWN.  Here we are trying to find the alternative way to insert multiple null value in Unique filed through NONCLUTERED Index.

We can implement the same using trigger, as we demonstrate in previous post; here we will try to implement the same using UNIQUE NONCLUSTERED Index.

Here we are creating table with three column id, name and salary.

create table TestMutipleNull_TB

(id int identity(1,1) primary key,

name varchar(10),

salary decimal)

Command(s) completed successfully.

Table creation is done; let’s create a UNIQUE NON CLUSTERTED Index on name field.

create unique nonclustered index ix_TestMultipleNull_TB_name

on TestMutipleNull_TB(name) where name is not null

Command(s) completed successfully.

Here the index has where condition which says name is not null, so the uniqueness will applied to only non-null value keeping null value aside, which is our objective to do in this article.

Lets insert some data in the table.

insert into TestMutipleNull_TB

select 'Babu',null union all

select 'Rinny',30000 union all

select 'Binny',null union all

select 'Lopa', 50000

Now here we are trying to insert a name which is present in the table.

insert into TestMutipleNull_TB values('Babu',40000)

Msg 2601, Level 14, State 1, Line 17

Cannot insert duplicate key row in object 'dbo.TestMutipleNull_TB' with unique index 'ix_TestMultipleNull_TB_salary'. The duplicate key value is (Babu).

The statement has been terminated.

Since the same name is present in the table, the insertion fails. This means uniqueness is maintained.

Now lets try to insert multiple null values in the name field.

insert into TestMutipleNull_TB values(null,40000)

(1 row(s) affected)

insert into TestMutipleNull_TB values(null,200)

(1 row(s) affected)

select * from TestMutipleNull_TB(nolock)

id          name       salary

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

1           Babu       NULL

2           Rinny      30000

3           Binny      NULL

4           Lopa       50000

6           NULL       40000

7           NULL       200

So here we saw multiple null values gets inserted into name field but it maintain the uniqueness of non null values.

Conclusion: You can use NONCLUSTERED Index to allow multiple null values keeping other values unique.

Software Engineer: Life became NULL if you go away my darling; me and my code :)

Post Reference: Vikram Aristocratic Elfin Share