unique index with multiple nulls

Question

unique index with multiple nulls

isa-369599

SSCommitted

Points: 1602
More actions
March 18, 2008 at 11:59 pm

#204321

hello everyone , i m using SQL Server 2005, here are my 2 tables:
Table TVSRecords:
TvsRecodID int (pk)
TvsFormNo Int (unique index)
CreateDate datetime
FormImage image
Comments varchar(100)
AnotherTable is FormsQueue:
FormQueueID int (pk)
FormNo int
FormImage image
Now i moved the records from FormQueue table to TvsRecords, now the problem is while moving
record from application, In 1st table TvsFormNo is unique and it allows only 1 null, so 1st
user add forms in this table and its also check unique forms are inserted , but we have mutilple users
used this table and add forms in TvsRecors (1st Table) so it gives error,
Plz tell me how i implement unique index allow multiple nulls in TvsRecords table with Uniqueness
in SQL SERVER 2005
? Plz tell me the solution.
Thanx in Advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gogula G. Aryalingam SSCrazy Points: 2688 More actions · Answer 1

[font="Verdana"]A Unique index allows only one NULL value, so that values entered are unique (while indexing SQL Server compares NULL values as equal). It is by design.[/font]

isa-369599 SSCommitted Points: 1602 More actions · Answer 2

i know this , by design it support 1 null only, but is there any other solution exists?

Gogula G. Aryalingam SSCrazy Points: 2688 More actions · Answer 3

Gogula G. Aryalingam

SSCrazy

Points: 2688

March 19, 2008 at 12:25 am

#791294

How about a trigger, to run on INSERT, UPDATE?

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

Well you cant have multiple columns with null values and a unique index on them. If this situation cant be changed you can take away the unique index and do you data validation ( for uniqueness for non-null values) in your stored procedure or your code.

Or

you can substitue your null values for some other values say negative values.

"Keep Trying"

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

Try using an indexed view

create view dbo.myview

with schemabinding

as

select TvsFormNo

from dbo.TvsRecords

where TvsFormNo is not null

go

create unique clustered index uix_myview on dbo.myview (TvsFormNo)

Also google for 'nullbuster' for another technique

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537