DROP PROCEDURE [CreateRegistryKey]
DROP TABLE [RegistryValue];
DROP TABLE [RegistryKey];
GO
CREATE TABLE [RegistryKey]
(
    [HID]     HIERARCHYID   NOT NULL,
    [HLevel]  AS ([HID].[GetLevel]())     PERSISTED,
    [HParent] AS ([HID].[GetAncestor](1)) PERSISTED,
    [Name]    NVARCHAR(256) NOT NULL
)
GO
CREATE TABLE [RegistryValue]
(
    [HID]     HIERARCHYID    NOT NULL,
    [Name]    NVARCHAR(256)  NOT NULL,
    [Value]   VARBINARY(MAX) NOT NULL,
)
GO
ALTER TABLE [RegistryKey]   ADD CONSTRAINT [RegistryKey/PK]   PRIMARY KEY CLUSTERED ([HID], [Name]);
CREATE UNIQUE NONCLUSTERED INDEX [RegistryKey/UK] ON [RegistryKey]([HID])
GO
INSERT INTO [RegistryKey]([HID], [Name]) VALUES (hierarchyid::GetRoot(), '');

GO
ALTER TABLE [RegistryValue] ADD CONSTRAINT [RegistryValue/PK] PRIMARY KEY CLUSTERED ([HID], [Name]);
GO
ALTER TABLE [RegistryValue] ADD CONSTRAINT [RegistryValue/FK] FOREIGN KEY([HID]) REFERENCES [RegistryKey]([HID]);
GO
CREATE PROCEDURE [CreateRegistryKey](
    @ParentID HIERARCHYID,
    @Name     NVARCHAR(256))
AS
BEGIN
    DECLARE @ParentHierarchyID hierarchyid;
    DECLARE @SiblingHierarchyID hierarchyid;
    DECLARE @HierarchyID hierarchyid;
    
    IF @ParentID IS NULL
        SET @ParentID = hierarchyid::GetRoot();

    SELECT
        @ParentHierarchyID = [HID]
    FROM
        [RegistryKey]
    WHERE
        [HID] = @ParentID;

    SELECT
        @SiblingHierarchyID = MAX([HID])
    FROM
        [RegistryKey]
    WHERE
        [HID].GetAncestor(1) = @ParentHierarchyID;

    SET @HierarchyID = @ParentHierarchyID.GetDescendant(@SiblingHierarchyID, NULL);

    INSERT INTO
        [RegistryKey]([HID], [Name])
    VALUES
        (@HierarchyID, @Name);
END
GO
CREATE PROCEDURE [CreateRegistryValue](
    @HID   HIERARCHYID,
    @Name  NVARCHAR(256),
    @Value VARBINARY(MAX))
AS
BEGIN
    INSERT INTO
        [RegistryValue]([HID], [Name], [Value])
    VALUES
        (@HID, @Name, @Value);
END
GO
EXEC [CreateRegistryKey] NULL, 'A';
EXEC [CreateRegistryKey] NULL, 'ZZ';

DECLARE @Hid1 HIERARCHYID;
DECLARE @Hid2 HIERARCHYID;
SELECT @Hid1 = [HID] FROM [RegistryKey] WHERE [Name] = 'A';
SELECT @Hid2 = [HID] FROM [RegistryKey] WHERE [Name] = 'ZZ';

EXEC [CreateRegistryValue] @Hid1, 'P', 0x01;
EXEC [CreateRegistryValue] @Hid1, 'Q', 0x02;
EXEC [CreateRegistryValue] @Hid2, '', 0x03;

SELECT * FROM [RegistryKey];
SELECT * FROM [RegistryValue];