I have to define a Primary key on a table which has following 4 columns and its data types.
GSiteID hirarchyid,,
SiteID varchar(10),
GComID hirarchyid,
ComID nvarchar(10)
As we know there is limit of 900 bytes when we design any key on a table. whereas hirarchyid is a variable data type consumes 892 bytes so naturally my above case exceed limit of 900 bytes. In some of the posts I read we can use ToString() and other binary formatter to reduce the size of key length.
Can any one explain me or refer such article which shows this case where I can use above two alternatives of ToString and binary formatter which is more compressed to define my key? If someone provides me exact syntext to form my above four field as key in the same seq.
It would be helpful to me. I have already seen examples illustrating use of tostring() into select claues but I want explicit example of define a unique key or index key for hirarchyid so I can manage my key in 900 bytes limit.
As I know that max 10 level is max my tree will evolve so please consider this also while providing suggestion.
ID INT IDENTITY(1,1)column to be your primary key – marc_s Aug 22 '12 at 11:06