varchar(max) knotted attributes

Ivo_Clemens

Hi,

I've modeled a varchar(max) column as a knotted attribute that i use to store thumbnail images. This results in error:

----------------------------------- [Knot Table] -------------------------------------

-- THU_Thumbnail table

--------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'THU_Thumbnail' and type LIKE '%U%')

CREATE TABLE [dbo].[THU_Thumbnail] (

THU_ID int identity(1, 1) not null,

THU_Thumbnail varchar(max) not null unique,

Metadata_THU int not null,

primary key (

THU_ID asc

)

);

This results in error:

Column 'THU_Thumbnail' in table 'THU_Thumbnail' is of a type that is invalid for use as a key column in an index.

I guess this is a (small) bug.

I get around it by manually removing "unique" from the varchar(max) line, and maintaining uniqueness in my ETL.

Ivo

Roenbaeck

Thanks for spotting this. I think the best way would be to let the user decide if the uniqueness constraint should be turned off in a knot, using a checkbox in the tool. I suspect that the same thing happens for other data types as well (blobs?).

The reason why SQL Server is complaining is that there is a maximum key length for indexes. It's 900 bytes if I remember correctly, and a varchar(max) can store 2GB or something like that.

The reason we add the uniqueness constraint is that the query optimizer can use this fact to speed up queries that contain conditions on a knot value, for example "where GEN_Gender = 'Male'".