Share via

hierarchyid data type used as a field to categorize products

Alen Cappelletti 992 Reputation points
2020-12-15T21:59:03.933+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
0 comments
Accepted answer
  1. MelissaMa-MSFT 24,191 Reputation points
    2020-12-16T02:56:04.177+00:00

    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

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest