Thanks for the tip about "a unique clustered index on the view". Wonderful!

Anonymous User 3/6/2005 9:14:50 AM

Good explanation and very useful

Anonymous User 1/14/2005 5:17:17 AM

Not being a fan of triggers I went looking for other solutions to this problem. As mentioned elsewhere on this site by the MVPs, you can create a view with schemabinding that selects the columns that define your uniqueness constraint and return only the non NULL values in the view. Then create a unique clustered index on the view. (works in Standard Edition SQL Server too). If you have more than one ANSI SQL-92 compliant uniqueness constraint to implement you can create multiple views, each with their own unique clustered index. Alternatively consider creating a computed column that substitutes another unique value in place of a NULL, then place a non clustered index on the computed column(s). For example, to ensure uniqueness on Col2 of type int where Col1 is a primary key of type int you could create the following :
create table Tab1 (
Col1 int not null identity primary key,
Col2 int null,
Col3 as coalesce(Col2, Col1 + 1000000000) UNIQUE
)


Peter Ellis 9/21/2004 7:08:59 PM