ا CTE در SQL Server چیست؟ - CTE in SQL Server

یک مجموعه نتایج با نام موقت را مشخص می کند که به عنوان "عبارت جدول مشترک" (CTE - Common Table Expression) شناخته می شود. این از یک کوئری ساده مشتق شده و در محدوده اجرای یک استیتمنت (statement) ِ SELECT، INSERT، UPDATE، DELETE یا MERGE تعریف شده است. این قسمت همچنین می تواند در دستور CREATE VIEW به عنوان بخشی از دستور SELECT تعریف کننده آن استفاده شود. یک CTE می تواند شامل رفرنس به خودش باشد. این به عنوان یک CTE بازگشتی نامیده می شود.

نوشتن کوئری های طولانی حاوی logic ِ گسترده امری عادی است. می تواند شامل join های زیاد، سطوح مختلف aggregation، و فیلتر کردن در هر تعداد جدول باشد. در برخی موارد، یک مجموعه نتیجه را نمی توان با یک کوئری ساده به تنهایی (SELECT x FROM y WHERE z) به دست آورد و به مجموعه ای از کوئری ها نیاز دارد تا به هم چسبانده شوند. علاوه بر این، برای کوئری های پیچیده تر، بهترین راه برای نوشتن یک کوئری، اغلب تقسیم آن به مراحل منطقی و ساختن آن قطعه به قطعه است.

به طور سنتی،sub-query ها روشی برای جداسازی logic در یک SQL statement است که می‌توان آن را دوباره به کوئری اصلی که می‌خواهید به صورت تدریجی ایجاد کنید، اضافه کرد. این می تواند موثر باشد، اما خواندن و درک آن برای دیگران نیز دشوار است.

ا CTE یک رویکرد (approach) منطقی و خوانا برای نوشتن کوئری ارائه می دهد که می تواند کوئری های پیچیده را به یک سری مراحل منطقی تقسیم کند، به بهبود خوانایی کوئری ها کمک کند و به مجموعه نتایج پیچیده تری دست یابد.

کوئری های زیر از پایگاه داده AdventureWorks مایکروسافت، علاوه بر SQL Server Management Studio و Microsoft SQL Server Developer Edition استفاده می کنند.

آخرین نسخه این نرم افزار را می توانید به صورت رایگان از لینک های زیر دانلود کنید:

SQL Server Developer Edition

SQL Server Management Studio

AdventureWorks DW Database

تعریف CTE

طبق تعریف خالص، یک CTE یک «مجموعه نتایج نام‌گذاری موقت»-temporary named result set- است. در عمل، یک CTE مجموعه ای از نتایج است که برای دامنه اجرای یک دستور SELECT، INSERT، UPDATE، DELETE یا MERGE در حافظه باقی می ماند.

بیایید این را تجزیه کنیم و نگاهی به syntax بیاندازیم تا درک بهتری از معنای این و چرایی مفید بودن آن داشته باشیم. سینتکس اصلی یک CTE به شرح زیر است:

WITH <common_table_expression> ([column names])
AS
(
   <cte_query_definition>
)
<operation>

عبارت WITH به SQL Server می گوید که ما در شرف تعریف (declare کردن) یک CTE هستیم، و <common_table_expression> مشخص می کنه چگونه مجموعه نتایج را برای رفرنس دادن های بعدا نامگذاری می کنیم. از این رو، یک "مجموعه نتایج نامگذاری شده".

[ا column names] جایی است که می‌توانید نام مستعار ستون‌هایی را که از CTE بیرون می‌آیند، نامگذاری کنید. این یک بخش اختیاری از syntax است، و من اغلب فقط ستونی را که در قسمت <cte_query_definition> از سینتکس است نامگذاری می‌کنم.

ا <cte_query_definition> همیشه یک عبارت SELECT است. اینجاست که ما مجموعه نتایج (result set) خود را تعریف می کنیم. شما می توانید این را به عنوان یک جدول موقت در نظر بگیرید که می تواند در یک عبارت FROM یا JOIN مانند هر جدول معمولی دیگری ارجاع داده شود. با این حال، تفاوت های کلیدی بین CTE و جدول موقت وجود دارد که بعدا توضیح داده خواهد شد. همچنین می توانید ستون های خود را در این بخش alias کنید تا بعداً به آنها رفرنس داده شود.

ا <operation> جایی است که ما actual execution خود را انجام می دهیم و به CTE ارجاع می دهیم. همانطور که قبلا ذکر شد، این می تواند SELECT، INSERT، UPDATE، DELETE یا MERGE T-SQL باشد. در زیر به چند نمونه از این موارد خواهیم پرداخت.

بیایید نگاهی به یک مثال CTE برای هدایت این خانه بیندازیم:

WITH Simple_CTE
AS (
   SELECT dd.CalendarYear
      ,fs.OrderDateKey
      ,fs.ProductKey
      ,fs.OrderQuantity * fs.UnitPrice AS TotalSale
      ,dc.FirstName
      ,dc.LastName
   FROM [dbo].[FactInternetSales] fs
   INNER JOIN [dbo].[DimCustomer] dc ON dc.CustomerKey = fs.CustomerKey
   INNER JOIN [dbo].[DimDate] dd ON dd.DateKey = fs.OrderDateKey
   )
SELECT *
FROM Simple_CTE

در مثال بالا، ما یک CTE ایجاد کرده‌ایم که حاوی چند join ، لیست ستون‌های انتخاب شده خاص، و یک ستون مشتق شده «TotalSale» است. عملیات ما در این مثال فقط یک دستور SELECT دیگر است، فقط برای اینکه نشان دهیم چگونه می توان از CTE به ساده ترین شکل استفاده کرد.

ا CTE با SELECT

بیایید syntax را کمی جلوتر ببریم. اغلب، ممکن است متوجه شوید که نیاز به انجام یک aggregation چند لایه دارید (multi-tiered aggregation). یعنی aggregation ِ یک aggregation. ا CTE ها می توانند راهی عالی برای نوشتن این نوع کوئری به روشی خوانا باشند.

WITH Sum_OrderQuantity_CTE
AS (
SELECT ProductKey
,EnglishMonthName
,SUM(OrderQuantity) AS TotalOrdersByMonth
FROM [dbo].[FactInternetSales] fs
INNER JOIN [dbo].[DimDate] dd ON dd.DateKey = fs.OrderDateKey
GROUP BY ProductKey, EnglishMonthName

)
SELECT ProductKey, AVG(TotalOrdersByMonth) AS 'Average Total Orders By Month'
FROM Sum_OrderQuantity_CTE
GROUP BY ProductKey
ORDER BY ProductKey

در این کد SQL، ما مجموع OrderQuantity را بر اساس محصول در ماه می گیریم (product per month) تا ببینیم چه مقدار از هر کالا در ماه فروخته شده است. سپس، ما این مجموع را به طور میانگین محاسبه می کنیم تا ببینیم برای هر محصول، میانگین ماهانه مقدار فروخته شده چقدر است.

اCTE با INSERT

برای ساختن مثال قبلی، به جای select داده‌های انبوه، می‌توانیم یک عبارت INSERT را با subquery

جفت کنیم تا داده‌ها را در جدول جدید وارد کنیم. ابتدا این جدول را بسازید تا جدولی برای insert بهش داشته باشیم:

CREATE TABLE [dbo].[TestTable](
   [CalendarYear] [smallint] NOT NULL,
   [OrderDateKey] [int] NOT NULL,
   [ProductKey] [int] NOT NULL,
   [TotalSale] [money] NULL,
   [FirstName] [nvarchar](50) NULL,
   [LastName] [nvarchar](50) NULL
)

سپس، در مثال زیر می‌توانیم از یک CTE برای insert در این جدول استفاده کنیم.

WITH Simple_CTE
AS (
   SELECT dd.CalendarYear
      ,fs.OrderDateKey
      ,fs.ProductKey
      ,fs.OrderQuantity * fs.UnitPrice AS TotalSale
      ,dc.FirstName
      ,dc.LastName
   FROM [dbo].[FactInternetSales] fs
   INNER JOIN [dbo].[DimCustomer] dc ON dc.CustomerKey = fs.CustomerKey
   INNER JOIN [dbo].[DimDate] dd ON dd.DateKey = fs.OrderDateKey
   )
 
INSERT INTO dbo.TestTable
SELECT * FROM Simple_CTE

نکته: ما این را به‌عنوان یک SELECT * برای سادگی نوشته‌ایم، اما اغلب باید نام هر ستون را صریحاً نامگذاری کنید (در صورت تغییر schema در آینده).

اCTE با update

مشابه یک CTE با DELETE، می‌توانیم یک جدول را با استفاده از روش مشابه به‌روزرسانی کنیم. ابتدا بیایید یک جدول back up ایجاد کنیم تا جدول واقعی را به روز نکنیم:

SELECT * 
INTO dbo.DimCustomerBakUpdate 
FROM DimCustomer

در مرحله بعد، می توانیم این جدول را update کنیم. در مثال زیر، تمام رکوردهایی را که AddressLine2، NULL است، همانطور که در عبارت WHERE مشخص شده است، update می‌کنیم تا به جای آن یک string خالی باشد.

WITH Update_CTE
AS (
   SELECT *
   FROM dbo.DimCustomerBakUpdate
   WHERE AddressLine2 IS NULL
   )
UPDATE Update_CTE
SET AddressLine2 = ''

می‌توانید نتیجه را ببینید - ما یک مجموعه نتیجه از همه رکوردها ایجاد کردیم که در آن AddressLine2 ، null است، و سپس یک به‌روزرسانی را برای مجموعه نتایج خاص اعمال کردیم. باز هم، این یک مثال ساده است که به منظور نشان دادن قابلیت‌های CTE است.

ا CTE با DELETE

علاوه بر select، insert و update داده ها، می توانید از CTE برای حذف داده ها (delete) از جدول استفاده کنید. DELETE ها مستلزم این است که شما فقط از یک جدول در CTE خود استفاده کرده باشید، بنابراین نمی توانید از هیچ join ای استفاده کنید، در غیر این صورت با خطا مواجه خواهید شد.

درست مانند update ، بیایید یک جدول backup دیگر برای حذف رکوردها ایجاد کنیم:

SELECT * 
INTO dbo.DimCustomerBak 
FROM DimCustomer

اکنون می‌توانیم با استفاده از یک CTE از این جدول داده حذف کنیم. در این مثال، ما تمام رکوردهایی که AddressLine2، برابر با null است را حذف می کنیم.

WITH MissingAddress_CTE
AS (
   SELECT *
   FROM dbo.DimCustomerBak
   WHERE AddressLine2 IS NULL
   )
DELETE
FROM MissingAddress_CTE

وقتی دوباره از جدول select می کنیم، می بینید که دیگر هیچ رکوردی با NULL در ستون AddressLine2 وجود ندارد.

چندین CTE در یک کوئری

در نهایت، شما محدود به نوشتن یک CTE در یک کوئری نیستید. می توانید هر تعداد که می خواهید بنویسید و با کاما از هم جدا کنید:

WITH Sales_Cust_Join_CTE
AS (
   SELECT fs.OrderDateKey
      ,fs.ProductKey
      ,fs.OrderQuantity * fs.UnitPrice AS TotalSale
      ,dc.FirstName
      ,dc.LastName
   FROM [dbo].[FactInternetSales] fs
   INNER JOIN [dbo].[DimCustomer] dc ON dc.CustomerKey = fs.CustomerKey
   )
   ,Date_CTE
AS (
   SELECT DateKey
      ,CalendarYear
   FROM [dbo].[DimDate]
   )
SELECT CalendarYear
   ,ProductKey
   ,SUM(TotalSale) AS TotalSales
FROM Sales_Cust_Join_CTE
INNER JOIN Date_CTE ON Date_CTE.DateKey = Sales_Cust_Join_CTE.OrderDateKey
GROUP BY CalendarYear
   ,ProductKey
ORDER BY CalendarYear ASC
   ,TotalSales DESC

در این مثال، ما دو CTE با تعدادی join، logic و انتخاب تنها چند ستون خاص ایجاد می کنیم. در عملیات نهایی خود، CTE ها را به یکدیگر join می کنیم و یک aggregate را تشکیل می دهیم تا کل فروش هر محصول را بر اساس سال تقویمی نشان دهیم.

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

چه زمانی باید از CTE استفاده کرد و چه زمانی نباید استفاده کرد

اگرچه CTE ها روشی عالی برای نوشتن کدهای تمیز تر هستند، اما نباید در هر سناریو از آنها استفاده کرد.مثلا، یک محدودیت عمده این است که یک CTE به scope ِ یک single execution (محدوده یک اجرا) محدود می شود. معنی آن در عمل این است که اگر نیاز به انجام بیش از یک عملیات با این مجموعه نتایج دارید، باید چندین بار CTE را declare کنید. این کارایی ندارد زیرا ما چندین بار کار CTE را انجام می دهیم.

در این سناریو احتمالاً بهتر است از یک temp table استفاده کنید