CTE ها در SQLERVER - مفاهیم، کاربرد ها و نگاهی به HierarchyID

اگر تجربه‌ کار با SQL Server را داشته باشید، احتمالاً نام CTE یا Common Table Expression برایتان آشناست. بسیاری از توسعه‌ دهندگان در ابتدا تصور می‌کنند CTE صرفاً برای کوئری‌های پیچیده به کار می‌رود یا ساختاری تخصصی است؛ اما حقیقت این است که CTE ابزاری بسیار کاربردی برای خوانایی بهتر، مدیریت منطقی کوئری‌ها و به‌خصوص پردازش داده‌های سلسله‌ مراتبی است.
در این میان، نوع داده‌ HierarchyID نیز نقش مهمی دارد و ترکیب این دو مفهوم می‌تواند در بسیاری از سناریوهای واقعی مفید و کارآمد باشد.

CTE دقیقاً چیست و از کجا وارد SQL Server شد؟

CTE در SQL Server 2005 معرفی شد. پیش از آن، برای مدیریت کوئری‌ های پیچیده معمولاً از Subquery های تو در تو یا Temporary Tableها استفاده می‌شد؛ اما این روش‌ ها در مواردی خوانایی را کاهش می‌دادند یا نگه‌داری کوئری را دشوار می‌کردند.

CTE در واقع یک «عبارت جدولی موقت» است که تنها در همان دستور SQL قابل استفاده است و به صورت زیر تعریف می‌شود:

WITH MyCTE AS (
    SELECT ...
)
SELECT * FROM MyCTE;

استفاده از این ساختار باعث می‌شود بتوانیم بخش‌ های مختلف یک کوئری پیچیده را مرحله‌ به‌ مرحله تنظیم کنیم و از آن‌ ها در ادامه استفاده کنید.

مزایای استفاده از CTE

افزایش خوانایی و نظم کوئری‌ ها
هنگامی که کوئری‌ های چند بخشی می‌نویسیم، استفاده از CTE به تفکیک منطقی بخش‌ های مختلف کمک می‌کند.

جایگزینی مناسب برای Subquery های عمیق
به جای اینکه چندین Subquery داخل یکدیگر قرار دهیم، با CTE می‌توانیم هر بخش را تمیز و مستقل تعریف کنیم.

اجرای کوئری‌ های بازگشتی (Recursive)
یکی از مهم‌ترین قابلیت‌ های CTE، امکان نوشتن کوئری‌ های بازگشتی است؛ به‌ویژه برای داده‌ هایی که ساختار درختی دارند. (در این مورد باید بیشتر صحبت کنیم. پایین تر توضیح میدم)

بدون نیاز به ایجاد جدول موقت
CTE بدون سربار اضافی، تنها در محدوده‌ کوئری فعال است و مانند Temporary Table نیاز به ایجاد و حذف ندارد. یعنی لایف سایکلش اینطوری هست که ابتدا CTE رو میسازیم و سپس ازش استفاده میکنیم و بعد از اون از بین میره و حافظه ای اشغال نمیکند.

فرض کنید یک جدول Employees دارید:

CREATE TABLE Employees (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100),
    Salary INT
);

یک CTE ساده برای انتخاب کارمندانی با حقوق بالاتر از مقدار مشخص:

WITH SalaryCTE AS (
    SELECT Name, Salary
    FROM Employees
    WHERE Salary > 10000000
)
SELECT *
FROM SalaryCTE;

همین ساختار ساده می‌تواند در Query های بزرگ‌ تر بسیار کاربردی‌ تر شود.

CTE بازگشتی چیست؟

ساختار های درختی در دنیای واقعی بسیار رایج‌ اند:
سازمان‌ها، دسته‌بندی محصولات، ساختار پوشه‌ها و …

CTE بازگشتی این امکان را فراهم می‌کند که از یک نقطه شروع کنیم و به‌ صورت مرحله‌ ای به تمام زیرمجموعه‌ ها یا بالادستان برسیم.

ساختار کلی CTE بازگشتی:

WITH RecursiveCTE AS (
    SELECT ... -- Anchor Member
    UNION ALL
    SELECT ... FROM RecursiveCTE ... --Recursive 
)
SELECT * FROM RecursiveCTE;

یک مثال پر استفاده از سلسله‌ مراتب کارمندان:

WITH EmpHierarchy AS (
    SELECT Id, Name, ManagerId, 0 AS Level
    FROM Employees
    WHERE ManagerId IS NULL
    
    UNION ALL
    
    SELECT e.Id, e.Name, e.ManagerId, Level + 1
    FROM Employees e
    JOIN EmpHierarchy h ON e.ManagerId = h.Id
)
SELECT *
FROM EmpHierarchy;

این کوئری به‌ روشنی سلسله‌ مراتب سازمان را تا هر لایه‌ ای نمایش می‌دهد. معماران و طراحان پایگاه داده، برنامه نویس ها و همه عزیزان باید توجه داشته باشند که UNION ALL اجباری هست و اگر نوشته نشه، SQLSERVER خطا میگیره. بحث فنی اش رو که چرا این اتفاق رخ میده رو در ویدئو های سری SQLSERVER در یوتیوب قرار خواهم داد.

HierarchyID چیست و چرا مفید است؟

SQL Server برای ذخیره و مدیریت ساختار های درختی، نوع داده‌ اختصاصی‌ ای به نام HierarchyID ارائه کرده است؛ نوعی داده که مسیر هر نود را در قالبی شبیه مسیر پوشه‌ ها نگه‌ داری می‌کند.

به عنوان مثال:

• / ریشه

• /1/ اولین فرزند

• /1/2/ فرزند دوم از فرزند اول

این ساختار باعث می‌شود عملیات‌ هایی مثل پیدا کردن فرزندان، والدها، سطح نود و مرتب‌ سازی درختی بسیار ساده و سریع انجام شود.

نمونه ایجاد جدول دسته بندی با HierarchyID:

CREATE TABLE Categories (
    Id INT IDENTITY PRIMARY KEY,
    Node hierarchyid,
    Title NVARCHAR(100)
);

برای درج داده در این جدول هم:

INSERT INTO Categories (Node, Title)
VALUES 
    (hierarchyid::GetRoot(), 'Root Category'),
    (hierarchyid::Parse('/1/'), 'Books'),
    (hierarchyid::Parse('/1/1/'), 'Programming'),
    (hierarchyid::Parse('/1/2/'), 'Science');

یک کوئری برای یافتن همه‌ زیر مجموعه‌ های یک دسته:

SELECT *
FROM Categories
WHERE Node.IsDescendantOf(hierarchyid::Parse('/1/')) = 1;

ترکیب CTE و HierarchyID

در بسیاری از پروژه‌ ها بهتر است ساختار سلسله‌ مراتبی با HierarchyID ذخیره شود، اما برای گزارش‌ گیری و تحلیل از CTE (به‌خصوص CTE بازگشتی) استفاده کنیم.

مثلاً:

WITH CatCTE AS (
    SELECT 
        Id,
        Title,
        Node.ToString() AS Path,
        Node.GetLevel() AS Level
    FROM Categories
)
SELECT *
FROM CatCTE
ORDER BY Path;

دوستان خواهش میکنم توجه کنید؛ این روش نقطه‌ تلاقی خوش‌ ساختی ذخیره‌ سازی و قدرت تحلیل است. انسان از عظمت فنی این ساختار لذت میبرد.

جمع‌ بندی

CTE یکی از ابزارهای قدرتمند SQL Server برای بهبود خوانایی، سادگی و مدیریت کوئری‌ های پیچیده است.
در کنار آن، HierarchyID راهکاری مناسب برای ذخیره‌ سازی ساختار های درختی ارائه می‌دهد.
هنگامی که این دو قابلیت در کنار یکدیگر استفاده شوند، بسیاری از سناریوهای عملی (از مدیریت دسته‌بندی محصولات تا نمایش سلسله‌مراتب سازمانی) به شکل بسیار تمیز تر و کارآمدتری قابل پیاده‌سازی خواهند بود.