SQL Server gets an ANSI compliant unique index … sort of
If you work with any of the other big database platforms you've probably noticed that SQL Server's implementation of a unique index is "different" than the others. Until now there hasn't been a way to fix that without using a trigger. Until now...
But first, a little background information.
When you create a unique index in SQL Server (assuming a single column index here), you can have a single row with each value, and a single null value.
When you create a unique index in Oracle, or any other database which uses the ANSI compliant unique index (again assuming a single column index), you can hand a single row with each value, and as many null values as you want.
The logic of allowing multiple NULL values (which I agree with) is that NULL isn't a value, its unknown, so who's to say that two unknown values aren't different.
With SQL Server if you wanted to enforce this you'd have to write some logic into a trigger to enforce this.
With SQL Server 2008 and up, you no longer need to do this. A standard unique index can now be used to give you an ANSI complaint unique index. The trick, is to use a filtered index.
When you create a unique filtered index the uniqueness is only applied to the values which are stored in the index. Values which aren't stored in the index aren't required to be unique. So if we create a unique index on a column where the column is not null, we have an ANSI complaint unique index.
We can test this with a little sample code.
-
CREATE TABLE test
-
(column1 int)
-
go
-
CREATE UNIQUE INDEX ux_test_c1 ON dbo.test
-
(column1)
-
WHERE column1 IS NOT NULL
-
go
-
INSERT INTO test
-
SELECT NULL
-
go
-
INSERT INTO test
-
SELECT NULL
-
go
-
INSERT INTO test
-
SELECT 1
-
go
-
INSERT INTO test
-
SELECT 2
-
go
-
SELECT *
-
FROM test
Denny
