Modern Directory Table Structure

directory.sql

CREATE TABLE [dbo].[Directories]
(
	[AggregateId] UNIQUEIDENTIFIER NOT NULL,
	[Name] NVARCHAR(64) NOT NULL,
	[Route] NVARCHAR(128) NOT NULL UNIQUE,
	[Route_Hash] AS CAST(HASHBYTES('SHA1', [Route]) AS VARBINARY(20)) PERSISTED UNIQUE,
	[Depth] TINYINT NOT NULL,
	[ParentId] UNIQUEIDENTIFIER NULL,
	CONSTRAINT [PK_Directories] PRIMARY KEY NONCLUSTERED (
		[AggregateId] ASC
	)
)
GO

CREATE CLUSTERED INDEX [CX_Directories_AggregateId_ParentId_Depth]
	ON [dbo].[Directories] (AggregateId ASC, ParentId ASC, Depth ASC);
GO