فانکشن‌ها (UDF) در SQL Server توابعی هستند که پارامتر می‌گیرند و یک مقدار (یا یک جدول) برمی‌گردانند. انواع اصلی: Scalar UDF (یک مقدار)، Inline Table-Valued Function (ITVF) و Multi-Statement Table-Valued Function (MSTVF). فانکشن‌ها را می‌توان داخل SELECT, WHERE, JOIN, VIEW و جاهای دیگر استفاده کرد، اما محدودیت‌هایی هم دارند (مثل ناتوانی در تغییر حالت دیتابیس، استفاده از temp table یا فراخوانی stored procedure). Microsoft Learn+1

---

۱) انواع فانکشن‌ها — توضیح کوتاه

• Scalar UDF: یک مقدار واحد (مثلاً INT, VARCHAR) برمی‌گرداند.
• Inline Table-Valued Function (ITVF): عملاً مانند یک VIEW پارامتری‌شده است — یک تک SELECT را برمی‌گرداند و برای optimizer بهینه‌تر قابل ترکیب است.
• Multi-Statement Table-Valued Function (MSTVF): داخلش می‌توان چندین statement و یک متغیر جدول (@table) داشت، سپس آن جدول را RETURN کرد — اما به‌خاطر استفاده از table variable، مبتنی بر آمار نیست و ممکن است باعث پلن‌های ضعیف شود. Microsoft Learn+1

---

۲) محدودیت‌های مهم فانکشن‌ها (چیزهایی که قبل از نوشتن باید بدونی)

• فانکشن‌ها نمی‌توانند حالت دیتابیس را تغییر دهند (INSERT/UPDATE/DELETE روی جداول دائمی یا DDL داخل تابع مجاز نیست).
• فانکشن‌ها نمی‌توانند stored procedure را فراخوانی کنند.
• استفاده از temp table و dynamic SQL و بعضی از عبارات مثل FOR XML در UDF مجاز نیست. به‌علاوه TRY...CATCH، RAISERROR و @@ERROR در UDF محدود یا پشتیبانی‌نشده‌اند.
• فانکشن‌ها تا ۳۲ سطح تو در تو (nesting) را پشتیبانی می‌کنند. Microsoft Learn+1

---

۳) مثال‌ها + توضیح خط‌به‌خط

مثال 1 — Scalar UDF: محاسبه سن از تاریخ تولد

CREATE OR ALTER FUNCTION dbo.fn_CalcAge(@BirthDate DATE)
RETURNS INT
AS
BEGIN
    -- محاسبه سن بر اساس تولد تا امروز
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE())
           - CASE 
               WHEN (MONTH(@BirthDate) > MONTH(GETDATE()))
                 OR (MONTH(@BirthDate) = MONTH(GETDATE()) AND DAY(@BirthDate) > DAY(GETDATE()))
             THEN 1 ELSE 0 END;
END;
GO

توضیح خط‌به‌خط:

• CREATE OR ALTER FUNCTION dbo.fn_CalcAge(@BirthDate DATE): تعریف یا به‌روزرسانی فانکشنی با یک پارامتر تاریخ.
• RETURNS INT: نوع خروجی (سن به صورت عدد صحیح).
• داخل BEGIN ... END ما RETURN یک عبارت می‌دهیم: DATEDIFF(YEAR, ...) تقریب اول را می‌دهد؛ سپس با CASE بررسی می‌کنیم که آیا هنوز به تاریخ تولد سال جاری نرسیده‌ایم یا نه (در این صورت یک سال کم می‌کنیم).
• فراخوانی: SELECT dbo.fn_CalcAge(BirthDate) AS Age FROM dbo.Person;
  نکتهٔ عملکردی: اگر این فانکشن را روی میلیون‌ها ردیف در SELECT فراخوانی کنی، قبل از SQL Server 2019 ممکن است عملکرد ضعیفی ببینی (scalar UDFها می‌توانند باعث اجرای row-by-row شوند). در SQL Server 2019+ ویژگی scalar UDF inlining تا حدی این مشکل را کاهش داده است (در شرایطی انجام می‌شود). Microsoft Learn

---

مثال 2 — Inline Table-Valued Function (ITVF): سفارشات اخیر مشتری

CREATE OR ALTER FUNCTION dbo.fn_RecentOrders(@CustomerID INT, @Days INT)
RETURNS TABLE
AS
RETURN
(
    SELECT o.OrderID, o.OrderDate, o.TotalAmount
    FROM dbo.Orders o
    WHERE o.CustomerID = @CustomerID
      AND o.OrderDate >= DATEADD(DAY, -@Days, CAST(GETDATE() AS DATE))
);
GO

خط‌به‌خط:

• RETURNS TABLE AS RETURN ( SELECT ... ) => این یک inline TVF است؛ بدین معنا که بدنهٔ آن یک SELECT واحد است و SQL Server می‌تواند آن را مانند view پارامتری شده، در پلن کلی ادغام کند.
• مزیت عملی: می‌توانی از آن در CROSS APPLY یا JOIN استفاده کنی و optimizer معمولاً پلن مناسبی می‌سازد.

مثال فراخوانی:

SELECT c.CustomerName, r.OrderID, r.OrderDate, r.TotalAmount
FROM dbo.Customers c
CROSS APPLY dbo.fn_RecentOrders(c.CustomerID, 30) r;

نکتهٔ عملکردی: ITVFها معمولاً بسیار بهتر از MSTVFها هستند چون به optimizer اجازه می‌دهند تا کارتینالیتی و پلن را براساس جداول واقعی محاسبه کند. TECHCOMMUNITY.MICROSOFT.COM

---

مثال 3 — Multi-Statement TVF (MSTVF): جمع فروش دسته‌ها در بازه زمانی

CREATE OR ALTER FUNCTION dbo.fn_CategorySales(@StartDate DATE, @EndDate DATE)
RETURNS @Sales TABLE
(
    CategoryID INT,
    CategoryName NVARCHAR(200),
    TotalSales DECIMAL(18,2)
)
AS
BEGIN
    INSERT INTO @Sales(CategoryID, CategoryName, TotalSales)
    SELECT p.CategoryID, c.CategoryName, SUM(od.Quantity * od.UnitPrice)
    FROM dbo.OrderDetails od
    JOIN dbo.Products p ON od.ProductID = p.ProductID
    JOIN dbo.Categories c ON p.CategoryID = c.CategoryID
    WHERE od.OrderDate BETWEEN @StartDate AND @EndDate
    GROUP BY p.CategoryID, c.CategoryName;

    RETURN;
END;
GO

توضیح:

• این تابع چند‌مرحله‌ای است: ابتدا یک متغیر جدول @Sales تعریف می‌شود (ساختار آن در RETURNS @Sales TABLE (...) مشخص است). سپس INSERT INTO @Sales انجام می‌شود و در نهایت RETURN می‌شود.
• هشدار عملکردی: هنگامی که از متغیر جدول استفاده می‌شود، آمار جدول (statistics) روی آن موجود نیست؛ بنابراین optimizer ممکن است تخمین‌های اشتباهی بزند و پلن نامناسب تولید کند. وقتی پیچیدگی زیاد است MSTVF ساده‌ترین راه است ولی باید مراقب هزینه باشی. TECHCOMMUNITY.MICROSOFT.COM

---

۴) تفاوت‌ها و مقایسهٔ فانکشن با Stored Procedure (به‌صورت مستقیم و کاربردی)

• قابلیت استفاده در SELECT / JOIN / WHERE: فانکشن‌ها (به‌ویژه scalar و TVF) را می‌توان داخل SELECT و JOIN استفاده کرد؛ stored procedure را نمی‌توان داخل SELECT استفاده کرد. (پس اگر خروجی باید بخشی از یک کوئری باشد، از فانکشن یا TVF استفاده کن). Microsoft Learn
• تغییر حالت دیتابیس و DML/DDL: stored procedure می‌تواند INSERT/UPDATE/DELETE و DDL انجام دهد؛ فانکشن‌ها به‌طور معمول نمی‌توانند حالت دیتابیس را تغییر دهند. اگر نیاز به عملیات نوشتن/تغییر داری، باید از stored procedure استفاده کنی. Microsoft Learn+1
• خروجی و پارامترها: stored procedure می‌تواند چند result set و پارامترهای OUTPUT داشته باشد؛ فانکشن فقط یک مقدار یا یک جدول برمی‌گرداند (و پارامترهای خروجی کلاسیک ندارد). Microsoft Learn
• خطاها و تراکنش: stored procedure قابلیت TRY...CATCH و مدیریت تراکنش را دارد؛ فانکشن محدودیت‌هایی در مدیریت خطا و تراکنش دارد. Microsoft Learn+1

---

۵) مزایا (چرا فانکشن بنویسیم) و معایب (چه موقع ننویسیم)

مزایا

• کپسوله‌سازی منطق — خوانایی و بازاستفادهٔ کد.
• قابل استفاده در computed column و CHECK constraint یا view (در صورت رعایت شرایط).

معایب / ریسک‌ها

• محدودیت در تغییر داده (نمی‌توانند دِی‌ام‌ال دائمی اجرا کنند). Microsoft Learn
• مشکلات عملکردی بالقوه:
  • Scalar UDFها در نسخه‌های قدیمی‌تر می‌توانند باعث اجرای row-by-row و SPOOL شوند و عملکرد را بشدت پایین بیاورند. از SQL Server 2019 به بعد scalar UDF inlining این مشکل را تا حدی رفع می‌کند اما شرایطی برای inlining وجود دارد. Microsoft Learn
  • MSTVFها آمار ندارند و ممکن است پلن‌های بد تولید شود؛ در بسیاری موارد ITVF راه‌حل بهتر و سریع‌تری است. TECHCOMMUNITY.MICROSOFT.COM

---

۶) نکات عملی و بهترین روش‌ها (practical tips)

1. اگر ممکن است از Inline TVF استفاده کن — وقتی منطق قابل بیان در یک SELECT است از ITVF استفاده کن (بهینه‌تر و قابل ترکیب با پلن). TECHCOMMUNITY.MICROSOFT.COM
2. از scalar UDF در ستون‌های بزرگی که روی میلیون‌ها ردیف اجرا می‌شوند اجتناب کن مگر اینکه در SQL Server 2019+ و شرایط inlining برقرار باشد. Microsoft Learn
3. برای محاسبات پیچیده ولی set-based، سعی کن آن‌ها را به یک query یا ITVF تبدیل کنی تا optimizer بتواند از آمار استفاده کند.
4. از SCHEMABINDING استفاده کن وقتی می‌خواهی تضمین کنی که اشیاء مرجع تغییر نکنند (و برای بعضی کاربردها ضرورت دارد). اما توجه کن که تغییر ساختار جداول وابسته سخت‌تر می‌شود. Microsoft Learn
5. اجازهٔ فراخوانی stored procedure از داخل فانکشن وجود ندارد — طراحی‌ات را بر اساس این محدودیت انجام بده. Microsoft Learn
6. در صورت نیاز به عملیات DML یا چندین result-set از stored procedure استفاده کن. Microsoft Learn

---

۷) جمع‌بندی و پیشنهاد برای قدم بعدی

• اگر منطق قابل بیان به‌صورت set-based است: اولویت با ITVF.
• اگر منطق نیاز به متغیر جدول و چند مرحله دارد و تعداد ردیف کم است: MSTVF قابل قبول است اما باید تست عملکرد بکنی.
• اگر منطق باید دیتابیس را تغییر دهد یا نیاز به تراکنش/TRY…CATCH/OUTPUT multiple resultsets دارد: از Stored Procedure استفاده کن.
• اگر با scalar UDF مشکل عملکرد دیدی و SQL Server نسخه‌ت 2019+ است، بررسی کن که آیا فانکشن قابل inlining است یا نه (یا فانکشن را بازنویسی کن به شکل set-based).