How to use the SQL Server 2008 HierarchyID field type to construct a tree classification table

Using database tables to construct infinite-level classifications, we usually use the following approach

The structure of this master relationship is clear, but there will be some small problems in the query; Query the descendants of all subclasses and subclasses of the current category, recursive queries appear, and in practical applications such queries are common, such as we want to query all technology-related article information;

Resolving such problems is usually done with cursors before SqlServer2005, but people familiar with the internal mechanisms of the database know that the performance and other problems associated with using cursors are more serious

Under the SqlServer2005, you can choose to use a CTE to do recursive queries, such as to query all technical articles, you can use this approach, upward recursion and downward recursive basic implementation is basically consistent

* * Query All "technical Articles" category and its descendants class * *

With T_category (Id,name,parentid) as

(

SELECT Categoryid,categoryname,parentid from Category WHERE categoryid=3

UNION All

SELECT A.categoryid,a.categoryname, A.parentid from Category A, T_category B where a.parentid=b.id

)

SELECT * from T_category

This way query is more concise, but because the internal database is a recursive query method, its efficiency is still not high;

In order to achieve a concise and efficient query, it is common practice to add redundant fields, such as adding a "Path" field:

Query with a fuzzy query for the left match, such as query all the categories of articles so write, Select * from sitecategory where path like ' article% '

After the path is indexed, the efficiency of this query is still quite high, so this method is also a general way of design;

Here is another way of designing, using the HierarchyID field type in SqlServer2008 to construct this structure;

Create a Customer Area information table "Customer", structured as follows