تابع IIF در SQL Server راهنمای جامع کاربرد، عملکرد و بهینه‌سازی

IIF SQL Server, IIF در SQL Server, تابع IIF در SQL Server, SQL Server IIF function, IIF vs CASE WHEN, تفاوت IIF و CASE WHEN, CASE WHEN در SQL Server, شرط در SQL Server, Conditional Logic SQL Server, T SQL IIF, IIF SQL Server 2012, آموزش IIF, مثال IIF در SQL Server, توابع منطقی SQL Server, SQL Server Performance, SARGability, بهینه سازی Query در SQL Server, تفاوت IIF و IF ELSE

نوشتن منطق شرطی در دستورات SQL یکی از نیازهای رایج توسعه‌دهندگان و متخصصان پایگاه‌داده است. پیش از SQL Server 2012، برای پیاده‌سازی چنین منطق‌هایی در سطح Expression معمولاً از عبارت CASE WHEN استفاده می‌شد. این عبارت هرچند قدرتمند است، اما برای شرط‌های ساده دو حالته، نگارش نسبتاً طولانی دارد.

تابع IIF با هدف ساده‌سازی نگارش شرط‌های دوحالته معرفی شد. این تابع به‌عنوان یک میان‌بر نحوی برای CASE WHEN عمل می‌کند و در بسیاری از سناریوهای گزارش‌گیری، محاسبات فیلدهای مشتق‌شده و منطق‌های ساده کاربرد دارد.

تعریف تابع IIF

تابع IIF یک تابع منطقی در T-SQL است که یک عبارت شرطی را ارزیابی می‌کند و بر اساس نتیجه آن، یکی از دو مقدار مشخص‌شده را بازمی‌گرداند.

این تابع از نسخه SQL Server 2012 به بعد در دسترس است و همچنین در Azure SQL Database و Azure SQL Managed Instance پشتیبانی می‌شود. در نسخه‌های قدیمی‌تر مانند SQL Server 2008 یا SQL Server 2008 R2، این تابع وجود ندارد و باید از CASE WHEN استفاده شود.

نحوه عملکرد IIF

Syntax پایه

ساختار کلی تابع IIF به شکل زیر است.

IIF ( boolean_expression, true_value, false_value )

پارامترهای این تابع عبارت‌اند از:

boolean_expression: یک عبارت شرطی یا Predicate که نتیجه آن TRUE، FALSE یا UNKNOWN مشخص می‌کند.
true_value: مقداری که در صورت TRUE بودن شرط بازگردانده می‌شود.
false_value: مقداری که در صورت FALSE یا UNKNOWN بودن شرط بازگردانده می‌شود.

مثال زیر یک استفاده ساده از این تابع را نشان می‌دهد.

SELECT
    ProductName,
    UnitsInStock,
    IIF(UnitsInStock > 0, N'موجود', N'ناموجود') AS StockStatus
FROM Products;

در این کوئری، برای هر رکورد محصول، مقدار ستون UnitsInStock بررسی می‌شود. اگر بزرگ‌تر از صفر باشد، مقدار «موجود» و در غیر این صورت مقدار «ناموجود» در ستون خروجی StockStatus نمایش داده می‌شود.

رفتار IIF با مقادیر NULL و منطق سه‌ارزشی

یکی از نکات مهمی که در استفاده از IIF باید به آن توجه شود، این است که SQL Server از منطق سه‌ارزشی پیروی می‌کند. به عبارت دیگر، یک عبارت شرطی می‌تواند سه نتیجه داشته باشد: TRUE، FALSE یا UNKNOWN. تابع IIF در حالت TRUE مقدار true_value و در هر دو حالت FALSE و UNKNOWN مقدار false_value را بازمی‌گرداند.

مثال زیر این رفتار را نشان می‌دهد.

SELECT IIF(NULL = NULL, N'برابر', N'برابر نیست یا نامشخص') AS Result;

نتیجه این کوئری همیشه «برابر نیست یا نامشخص» است، زیرا در SQL Server عبارت NULL = NULL مقدار TRUE تولید نمی‌کند، بلکه مقدار UNKNOWN تولید می‌کند. این رفتار نشان می‌دهد که IIF یک ساختار if/else ساده مانند زبان‌های برنامه‌نویسی معمولی نیست، بلکه از قوانین منطق سه‌ارزشی SQL پیروی می‌کند.

به همین دلیل، در مثال زیر:

SELECT
    ProductName,
    IIF(UnitsInStock > 0, N'موجود', N'ناموجود') AS StockStatus
FROM Products;

اگر مقدار UnitsInStock برابر NULL باشد، عبارت UnitsInStock > 0 به UNKNOWN ارزیابی می‌شود و IIF به‌طور طبیعی مقدار «ناموجود» را بازمی‌گرداند، بدون نیاز به بررسی جداگانه NULL.

با این حال، در سناریوهایی که منطق کسب‌وکار ایجاب می‌کند مقدار NULL به‌صورت صریح معادل یک مقدار پیش‌فرض در نظر گرفته شود، استفاده از ISNULL می‌تواند مفید باشد.

SELECT
    ProductName,
    IIF(
        ISNULL(UnitsInStock, 0) > 0,
        N'موجود',
        N'ناموجود'
    ) AS StockStatus
FROM Products;

در این مثال ISNULL از نظر نتیجه ضروری نیست، زیرا IIF در صورت UNKNOWN نیز false_value را برمی‌گرداند. استفاده از آن صرفاً برای بیان صریح منطق جایگزینی NULL انجام شده است تا خواننده کد بدون نیاز به دانستن جزئیات منطق سه‌ارزشی، رفتار مربوط به NULL را متوجه شود.

رابطه IIF با CASE WHEN

IIF در SQL Server از نظر معنایی معادل یک عبارت CASE است و در مستندات رسمی مایکروسافت نیز به‌عنوان یک میان‌بر برای CASE معرفی می‌شود. برای منطق یکسان، IIF مزیت Performance ذاتی نسبت به CASE WHEN ندارد، زیرا از نظر معنایی به CASE ترجمه می‌شود. تفاوت اصلی این دو در شیوه نگارش و قابلیت بیان منطق شرطی است.

عبارت زیر:

IIF(UnitsInStock > 0, N'موجود', N'ناموجود')

از نظر رفتاری معادل عبارت زیر عمل می‌کند.

CASE WHEN UnitsInStock > 0 THEN N'موجود' ELSE N'ناموجود' END

تعیین نوع داده خروجی

نوع داده نهایی خروجی IIF بر اساس قوانین Data Type Precedence در SQL Server تعیین می‌شود. اگر true_value و false_value از نوع‌های داده متفاوتی باشند، SQL Server نوع داده با اولویت بالاتر را برای خروجی انتخاب کرده و تلاش می‌کند مقدار دیگر را به آن نوع تبدیل کند.

SELECT IIF(1 = 1, 1

فهرست مطالب
Toggle
تعریف تابع IIF
نحوه عملکرد IIF
رفتار IIF با مقادیر NULL و منطق سه‌ارزشی
رابطه IIF با CASE WHEN
مقایسه IIF با CASE WHEN
IIF در برابر IF…ELSE
سناریوهای واقعی استفاده
مثال عملی
مزایای استفاده از IIF
محدودیت‌های IIF
Best Practiceها
اشتباهات رایج
00, N'صد') AS Result;

در این مثال، نوع int نسبت به نوع رشته‌ای اولویت بالاتری دارد. بنابراین SQL Server تلاش می‌کند مقدار N'صد' را به int تبدیل کند. از آنجا که این تبدیل امکان‌پذیر نیست، Query با خطای Conversion مواجه می‌شود.

نکته دیگری که در این زمینه اهمیت دارد، حالتی است که هر دو مقدار true_value و false_value به‌صورت NULL ثابت وارد شوند.

SELECT IIF(1 = 1, NULL, NULL);

این کوئری نیز با خطا مواجه می‌شود، زیرا SQL Server نمی‌تواند نوع داده خروجی را صرفاً از روی دو مقدار NULL تعیین کند. برای رفع این مشکل باید حداقل یکی از دو مقدار را با CAST یا CONVERT به نوع داده مشخصی تبدیل کرد.

SELECT IIF(1 = 1, CAST(NULL AS INT), NULL);

مقایسه IIF با CASE WHEN

ویژگی	IIF	CASE WHEN
تعداد شرط قابل بررسی	فقط یک شرط (دو حالته)	چند شرط (چند حالته)
خوانایی در شرط‌های ساده	بالاتر	متوسط
خوانایی در شرط‌های تودرتو	پایین‌تر	بالاتر
نسخه پشتیبانی	SQL Server 2012 به بعد	نسخه‌های قدیمی‌تر SQL Server نیز پشتیبانی می‌کنند
نحوه پیاده‌سازی	به CASE ترجمه می‌شود	مستقیماً CASE است
حداکثر عمق تودرتویی	10 سطح	10 سطح

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

IIF در برابر IF…ELSE

یکی از اشتباهات رایج، به‌ویژه در میان توسعه‌دهندگانی که تازه با T-SQL کار می‌کنند، خلط مفهومی میان IIF و IF...ELSE است. این دو ساختار از نظر ماهیت با یکدیگر کاملاً متفاوت‌اند.

IIF یک Expression است و همیشه یک مقدار را در سطح یک عبارت SQL بازمی‌گرداند. این تابع در داخل SELECT، WHERE، ORDER BY یا هر جایی که یک Expression مجاز است قابل استفاده است.

IF...ELSE در مقابل، یک Control-of-Flow Construct است و برای کنترل مسیر اجرای دستورات T-SQL در سطح Batch یا Stored Procedure به کار می‌رود. این ساختار مقداری بازنمی‌گرداند، بلکه تعیین می‌کند کدام بلوک از دستورات اجرا شود.

مثال زیر استفاده از IIF را نشان می‌دهد.

SELECT
    ProductName,
    IIF(UnitsInStock > 0, N'موجود', N'ناموجود') AS StockStatus
FROM Products;

در مقابل، مثال زیر همان منطق را با IF…ELSE در قالب Control-of-Flow پیاده‌سازی می‌کند.

IF EXISTS
(
    SELECT 1
    FROM Products
    WHERE UnitsInStock > 0
)
BEGIN
    PRINT N'محصول موجود است';
END
ELSE
BEGIN
    PRINT N'محصول موجود نیست';
END;

انتخاب میان این دو ساختار به نیاز واقعی بستگی دارد. اگر هدف تولید یک مقدار در نتیجه یک Query باشد، IIF یا CASE WHEN گزینه صحیح است. اگر هدف کنترل مسیر اجرای منطق برنامه، مانند اجرای یک Stored Procedure متفاوت بر اساس یک شرط باشد، IF…ELSE انتخاب درست است.

سناریوهای واقعی استفاده

در پروژه‌های واقعی، IIF معمولاً در موارد زیر کاربرد دارد.

تولید ستون‌های وضعیت در گزارش‌های مدیریتی
محاسبه فیلدهای مشتق‌شده در View ها و Stored Procedure ها
استفاده در Queryهای T-SQL که به‌عنوان منبع داده در ابزارهای گزارش‌گیری مانند SSRS و Power BI مورد استفاده قرار می‌گیرند
استفاده در منطق‌های شرطی که نیاز به مدیریت صریح مقادیر NULL دارند

مثال عملی

مثال ساده با شرط عددی

SELECT
    OrderID,
    TotalAmount,
    IIF(TotalAmount >= 1000000, N'سفارش عمده', N'سفارش عادی') AS OrderType
FROM Orders;

خروجی این کوئری ستونی به نام OrderType تولید می‌کند که بر اساس مبلغ کل سفارش، آن را به دو دسته سفارش عمده و سفارش عادی تقسیم می‌کند.

مثال تودرتو برای چند شرط

SELECT
    StudentID,
    Score,
    IIF(Score >= 18, N'عالی',
        IIF(Score >= 14, N'خوب', N'نیاز به تلاش بیشتر')) AS Grade
FROM StudentScores;

در این مثال، از تودرتو کردن دو تابع IIF برای پیاده‌سازی سه سطح ارزیابی استفاده شده است. با وجود صحت عملکرد، این نوع نگارش برای سطوح بیشتر از دو یا سه شرط توصیه نمی‌شود. علاوه بر کاهش خوانایی، از آنجا که IIF به CASE ترجمه می‌شود، حداکثر تا 10 سطح تودرتویی را پشتیبانی می‌کند و تجاوز از این محدودیت منجر به خطا خواهد شد. بنابراین برای منطق‌های شرطی گسترده، استفاده از CASE WHEN انتخاب مناسب‌تری است.

مثال استفاده در WHERE و ملاحظات SARGability

SELECT
    ProductName,
    UnitsInStock
FROM Products
WHERE IIF(UnitsInStock > 0, 1, 0) = 1;

این کوئری از نظر عملکردی صحیح است، اما از نظر بهینه‌سازی الگوی مناسبی نیست. قرار دادن ستون درون یک Expression مانند IIF در Predicate می‌تواند مانع استفاده بهینه از Index برای جست‌وجوی SARGable شود. نگارش صحیح‌تر برای همین منطق به شکل زیر است.

SELECT
    ProductName,
    UnitsInStock
FROM Products
WHERE UnitsInStock > 0;

به‌عنوان یک قاعده کلی، در شرط‌های WHERE بهتر است مقایسه مستقیم روی ستون انجام شود و از قرار دادن ستون داخل توابعی مانند IIF یا CASE خودداری شود، مگر در مواردی که منطق شرطی واقعاً پیچیده باشد و امکان بازنویسی ساده وجود نداشته باشد.

مثال ترکیب با JOIN و توابع تجمعی

SELECT
    c.CustomerID,
    c.CustomerName,
    SUM(o.TotalAmount) AS TotalPurchases,
    IIF(
        SUM(o.TotalAmount) > 5000000,
        N'مشتری VIP',
        N'مشتری عادی'
    ) AS CustomerTier
FROM Customers c
INNER JOIN Orders o
    ON c.CustomerID = o.CustomerID
GROUP BY
    c.CustomerID,
    c.CustomerName;

این کوئری مجموع خرید هر مشتری را محاسبه کرده و بر اساس آن، سطح مشتری را مشخص می‌کند. قرار دادن CustomerID در GROUP BY ضروری است، زیرا در صورت یکسان بودن نام دو مشتری مختلف، گروه‌بندی تنها بر اساس نام می‌تواند نتایج نادرستی تولید کند.

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

کاهش حجم کد در شرط‌های ساده دو حالته
افزایش خوانایی کوئری‌های گزارشی
رفتار اجرایی معادل CASE WHEN بدون سربار اضافه برای منطق یکسان
قابلیت استفاده در SELECT، WHERE، HAVING، ORDER BY و Computed Columns

محدودیت‌های IIF

عدم پشتیبانی در نسخه‌های SQL Server قدیمی‌تر از 2012
عدم پشتیبانی در Dedicated SQL pool مربوط به Azure Synapse Analytics، که در آن باید از CASE استفاده کرد
عدم امکان پیاده‌سازی شرط‌های چند حالته بدون تودرتو کردن
محدودیت حداکثر 10 سطح تودرتویی به دلیل ترجمه شدن به CASE
محدودیت در تعیین نوع داده خروجی بر اساس Data Type Precedence، به‌ویژه در صورت استفاده از دو مقدار NULL ثابت
کاهش احتمالی SARGability در صورت استفاده مستقیم روی ستون داخل شرط WHERE

Best Practiceها

برای استفاده صحیح از IIF در پروژه‌های واقعی، رعایت نکات زیر توصیه می‌شود.

استفاده از IIF فقط برای شرط‌های ساده و دو حالته
استفاده از CASE WHEN برای منطق‌های چند شرطی به‌جای تودرتو کردن IIF
بررسی Data Type Precedence در صورت تفاوت نوع داده true_value و false_value و استفاده از CAST یا CONVERT در صورت نیاز
استفاده صریح از ISNULL یا COALESCE در مواردی که رفتار مقادیر NULL باید به‌صورت شفاف در منطق Query بیان شود
پرهیز از استفاده IIF مستقیم روی ستون‌های Index‌دار در شرط WHERE برای حفظ SARGability
بررسی سازگاری نسخه SQL Server مقصد یا نوع سرویس Azure قبل از استفاده از IIF در پروژه‌های Migration

اشتباهات رایج

استفاده بیش از حد از تودرتو کردن IIF که خوانایی کد را از بین می‌برد و می‌تواند به محدودیت 10 سطح تودرتویی برخورد کند
نادیده گرفتن تفاوت نوع داده بین true_value و false_value که منجر به خطای Conversion می‌شود
تصور نادرست از IIF به‌عنوان یک ساختار if/else ساده، بدون توجه به منطق سه‌ارزشی SQL Server در برخورد با NULL
خلط مفهومی میان IIF به‌عنوان Expression و IF…ELSE به‌عنوان Control-of-Flow Construct
استفاده از IIF در پروژه‌هایی که باید با نسخه‌های قدیمی‌تر SQL Server یا Dedicated SQL pool در Azure Synapse سازگار باشند
استفاده از IIF روی ستون‌های فیلترشونده در WHERE بدون توجه به تأثیر آن بر استفاده از Index

جمع‌بندی

تابع IIF در SQL Server ابزاری کارآمد برای ساده‌سازی نگارش شرط‌های دو حالته است که از نسخه 2012 به بعد در دسترس قرار گرفته است. این تابع از نظر معنایی معادل CASE WHEN عمل می‌کند و برای منطق یکسان، مزیت Performance ذاتی نسبت به آن ندارد. توجه به منطق سه‌ارزشی SQL Server در برخورد با NULL، محدودیت 10 سطح تودرتویی، نوع داده خروجی و تأثیر بر SARGability از نکات کلیدی در استفاده صحیح از این تابع محسوب می‌شود. همچنین تمایز میان IIF به‌عنوان یک Expression و IF…ELSE به‌عنوان یک Control-of-Flow Construct، از مفاهیم پایه‌ای است که هر توسعه‌دهنده T-SQL باید به آن مسلط باشد.

پرسش‌های متداول FAQ

آیا IIF در تمام نسخه‌های SQL Server قابل استفاده است؟
خیر. این تابع تنها از SQL Server 2012 به بعد و همچنین در Azure SQL Database و Azure SQL Managed Instance پشتیبانی می‌شود. برای نسخه‌های قدیمی‌تر و همچنین Dedicated SQL pool در Azure Synapse Analytics باید از CASE WHEN استفاده شود.

آیا استفاده از IIF نسبت به CASE WHEN تأثیری بر Performance دارد؟
خیر، برای منطق یکسان هر دو رفتار اجرایی معادلی دارند، زیرا IIF در سطح موتور پایگاه‌داده به CASE ترجمه می‌شود. تفاوت اصلی آن‌ها در خوانایی کد و قابلیت بیان شرط‌های چند حالته است.

رفتار IIF در مواجهه با مقادیر NULL چگونه است؟
IIF از منطق سه‌ارزشی SQL Server پیروی می‌کند. اگر عبارت شرطی به UNKNOWN ارزیابی شود، که معمولاً در مقایسه‌های شامل NULL رخ می‌دهد، IIF مقدار false_value را بازمی‌گرداند، دقیقاً مانند حالتی که شرط FALSE باشد.

تفاوت IIF و IF…ELSE در SQL Server چیست؟
IIF یک Expression است و یک مقدار در سطح Query بازمی‌گرداند. IF…ELSE یک Control-of-Flow Construct است و برای کنترل مسیر اجرای دستورات T-SQL در Batch یا Stored Procedure استفاده می‌شود و خودش مقداری تولید نمی‌کند.

آیا می‌توان چند شرط را با IIF بررسی کرد؟
بله، اما تنها با تودرتو کردن چند تابع IIF و حداکثر تا 10 سطح. برای شرط‌های چند حالته با بیش از دو یا سه سطح، استفاده از CASE WHEN به دلیل خوانایی بهتر توصیه می‌شود.

نیاز به بهینه‌سازی SQL Server دارید؟

استفاده درست از توابعی مانند IIF فقط به کوتاه‌تر شدن کد محدود نمی‌شود. انتخاب Expression مناسب، حفظ SARGability، مدیریت صحیح NULL و درک رفتار Query Optimizer می‌تواند تأثیر مستقیمی بر کیفیت و Performance کوئری‌های SQL Server داشته باشد.

اگر در پروژه خود با کوئری‌های کند، طراحی نامناسب دیتابیس، مشکلات Performance یا نیاز به Migration و بهینه‌سازی SQL Server مواجه هستید، تیم فنی توسعه فناوری اطلاعات لاندا آماده ارائه خدمات مشاوره، طراحی و بهینه‌سازی پایگاه‌داده است.

برای بررسی تخصصی Performance و بهینه‌سازی SQL Server با لاندا در تماس ✆ باشید.

روزبه امیرعصامی

1405/05/20