如何在SQL Server中更优存储图/层级结构？寻求改进方案

我来给你几个在SQL Server里存储图/层级结构的更优方案，都是实际项目里验证过的，比你现在把所有信息塞单表的方式灵活太多：

这个模型的核心思路是给每个节点记录它的直接父节点，结构简单，维护成本低，适合大多数层级场景。

建表语句： #

CREATE TABLE [OperationalModelNodes] (
    [Id] INT PRIMARY KEY IDENTITY(1,1), -- 节点唯一ID
    [Name] VARCHAR(150) NOT NULL,
    [Code] VARCHAR(10) NOT NULL,
    [OrgId] INT, -- 节点所属组织ID
    [ParentNodeId] INT NULL FOREIGN KEY REFERENCES [OperationalModelNodes]([Id]) -- 父节点ID，根节点为NULL
);

插入示例（对应你原来的数据逻辑）： #

-- 根节点（ParentNodeId为NULL）
INSERT INTO [OperationalModelNodes] ([Name], [Code], [OrgId], [ParentNodeId])
VALUES ('x', '1', NULL, NULL);

-- 一级子节点（父节点是ID=1的根节点）
INSERT INTO [OperationalModelNodes] ([Name], [Code], [OrgId], [ParentNodeId])
VALUES ('x', '2', 1, 1), ('x', '3', 1, 1);

-- 二级子节点（父节点是ID=2的节点）
INSERT INTO [OperationalModelNodes] ([Name], [Code], [OrgId], [ParentNodeId])
VALUES ('x', '4', 2, 2), ('x', '5', 2, 2), ('x', '6', 2, 2);

-- 二级子节点（父节点是ID=3的节点）
INSERT INTO [OperationalModelNodes] ([Name], [Code], [OrgId], [ParentNodeId])
VALUES ('x', '7', 3, 3);

-- 三级子节点（父节点是ID=4的节点）
INSERT INTO [OperationalModelNodes] ([Name], [Code], [OrgId], [ParentNodeId])
VALUES ('x', '8', 4, 4);

优缺点： #

• ✅ 优点：结构简单，插入/更新节点很方便，容易理解
• ❌ 缺点：查询多层级（比如找某个节点的所有子孙）需要递归查询（SQL Server里用CTE实现），数据量极大时性能会有影响

这个模型会给每个节点存储从根节点到它的完整路径，比如用/1/2/4/这样的格式，不用递归就能快速找到路径上的所有节点。

建表语句： #

CREATE TABLE [OperationalModelPath] (
    [Id] INT PRIMARY KEY IDENTITY(1,1),
    [Name] VARCHAR(150) NOT NULL,
    [Code] VARCHAR(10) NOT NULL,
    [OrgId] INT,
    [Path] VARCHAR(MAX) NOT NULL -- 存储根到当前节点的路径，比如'/1/'代表根节点，'/1/2/'代表ID=2的节点
);

插入示例： #

INSERT INTO [OperationalModelPath] ([Name], [Code], [OrgId], [Path])
VALUES 
('x', '1', NULL, '/1/'),
('x', '2', 1, '/1/2/'),
('x', '3', 1, '/1/3/'),
('x', '4', 2, '/1/2/4/'),
('x', '5', 2, '/1/2/5/'),
('x', '6', 2, '/1/2/6/'),
('x', '7', 3, '/1/3/7/'),
('x', '8', 4, '/1/2/4/8/');

查询示例（找ID=2节点的所有子孙）： #

SELECT * FROM [OperationalModelPath] WHERE [Path] LIKE '/1/2/%';

优缺点： #

• ✅ 优点：查询路径、子孙/祖先节点速度快，不用递归
• ❌ 缺点：插入/更新节点时需要维护路径字符串，容易出错；路径长度有限制（虽然用VARCHAR(MAX)缓解，但还是有潜在问题）

这个模型用一张额外的表存储所有节点之间的祖先-后代关系，包括直接和间接的，查询时不用递归，直接关联这张表就行。

建表语句： #

-- 节点主表
CREATE TABLE [OperationalModelClosureNodes] (
    [Id] INT PRIMARY KEY IDENTITY(1,1),
    [Name] VARCHAR(150) NOT NULL,
    [Code] VARCHAR(10) NOT NULL,
    [OrgId] INT
);

-- 闭包关系表，存储所有祖先-后代对
CREATE TABLE [OperationalModelClosureRelations] (
    [AncestorId] INT FOREIGN KEY REFERENCES [OperationalModelClosureNodes]([Id]),
    [DescendantId] INT FOREIGN KEY REFERENCES [OperationalModelClosureNodes]([Id]),
    [Depth] INT NOT NULL, -- 祖先到后代的层级深度，比如父节点Depth=1，祖父节点Depth=2
    PRIMARY KEY ([AncestorId], [DescendantId])
);

插入示例： #

-- 先插入节点
INSERT INTO [OperationalModelClosureNodes] ([Name], [Code], [OrgId])
VALUES 
('x', '1', NULL),
('x', '2', 1),
('x', '3', 1),
('x', '4', 2),
('x', '5', 2),
('x', '6', 2),
('x', '7', 3),
('x', '8', 4);

-- 插入闭包关系（每个节点自己也是自己的祖先，Depth=0）
INSERT INTO [OperationalModelClosureRelations] ([AncestorId], [DescendantId], [Depth])
VALUES 
(1,1,0), (2,2,0), (3,3,0), (4,4,0), (5,5,0), (6,6,0), (7,7,0), (8,8,0);

-- 插入直接父-子关系（Depth=1）
INSERT INTO [OperationalModelClosureRelations] ([AncestorId], [DescendantId], [Depth])
VALUES 
(1,2,1), (1,3,1), (2,4,1), (2,5,1), (2,6,1), (3,7,1), (4,8,1);

-- 插入间接关系（比如1是4的祖先，Depth=2；1是8的祖先，Depth=3）
INSERT INTO [OperationalModelClosureRelations] ([AncestorId], [DescendantId], [Depth])
VALUES 
(1,4,2), (1,5,2), (1,6,2), (1,7,2), (1,8,3), (2,8,2);

查询示例（找ID=1节点的所有子孙）： #

SELECT n.* FROM [OperationalModelClosureNodes] n
JOIN [OperationalModelClosureRelations] r ON n.Id = r.DescendantId
WHERE r.AncestorId = 1 AND r.Depth > 0;

优缺点： #

• ✅ 优点：复杂层级查询速度极快，支持任意深度的祖先/子孙查询
• ❌ 缺点：插入/更新节点时需要维护大量的关系记录，数据冗余度高，维护成本大

从SQL Server 2017开始，官方支持原生的图形数据库功能，专门用来存储节点和边的关系，适合真正的图结构（不是简单的树状层级，比如节点之间有多个关联）。

建表语句： #

-- 创建节点表
CREATE TABLE [OperationalModelGraphNodes] (
    [Id] INT,
    [Name] VARCHAR(150),
    [Code] VARCHAR(10),
    [OrgId] INT,
    PRIMARY KEY ([Id])
) AS NODE;

-- 创建边表，用来记录节点之间的关联关系（比如父子、同层级关联等）
CREATE TABLE [OperationalModelGraphEdges] (
    [RelationshipType] VARCHAR(50) NOT NULL, -- 比如'Parent'、'SameVertex'
    PRIMARY KEY ($edge_id)
) AS EDGE;

插入示例： #

-- 插入节点
INSERT INTO [OperationalModelGraphNodes] ([Id], [Name], [Code], [OrgId])
VALUES 
(1, 'x', '1', NULL),
(2, 'x', '2', 1),
(3, 'x', '3', 1),
(4, 'x', '4', 2),
(5, 'x', '5', 2),
(6, 'x', '6', 2),
(7, 'x', '7', 3),
(8, 'x', '8', 4);

-- 插入父子关系边
INSERT INTO [OperationalModelGraphEdges] ($from_id, $to_id, [RelationshipType])
VALUES 
((SELECT $node_id FROM [OperationalModelGraphNodes] WHERE Id=1), (SELECT $node_id FROM [OperationalModelGraphNodes] WHERE Id=2), 'Parent'),
((SELECT $node_id FROM [OperationalModelGraphNodes] WHERE Id=1), (SELECT $node_id FROM [OperationalModelGraphNodes] WHERE Id=3), 'Parent'),
((SELECT $node_id FROM [OperationalModelGraphNodes] WHERE Id=2), (SELECT $node_id FROM [OperationalModelGraphNodes] WHERE Id=4), 'Parent'),
((SELECT $node_id FROM [OperationalModelGraphNodes] WHERE Id=2), (SELECT $node_id FROM [OperationalModelGraphNodes] WHERE Id=5), 'Parent'),
((SELECT $node_id FROM [OperationalModelGraphNodes] WHERE Id=2), (SELECT $node_id FROM [OperationalModelGraphNodes] WHERE Id=6), 'Parent'),
((SELECT $node_id FROM [OperationalModelGraphNodes] WHERE Id=3), (SELECT $node_id FROM [OperationalModelGraphNodes] WHERE Id=7), 'Parent'),
((SELECT $node_id FROM [OperationalModelGraphNodes] WHERE Id=4), (SELECT $node_id FROM [OperationalModelGraphNodes] WHERE Id=8), 'Parent');

-- 插入同层级关联边（比如你原来的RelatedOrgIdOnSameVertex）
INSERT INTO [OperationalModelGraphEdges] ($from_id, $to_id, [RelationshipType])
VALUES 
((SELECT $node_id FROM [OperationalModelGraphNodes] WHERE Id=2), (SELECT $node_id FROM [OperationalModelGraphNodes] WHERE Id=3), 'SameVertex');

查询示例（找所有父节点是1的子节点）： #

SELECT n.* FROM [OperationalModelGraphNodes] n
JOIN [OperationalModelGraphEdges] e ON n.$node_id = e.$to_id
JOIN [OperationalModelGraphNodes] parent ON parent.$node_id = e.$from_id
WHERE parent.Id = 1 AND e.RelationshipType = 'Parent';

优缺点： #

• ✅ 优点：原生支持图结构，能灵活存储各种复杂关联（不仅仅是层级），SQL Server专门优化了图查询的性能
• ❌ 缺点：需要熟悉SQL Server的图语法，学习成本略高；对于简单层级来说有点“大材小用”

总结怎么选？ #

• 如果是简单的树状层级，优先用邻接表模型，成本低易维护；
• 如果需要快速查询节点路径，选路径枚举模型；
• 如果经常需要复杂的层级查询（比如找所有祖先/子孙），可以考虑闭包表模型；
• 如果是复杂的图结构（节点之间有多种关联关系，不是单纯的树），直接用SQL Server原生图形表，这是最适合的方案。

内容的提问来源于stack exchange，提问作者Glock