Re: Unique Constraint with Multiple NULLS

> In SQLServer 2000 you can use an indexed view to achieve this.
>
> CREATE TABLE Sometable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL)
>
> GO
>
> CREATE VIEW Sometable_Unique_Non_NULL
> WITH SCHEMABINDING
> AS SELECT colx FROM dbo.Sometable WHERE colx IS NOT NULL
>
> GO
>
> CREATE UNIQUE CLUSTERED INDEX uclcolx ON Sometable_Unique_Non_NULL (colx)
>
> INSERT INTO Sometable VALUES (1,1)
> INSERT INTO Sometable VALUES (2,NULL)
> INSERT INTO Sometable VALUES (3,NULL)
>

Very interesting David, although I am not sure if using an index view would be the most optimal solution. The base tables will be updated frequently and there are several permutations of unique constraints that need to be put in place. I worry the performance hit for maintaining the indexes would be too much. I will definitely give this a good test though... Thank you!

Adam Received on Thu Jan 01 2004 - 23:37:22 CET