About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Sunday, July 6, 2014

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

No comments:

Post a Comment