New in SQL SERVER 2008:
We can define an unique column, that NULL is not need to be unique, or in other words: the unique check is only for not NULL values.
CREATE UNIQUE NONCLUSTERED INDEX IX_TableName_UniqueColumn
ON dbo.TableName (UniqueColumn)
WHERE UniqueColumn IS NOT NULL
GO
SELECT ID, UniqueColumn FROM TableName WHERE ID in (5, 6, 7)
-- for the example. lets say that the results are: 5, NULL ; 6, NULL ; 7, NULL.
UPDATE TableName SET UniqueColumn = 'test' WHERE ID = 5 --will be success
UPDATE TableName SET UniqueColumn = 'test' WHERE ID = 6 --will be failed
UPDATE TableName SET UniqueColumn = NULL WHERE ID = 5 --will be success
Yes. True
ReplyDeleteBut, one thing, INDEX deffers from CONSTRAINTS. You have applied FILTER Unique INDEX, so It validates only the Filtered condition.. But, CONSTRAINTS are always Unique(Data, Blank , NULL(If the column is nullable,.....). Am I correct ?
I guess you right.
ReplyDeleteBut while we get to the target of Unique columns to not NULL values, so as I see it, it's doesn't really matter if we use a CONSTRAINT or an index.
Thanks for the reply.