I am considering using `hierarchyid` type as a part of clustered primary key. The key itself will be composed by the following types: - BIGINT - INT - HIERARCHYID in the given order. Generally (in 90% of the cases), the value of the `hierarchyid` column will be set to `/` - in other words there will be only one record for the given `BIGINT - INT ` value pair. I have read some similar articles/questions ([Clustered Index Design Guidelines], [SQL primary key: integer vs varchar ]), and generally the only thing I found is about the use of wide columns. I in the [official documentation] is said that the length of `hierarchyid` can be 5 bytes or more, but if you create a table you can see the `Length` is exactly 892 bytes. Is this mean that the size of the primary key is increased with `892` bytes always, or it is increased with the actual size of the column only (for example 5 bytes)? Also, if someone have experience with using the such column as primary key it will be grateful to share some experience with us? :
I would shy away from using a CLR data type as a part of a primary key. It's too subject to change and changes in primary keys have serious impacts on all the related tables. Also, as you note, 892 bytes is a very wide key value. That wide key will have to be used on all the child tables. If that key is also the clustered index for the table, any nonclustered indexes on the table will also be storing that wide key. It's not something I would suggest in a database I was designing.