In this one project I am working on, I need to set a particular field to be unique (not a problem!) but if the field is null I want the constraint to be ignored. In Sql Server 2008 I use filtered index as shown below but this is not available in earlier versions of SQL!
CREATE UNIQUE NONCLUSTERED INDEX User_UserName_IUC
ON [User] (pinNr)
WHERE UserName IS NOT NULL
But I don't think this is available in SQL Server 2005. In fact this blog post indicates there is a workaround using a trigger to check for uniqueness. Does anyone have an example of this? or maybe an alternative?
Unfortunately upgrading to SQl Server 2008 is not an option for this particular client!!