November 1, 2010 at 1:08 am
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!
November 1, 2010 at 3:41 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply