Null values in indexes

[Pep Gómez]

I have a problem I don't know if it can be resolved...
I have a table where one of the fields, of datetime type, may accept Null
values. The problem is that I would like to create the index UNIQUE, but
SQL Server treats Null values as if they were "real" values, so I can't have
2 rows with the date field to Null.
Is this normal? I mean, a Null value should have no effect on the index (or
at least that's what I would like :-)
If there is no solution, how can I do the same but in a different way? What
I don't like is having to "invent" a Null date, like #1/1/1800# means
Null... but I think that's the only solution, right?

Thank you in advance for any solution and/or comment

Pep
pep@impulso.net

[David Satz]

the answer unfortunately is to get rid of the UNIQUE constraint and write
insert and update triggers to perform the check for you.
--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions
{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------
"Pep Gómez" <pep@impulso.net> wrote in message news:3d1c4650@10.1.10.29...
> I have a problem I don't know if it can be resolved...
> I have a table where one of the fields, of datetime type, may accept Null
> values. The problem is that I would like to create the index UNIQUE, but
> SQL Server treats Null values as if they were "real" values, so I can't
have
> 2 rows with the date field to Null.
> Is this normal? I mean, a Null value should have no effect on the index
(or
> at least that's what I would like :-)
> If there is no solution, how can I do the same but in a different way?
What
> I don't like is having to "invent" a Null date, like #1/1/1800# means
> Null... but I think that's the only solution, right?
>
> Thank you in advance for any solution and/or comment
>
> Pep
> pep@impulso.net
>
>
>