Tell me more ×
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. It's 100% free, no registration required.

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

migrated from stackoverflow.com Aug 23 '12 at 12:04

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

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