The correct type for materialized paths and hierarchies in general is hierarchyid , not varchar . To get consistent ordering and searching with a text type you'd have to ensure all key values have the exact same length by padding them, eg 000014 , 000014.000005 , etc.
hierarchyid is a binary type but allows converting from and to string representations. It can be sorted and indexed and there are built-in functions to determine parents, children, levels etc.
This example from the documentation shows how easy it is to use such paths
Given this table
CREATE TABLE SimpleDemo
(
Level hierarchyid NOT NULL,
Location nvarchar(30) NOT NULL,
LocationType nvarchar(9) NULL
);
2条答案
按热度按时间vfwfrxfs1#
The correct type for materialized paths and hierarchies in general is hierarchyid , not
varchar
. To get consistent ordering and searching with a text type you'd have to ensure all key values have the exact same length by padding them, eg000014
,000014.000005
, etc.hierarchyid
is a binary type but allows converting from and to string representations. It can be sorted and indexed and there are built-in functions to determine parents, children, levels etc.This example from the documentation shows how easy it is to use such paths
Given this table
and data :
The rows can be orderd with a simple
ORDER BY
. In this caseLevel
is the name of thehierarchyid
column :The expression
CAST(Level AS nvarchar(100))
converts thehierarchyid
path values to their text equivalent.The examples in Lesson 2: Create and Manage Data in a Hierarchical Table go into more detail, showing how to add nodes at any level, search for ancestors etc.
In the
EmployeeOrg
table theOrgNode
field is the primary key.When we know the hierarhcyId of an existing employee, we can find all subordinates with the
IsDescendantOf
function:nue99wik2#
Using the window function ROW_NUMBER to generate an id ordered by path can do the trick :
Demo here