Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

migrated from Aug 23 '12 at 12:04

This question came from our site for professional and enthusiast programmers.

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
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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.