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!

ON [User] (pinNr)

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!!

2 Answers 2


Actually, I had to do something like this once. It involved creating a computed column that takes the value of the Unique column when is not NULL and the value of the primary key (with some other logic to make it impossible to clash with the values on the unique column), and making the unique index on that column. You can see an example of this and the trigger method here.


You can make a view (Where Username is not null) then put a unique index against the view. You'll never need to use the view, it'll just exist for this.

You can also use this technique to enforce uniqueness across several tables, where it would normally be impossible.

  • 2
    Indexed views do not require enterprise edition. They do, however, require that you use the WITH (NOEXPAND) query hint to force the standard edition query optimizer to use the indexed view instead of the underlying query.
    – anon
    Jan 15, 2011 at 14:32
  • 3
    In this case though the only point of the view is to enforce the unique constraint and this this is a perfectly valid/common approach. Oct 30, 2011 at 20:03
  • 2
    Edited to remove incorrect info about Enterprise Edition being required.
    – Meff
    Oct 31, 2011 at 13:11
  • @Meff thanks for taking the time to respond to the comments here - I hope you won't be a stranger to the site: you might be interested in these questions :) Oct 31, 2011 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.