1

I want to know how many null values can a unique key has in sql server. I have read many articles and every one has different answers some says unlimited null are accepted and some says only one null is accepted , according to me only one null value is accepted am i right or wrong?

Ashu
  • 462
  • 3
  • 16
  • I have tried its accepting only one null value – Ashu May 19 '14 at 14:38
  • @AshU: there's your answer! Since the index has to be unique - every value (and non-value like `NULL`) can occur **only once** - that's the **whole point** of a **unique index** , no??? – marc_s May 19 '14 at 14:40
  • It's a little more complicated than that. In ANSI sql null does not equal null, therefore many nulls can be unique. You can SET ANSI_NULLS on or off. By default it's off in MS SQL. However, MS has said that in future versions it will default to ON and you won't be able to set it off. – RyanB May 19 '14 at 14:47
  • @RyanB could you please explain me with a small example , that how can I insert more than one null values in my unique column. – Ashu May 19 '14 at 15:05

1 Answers1

3

The answer is: it depends. There are way to create a unique index that allows multiple nulls in MS SQL Server, but it's not the default. The default in MS is to allow just one NULL. But that's not the ANSI standard. ANSI standards 92, 99, and 03 explicitly allow multiple nulls with unique.

The answers here give a good explanation: How do I create a unique constraint that also allows nulls?

UPDATE with example: remove the UNIQUE constraint from the table definition, then add the index like this

CREATE UNIQUE NONCLUSTERED INDEX idx_name
ON table_name(column_name)
WHERE column_name IS NOT NULL;
Community
  • 1
  • 1
RyanB
  • 757
  • 4
  • 11
  • I cannot understand , how to insert more than one null value in my unique column – Ashu May 19 '14 at 14:59
  • You can't do it with a regular UNIQUE column. But you can create a UNIQUE INDEX with WHERE column IS NOT NULL. That would allow you to insert multiple nulls into the column. The nulls wouldn't be indexed. – RyanB May 19 '14 at 15:06
  • could you please explain me with a small example , that how to do all this – Ashu May 19 '14 at 15:13
  • I have also add : set ANSI_NULLS On – Ashu May 19 '14 at 15:29