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

  • 0 down vote favorite

    Hi friends,

    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 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 why i can create the index in a separate statement, but not in the table creation?

    Thank you!

  • When you specify UNIQUE in the table's definition you're not creating a unique index, you're creating a unique constraint.

    It's equivalent to this:

    CREATE TABLE t (

    a NVARCHAR(100) SPARSE NULL

    )

    ALTER TABLE t ADD CONSTRAINT uq_a UNIQUE (a)

    and that gives exactly the same error as defining unique in the table's creation.

    While I can't find it anywhere in books online, I would guess that unique constraints can't reference sparse columns but unique indexes can. This is likely to allow for filtered unique indexes. Constraints can't be filtered.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply