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.

  • 2
    The only way is to not include the hierarchyid columns in the primary key. You might need to add a ID INT IDENTITY(1,1) column to be your primary key – marc_s Aug 22 '12 at 11:06
  • 1
    Create a new column (int, long, uniqueidentifier) which acts as primary key column. Depending on your requirements, either create a unique index on GSiteID,SiteID,GComID,ComID, or two unique indexes on GSiteID,SiteID and GComID,ComID. – devio Aug 22 '12 at 12:10
  • Currently this is the only alrenative I can see as of today. Anyway Thanks for your reply. – Nilkanth Desai Aug 23 '12 at 1:36

Are you trying to create a key column that represents the a tree structure? If so, you could use concatenated, 32-bit hash values of each node in the tree to represent tre hierarchy.

For example, assume you wanted to represent the following:


You would store the following three rows with these keys:

Size = 4B:  Hash("C:") 
Size = 8B:  Hash("C:")+Hash("MyData")
Size = 12B: Hash("C:")+Hash("MyData")+Hash("MyFolder")
Size = 16B: Hash("C:")+Hash("MyData")+Hash("MyFolder")+Hash("MySubFolder")

...where + represents binary concatenation. You would then store the concatenated key in a VARBINARY(900), which leaves you space for a tree that is 900 / 4 = 225 nodes deep. Hopefully this would be enough? This can be indexed.

Note that you don't need the hash to be without collisions, as long as you can reduce the number of keys that must be searched.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy