(TSQL) unique index on nvarchar(200) sparse null column

I'm facing a confusing problem. If you create a table like the following, you'll get an error:

CREATE TABLE t
(
 a NVARCHAR(100) SPARSE
                 NULL UNIQUE
)

Msg 1919, Level 16, State 2, Line 1
Column 'a' in table 't' is of a type that is invalid for use as a key column in an index.
Msg 1750, Level 16, State 0, Line开发者_Go百科 1
Could not create constraint. See previous errors.

But if you create the table first, then create the unique index like this, everything works.

CREATE TABLE t
(

 a NVARCHAR(100) SPARSE
                 NULL
)

CREATE UNIQUE NONCLUSTERED INDEX t_a ON dbo.t
(
a
)

Anyone can help me to explain it please?

Thank you!

---

I don't know why, but from MSDN SPARSE columns

A sparse column cannot be part of a clustered index or a unique primary key index.

Now, into conjecture land...

It kind of makes sense because a UNIQUE constraint can not be filtered, whereas an explicit index can. Hence the UNIQUE constraint is disallowed but allowed via CREATE INDEX where you have an implied filter.

I'd also say disallowed for clustered indexes because every non-clustered index refers to the clustered index + clustered indexes have to be internally unique if not explicitly ("uniquifier"). So every row must exist.

Taken together, you have to have something for unique and/or clustered: which would defeats the point of using SPARSE... no?

---

You are trying to do this, but:

CREATE TABLE t 
( 
     a NVARCHAR(100) SPARSE NULL 
         CONSTRAINT t_a UNIQUE NONCLUSTERED (a)
) 

...you can't create a unique index on a column with many NULL values. For that you need to create a Filtered Index. i.e.

CREATE NONCLUSTERED INDEX t_a ON dbo.t(a) 
WHERE a IS NOT NULL

---

It's definitely strange that you can create the index in a separate statement, but not in the table create.

The reason is probably that it doesn't make a lot of sense to create a unique index on a sparse column. The entire point of the sparse column is to store null efficiently, and a unique index would only allow one row with a null !

insert t values (null)
insert t values (null)
-->

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.t' with unique index 't_a'.
The statement has been terminated.