HIERARCHYID
HIERARCHYID is a SQL data type in the Data Types category. SQL Server: built-in data type for hierarchical data trees. Supports efficient ancestor/descendant queries. The syntax is HIERARCHYID. It returns hierarchical type. A typical example: CREATE TABLE org_chart ( node_id HIERARCHYID PRIMARY KEY, employee_name VARCHAR(100), title VARCHAR(100) ); INSERT INTO org_chart VALUES (HIERARCHYID::Parse('/1/'), 'CEO', 'Chief Executive'), (HIERARCHYID::Parse('/1/1/'), 'CTO', 'Tech Head'), (HIERARCHYID::Parse('/1/2/'), 'CFO', 'Finance Head'), (HIERARCHYID::Parse('/1/1/1/'), 'Dev Lead', 'Development Lead'); -- Get descendants of CTO: SELECT * FROM org_chart WHERE node_id.IsDescendantOf(HIERARCHYID::Parse('/1/1/')) = 1; -- Get ancestor: SELECT node_id.GetAncestor(1) AS parent FROM org_chart WHERE employee_name = 'Dev Lead'; A close relative is INT, which standard integer type. Range: -2147483648 to 2147483647 (signed). Use BIGINT for larger values. A close relative is VARCHAR(n), which variable-length string with a maximum length. Most common string type. Efficient for varying-length data. A close relative is TEXT, which for large text content (up to 65535 bytes in MySQL). Variants: TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.