Using a filtered index for a unique constraint

I needed to apply a unique constraint on a combination of columns, but only for rows that had not been marked as deleted. After scouring the web, I came to the conclusion that there were two worthwhile ways to achive this.

1. Set up a view that only exposes the rows on which the constraint should act. This needs to be a persisted view.
2. Instead of a constraint, set up a filtered index.

Discussions on Stack Overflow also suggested using triggers to do some magic in the system, but I have a longstanding hatred for triggers and cursors. One could also opt to enforce your uniqueness constraint in all your inserting and updating stored predures, but this is tiresome and error prone.

I opted to do the filtered index, as that seems to be the cleanest way of achieving my goal. Note that filtered indices are only available on SQL 2008. If you have 2005, you'll need to use a different mechanism, like the constraint on the view.

You can find a comparison of unique indices and unique constraints here http://msdn.microsoft.com/en-us/library/aa224827(SQL.80).aspx The writer comes to the conclusion that they're pretty much the same, with the index having a few more creation options.

Sample Code

This is the table we will use to illustrate the effect of the index:

CREATE TABLE dbo.SerialisedStock (
SerialisedStockId INT IDENTITY(1,1) NOT NULL,
StockId INT NOT NULL,
StockCode VARCHAR(50) NOT NULL,
IsDeleted BIT NOT NULL
CONSTRAINT PK_SerialisedStock PRIMARY KEY CLUSTERED
(
SerialisedStockId
)

The code to create our index on the table:

CREATE UNIQUE INDEX UK_SerialisedStock_StockNumber ON SerialisedStock(StockId, StockCode ) WHERE IsDeleted = 0

This insert statement will fail if done more than once:

INSERT INTO SerialisedStock
(StockId, StockCode, IsDeleted)
VALUES(1, 'aa',0)

This insert statement can be done multiple times:

INSERT INTO SerialisedStock
(StockId, StockCode, IsDeleted)
VALUES(1, 'aa',1)