November 19, 2015 at 4:00 am
Hi Team,
CREATE TABLE search_T
(
num INT,
value VARCHAR(max)
,UNIQUE CLUSTERED (num,value)
)
ERROR : Column 'value' in table 'search_T' is of a type that is invalid for use as a key column in an index.
I want to create a unique index on two columns.
Please suggest...
November 19, 2015 at 4:05 am
You can't create an index on a blob column, that's varchar(max), nvarchar(max), varbinary(max), XML or the old TEXT, NTEXT, IMAGE
If you want to enforce uniqueness across the two columns, the maximum size of the two combined cannot exceed 900 bytes. So an INT and a VARCHAR(895) will work
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
November 19, 2015 at 4:06 am
Maximum length data types are not indexable
Index key length maximum is 900 bytes.
Int is 4 bytes, that leaves 896 bytes, so you will need to change your varchar(max) to varchar(896) or below to prevent any insert/update errors.
You can go above 896 but you may get insert update errors if the string goes above 896 bytes
November 19, 2015 at 3:27 pm
To have the index automatically built, you can add a persisted, computed column, like this:
CREATE TABLE search_T
(
num INT,
value VARCHAR(max),
value_for_indexing as cast(left(value,896) as varchar(896)) persisted
,UNIQUE CLUSTERED (num,value_for_indexing)
)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply