hierarchyid data type used as a field to categorize products

Question

Hi all,
I'm reading about this type.. but sincerely I've never used it. It seems to me very versatile and powerful ... also to use it beyond the simple concept of its original development ... as a rigid hierarchy. Do you think it could be used as a hierarchy for product categorization ... let's say millions of products with a hierarchy exaggerating up to 7/8 sub-levels?
Thanks ALEN, ITALY

Answer 1

Hi @Alen Cappelletti ,

Thank you so much for posting here.

As mentioned in this article, SQL Server hierarchyID is a built-in data type designed to represent trees, which are the most common type of hierarchical data.

You could use HierarchyID with the following business scenarios:

• Organizational structure
• Folders, subfolders, and files
• Tasks and subtasks in a project
• Pages and subpages of a website
• Geographical data with countries, regions, and cities

Unfortunately, using hierarchyID can’t resolve all issues:

• SQL Server can’t guess what the child of a parent is. You have to define the tree in the table.
• If you don’t use a unique constraint, the generated hierarchyID value won’t be unique. Handling this problem is the developer’s responsibility.
• Relationships of a parent and child nodes are not enforced like a foreign key relationship. Hence, before deleting a node, query for any descendants existing.

In addition, as mentioned in this article, compared with one normal query without the use of the HierarchyID data type, the performance of the one with the use of the HierarchyID is better. It could be a great improvement if you have millions of products with a hierarchy in tables even there is no index used.

Actually it is still hard to say whether it is a good choice to use hierarchyid for product categorization (millions of products) in your case. You could begin with a test of some sample data and compare the performance with and without the use of HierarchyID. Then you could proceed with next steps.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table