In SQL Server, technically, NULL = NULL is false. By that logic, no NULL is equal to any NULL and all NULLs are distinct. Shouldn't this imply that all NULLs are unique, and a unique index should allow any number of NULLs?

  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Aug 29, 2017 at 9:48

5 Answers 5


Why does it work this way? Because way back when, someone made a design decision without knowing or caring about what the standard says (after all, we do have all kinds of weird behaviors with NULLs, and can coerce different behavior at will). That decision dictated that, in this case, NULL = NULL.

It wasn't a very smart decision. What they should have done is have the default behavior adhere to the ANSI standard, and if they really wanted this peculiar behavior, allow it through a DDL option like WITH CONSIDER_NULLS_EQUAL or WITH ALLOW_ONLY_ONE_NULL.

Of course, hindsight is 20/20.

And we have a workaround, now, anyway, even if it isn't the cleanest or most intuitive.

You can get the proper ANSI behavior in SQL Server 2008 and above by creating a unique, filtered index.


This allows more than one NULL value because those rows are completely left out of the duplicate checking. As an added bonus, this would end up being a smaller index than one that consisted of the entire table if multiple NULLs were allowed (especially when it isn't the only column in the index, it has INCLUDE columns, etc). However, you may want to be aware of some of the other limitations of filtered indexes:


Correct. The implementation of a unique constraint or index in sql server allows one and only one NULL. Also correct that this technically doesn't fit with the definition of NULL but it's one of those things they did to make it more useful even though it isn't "technically" correct. Note a PRIMARY KEY (also a unique index) does not allow NULLs (of course).

  • 1
    This (SQL-Server's) technicality also doesn't fit with the SQL standard. There is a 7-year old Connect item about this issue. Oct 17, 2014 at 18:39
  • @ypercube True. That's why I said it was just the implementation and doesn't really fit in with the definition of NULL. I hadn't thought about the filtered unique index (although I've used it for other things.) Oct 17, 2014 at 19:27
  • @KennethFisher You noted that they did this to make it more useful. I'm curious about that. Do you know why they considered it more useful than the ANSI standard?
    – liquidki
    Jun 8 at 16:15
  • 1
    @liquidki I'll admit the useful part is entirely my own opinion. If the index is on col1, col2, col3 someone tries to insert a second copy of (1, null, 2) I would rather then unique index disallow that rather than allow 1000's of copies just because one of the columns has a null value. Jul 24 at 19:46

This may not be technically accurate, but philosophically it helps me sleep at night...

Like several other have said or alluded to, if you think of NULL as unknown, then you cannot determine whether one NULL value is in fact equal to another NULL value. Thinking of it this way, the expression NULL == NULL should evaluate to NULL, meaning unknown.

A Unique constraint would need a definitive value for the comparison of the column values. In other words, when comparing a single column value against any other column value using the equality operator, it must evaluate to false to be valid. Unknown isn't really false even though it is often treated as falsy. Two NULL values could be equal, or not... it simply can't be definitively determined.

It helps to think of a unique constraint as restricting values that can be determined to be distinct from one another. What I mean by this is if you run a SELECT that looks something like this:

SELECT * from dbo.table1 WHERE ColumnWithUniqueContraint="some value"

Most people would expect one result, given that there is a unique constraint. If you allowed multiple NULL values in ColumnWithUniqueConstraint, then it would be impossible to select a single distinct row from the table using NULL as the compared value.

Given that, I believe that regardless of whether or not it's implemented accurately with respect the definition of NULL, it's definitely a lot more practical in most situations than allowing multiple NULL values.

  • Your Select will do give 1 result, when there is a Unique constraint (in any implementation, not only SQL-Server). What is your point? Oct 18, 2014 at 10:38

First - stop using the phrase "Null value", it will just lead you astray. Instead, use the phrase "null marker" - a marker in a column indicating that the actual value in this column is either missing or inapplicable (but note that the marker does not say which of those options is actually the case¹).

Now, imagine the following (where the database does not have complete knowledge of the modeled situation).

Situation          Database

ID   Code          ID   Code
--   -----         --   -----
1    A             1    A
2    B             2    (null)
3    C             3    C
4    B             4    (null)

The integrity rule we are modelling is "the Code must be unique". The real-world situation violates this, so the database shouldn't allow both items 2 and 4 to be in the table at the same time.

The safest, and least-flexible, approach would be to disallow null markers in the Code field, so there is no possibility of inconsistent data. The most flexible approach would be to allow multiple null markers and worry about uniqueness when values are entered.

The Sybase programmers went with the somewhat-safe, not-very-flexible approach of only allowing one null marker in the table - something commentators have been complaining about ever since. Microsoft have continued this behaviour, I guess for backwards compatibility.

¹ I am sure I read somewhere that Codd considered implementing two null markers - one for unknown, one for inapplicable - but rejected it, but I can't find the reference. Am I remembering correctly?

P.S. My favourite quote about null: Louis Davidson, "Professional SQL Server 2000 Database Design", Wrox Press, 2001, page 52. "Boiled down to a single sentence: NULL is evil."

  • 1
    Allowing a single null doesn't achieve this goal either. Because the missing value might turn out to be the same as the value in one of the other rows. Oct 20, 2014 at 5:13
  • 1
    What @MartinSmith said. What if you have a check constraint CHECK (Value IN ('A','B','C','D'))? Then both SQL-Server's implementation and the SQL standard allow the table to have 5 rows (one row for each value plus 1 with NULL.) Then, arguably, while the database is consistent with its constraints, it's not consistent with the designer's intent for the table to have max of 4 rows. There is no value that the NULL can be changed into that will not violate a constraint, unless one or more rows are deleted. Oct 20, 2014 at 8:49
  • 1
    The fact that the standard would allow 6 even 106 rows instead of 5 does not change that they both fail the some way in this scenario. Oct 20, 2014 at 8:50
  • @Martin Smith, it might, but then again, it might not - the database server can't tell so it doesn't risk it and takes the safe route. That's what the Sybase (I presume) programmers decided, causing annoyance ever since (at least as far back as Inside SQL Server 6.5, the oldest book on my bookshelf, where Ron Soukup makes much the same comment that Aaron Bertrand does in his answer). I guess it could be worse - they could have mandated no null markers. :-) Oct 20, 2014 at 9:49
  • 3
    @GreenstoneWalker - It doesn't take the "safe" route. It assumes that the missing value won't conflict. CREATE TABLE #T(A INT NULL UNIQUE);INSERT INTO #T VALUES (1),(NULL);UPDATE #T SET A = 1 WHERE A IS NULL; will raise an error. According to your theory of the design motivations it ought to have prevented the insertion of NULL in the first case - because the incomplete knowledge means there is no guarantee that the value is different. Oct 20, 2014 at 12:05

One of the major purposes of a UNIQUE constraint is to prevent duplicate records. If one needs to have a table in which there can be multiple records where a value is "unknown", but no two records are allowed to have the same "known" value, then the unknown values should be assigned artificial unique identifiers before they are added to the table.

There are a few rare cases in which a column which has a UNIQUE constraint and contains a single null value; for example, if a table contains a mapping between column values and localized text descriptions, a row for NULL would make it possible to define the description that should appear when that column in some other table is NULL. The behavior of NULL allows for that usage case.

Otherwise, I see no basis for a database with a UNIQUE constraint on any column to allow the existence of many identical records, but I see no way to prevent that while allowing multiple records whose key values are not distinguishable. Declaring that NULL isn't equal to itself won't make NULL values distinguishable from each other.

  • 3
    Artificial unique identifiers are a joke, sorry. How are you going to do that for a VIN? If you don't know what it is, why make something up? Just to take up extra disk space? Seems like nonsense to work around some other problem (like not wanting to write the application in such a way that it gracefully handles NULLs). If you absolutely need to know why something is NULL (exists but unknown vs. know it doesn't exist vs. don't know or care if it exists, for example), then add some kind of status column. Tokens just lead to awkward trickle-down code to deal with them. Oct 18, 2014 at 21:47
  • A lot depends upon the purpose of the uniqueness constraint. If a field will be used as an identifier, it shouldn't be null. In cases (as with VINs) where business rules would suggest that when an item appears twice, one of them must be wrong, but some items may be "don't know", a uniqueness constraint doesn't feel like the proper approach. If one has a vehicle with a known VIN, and it conflicts with another in the database, one may know that at least one of the VINs is wrong, but it would be better to have the database report the believed value for both records than guess that one is right.
    – supercat
    Oct 18, 2014 at 22:02
  • @AaronBertrand: There are some cases where a possibly-null unique-if-not-null field would need to be a surrogate key couldn't be established in advance of populating the field (e.g. "spouse ID"), but in situations like that a "unique" constraint would be insufficient; it would be necessary that if X.Spouse is non-null, X.Spouse.Spouse=X. Incidentally, something like "spouse" might also be handled by saying that the record for an unmarried person shouldn't have "NULL" as a spouse, but rather its own ID, in which case the X.spouse.spouse=X rule could apply to everyone.
    – supercat
    Oct 18, 2014 at 22:12

Your Answer

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

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