بهینه‌سازی SQL Server با گزینه‌های SET استاندارد SET ANSI SQL-92

افزایش عملکرد SQL Server: راهنمای جامع گزینه‌های SET ANSI در استاندارد ANSI SQL-92

مدیریت و بهینه‌سازی SQL Server نیازمند درک عمیق از جزئیات عملکرد آن است. یکی از جنبه‌های حیاتی که بر رفتار و عملکرد پایگاه داده تأثیر بسزایی دارد، گزینه‌های SET هستند. این گزینه‌ها، که بسیاری از آن‌ها از استاندارد ANSI SQL-92 نشأت می‌گیرند، چگونگی پردازش دستورات T-SQL را توسط SQL Server تعیین می‌کنند. عدم آشنایی با این تنظیمات می‌تواند منجر به مشکلات پنهان در اعتبار داده‌ها، عملکرد کوئری و حتی رفتار غیرمنتظره برنامه‌ها شود. در این مقاله، به بررسی دقیق مهم‌ترین گزینه‌های SET ANSI SQL-92 می‌پردازیم و پیامدهای آن‌ها را برای توسعه‌دهندگان و مدیران پایگاه داده (DBA) روشن می‌کنیم.

SQL Server با فعال کردن بسیاری از این گزینه‌ها، تطابق بیشتری با استاندارد ANSI SQL-92 پیدا می‌کند. در محیط‌های توسعه و تست، ممکن است این گزینه‌ها به صورت پیش‌فرض فعال نباشند، اما برای اطمینان از صحت عملکرد و جلوگیری از رفتارهای غیرمنتظره، فعال‌سازی آن‌ها در محیط‌های تولید (Production) اکیداً توصیه می‌شود. در ادامه، به بررسی هر یک از این گزینه‌ها و تأثیر آن‌ها بر SQL Server می‌پردازیم.

SET ANSI_NULLS

گزینه SET ANSI_NULLS تعیین می‌کند که مقایسه‌ها با مقادیر NULL چگونه رفتار کنند. هنگامی که ANSI_NULLS روشن باشد (ON)، هرگونه مقایسه با NULL (حتی NULL = NULL) همیشه نتیجه UNKNOWN را برمی‌گرداند. این رفتار با استاندارد ANSI مطابقت دارد. اما هنگامی که خاموش باشد (OFF)، مقایسه NULL = NULL به TRUE ارزیابی می‌شود و NULL = <هر مقدار دیگر> به FALSE ارزیابی می‌شود.

توصیه اکید این است که ANSI_NULLS همیشه روی ON تنظیم شود. این کار به دلیل تأثیر آن بر نماهای ایندکس‌شده (Indexed Views) و ستون‌های محاسباتی پایدار (Persisted Computed Columns) است. اگر ANSI_NULLS خاموش باشد، نمی‌توانید نماهای ایندکس‌شده یا ستون‌های محاسباتی پایدار را ایجاد یا از آن‌ها استفاده کنید.

درک این رفتار برای نوشتن کوئری‌های قابل پیش‌بینی و استاندارد حیاتی است. به عنوان مثال، برای بررسی اینکه یک ستون شامل NULL است یا خیر، همیشه باید از IS NULL یا IS NOT NULL استفاده کنید، نه از عملگرهای مقایسه‌ای مانند = یا !=.

NULL = NULL

هنگامی که SET ANSI_NULLS ON باشد، نتیجه این مقایسه همواره UNKNOWN است. این رفتار منطق “سه-ارزشی” (three-valued) را برای NULL در SQL رعایت می‌کند، جایی که NULL نشان‌دهنده یک مقدار نامعلوم است و نمی‌تواند با هیچ چیز دیگری، حتی با یک NULL دیگر، برابر در نظر گرفته شود.

مثال عملی:

SET ANSI_NULLS ON;
SELECT CASE WHEN NULL = NULL THEN 'True' ELSE 'False' END AS Result_ON;

SET ANSI_NULLS OFF;
SELECT CASE WHEN NULL = NULL THEN 'True' ELSE 'False' END AS Result_OFF;

خروجی Result_ON به دلیل رفتار UNKNOWN، ‘False’ خواهد بود (زیرا UNKNOWN برابر TRUE نیست)، در حالی که Result_OFF مقدار ‘True’ را برمی‌گرداند. این تفاوت در نتایج نشان‌دهنده اهمیت تنظیم صحیح این گزینه است.

SET ANSI_PADDING

گزینه SET ANSI_PADDING نحوه ذخیره‌سازی و مدیریت فضاهای انتهایی (trailing spaces) را برای انواع داده CHAR، VARCHAR، BINARY و VARBINARY تعیین می‌کند.

• هنگامی که ANSI_PADDING روی ON باشد، فضاهای انتهایی برای VARCHAR و VARBINARY که کمتر از طول ستون تعریف شده‌اند، حذف نمی‌شوند. برای CHAR و BINARY، مقادیر همیشه تا طول کامل ستون با فضاهای انتهایی پر می‌شوند. این رفتار برای مطابقت با استاندارد ANSI ترجیح داده می‌شود.
• هنگامی که ANSI_PADDING روی OFF باشد، فضاهای انتهایی برای VARCHAR و VARBINARY قبل از ذخیره‌سازی حذف می‌شوند. این می‌تواند منجر به از دست رفتن داده‌ها شود و با استاندارد ANSI مطابقت ندارد.

مانند ANSI_NULLS، ANSI_PADDING نیز باید همیشه روی ON تنظیم شود تا نماهای ایندکس‌شده و ستون‌های محاسباتی پایدار به درستی کار کنند و از رفتارهای غیرمنتظره در مقایسه رشته‌ها جلوگیری شود. این تنظیم تضمین می‌کند که داده‌ها همانطور که وارد می‌شوند، ذخیره و بازیابی گردند.

مثال برای درک تفاوت‌ها:

CREATE TABLE #TestPadding (
    Col_CHAR CHAR(10),
    Col_VARCHAR VARCHAR(10)
);

SET ANSI_PADDING ON;
INSERT INTO #TestPadding VALUES ('abc   ', 'abc   ');
SELECT
    'ON - CHAR: [' + Col_CHAR + ']',
    'ON - VARCHAR: [' + Col_VARCHAR + ']',
    LEN(Col_CHAR) AS Len_CHAR_ON,
    LEN(Col_VARCHAR) AS Len_VARCHAR_ON
FROM #TestPadding;
TRUNCATE TABLE #TestPadding;

SET ANSI_PADDING OFF;
INSERT INTO #TestPadding VALUES ('abc   ', 'abc   ');
SELECT
    'OFF - CHAR: [' + Col_CHAR + ']',
    'OFF - VARCHAR: [' + Col_VARCHAR + ']',
    LEN(Col_CHAR) AS Len_CHAR_OFF,
    LEN(Col_VARCHAR) AS Len_VARCHAR_OFF
FROM #TestPadding;

DROP TABLE #TestPadding;

در حالت ON، فضاهای انتهایی در VARCHAR حفظ می‌شوند و CHAR نیز تا طول کامل با فضا پر می‌شود. در حالت OFF، فضاهای انتهایی VARCHAR حذف می‌شوند.

SET ANSI_WARNINGS

گزینه SET ANSI_WARNINGS بر رفتار SQL Server در مواجهه با خطاهایی مانند تقسیم بر صفر، داده‌های بیش از حد طول ستون (truncation) و توابع تجمعی (aggregate functions) با مقادیر NULL تأثیر می‌گذارد. هنگامی که ANSI_WARNINGS روی ON تنظیم شده باشد، SQL Server پیام‌های هشدار یا خطا صادر می‌کند و در برخی موارد، اجرای کوئری را متوقف می‌کند. این رفتار به شناسایی و رفع مشکلات احتمالی داده‌ها و محاسبات کمک می‌کند.

• اگر ANSI_WARNINGS ON باشد:
  • در صورت تقسیم بر صفر، یک پیام خطا صادر می‌شود و کوئری متوقف می‌گردد.
  • اگر داده‌ای بیش از طول ستون CHAR، NCHAR، VARCHAR یا NVARCHAR وارد شود، یک هشدار صادر می‌شود.
  • توابع تجمعی مانند SUM، AVG و COUNT مقادیر NULL را نادیده می‌گیرند.
• اگر ANSI_WARNINGS OFF باشد:
  • تقسیم بر صفر نتیجه NULL را برمی‌گرداند و کوئری ادامه می‌یابد (رفتار مشابه SET ARITHABORT OFF).
  • داده‌های بیش از طول ستون بریده می‌شوند و هیچ هشدار یا خطایی صادر نمی‌گردد، که می‌تواند منجر به از دست رفتن داده شود.

برای حفظ یکپارچگی داده‌ها و اطمینان از صحت محاسبات، توصیه می‌شود ANSI_WARNINGS همیشه روی ON تنظیم شود. این گزینه نیز برای نماهای ایندکس‌شده و ستون‌های محاسباتی پایدار ضروری است.

مثال تقسیم بر صفر:

SET ANSI_WARNINGS ON;
SELECT 1/0 AS DivideByZero_ON; -- این دستور خطا می‌دهد.

SET ANSI_WARNINGS OFF;
SELECT 1/0 AS DivideByZero_OFF; -- این دستور NULL برمی‌گرداند.

SET ARITHABORT

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

• اگر ARITHABORT ON باشد: هر خطای سرریز یا تقسیم بر صفر در حین اجرای کوئری، کل کوئری را متوقف می‌کند.
• اگر ARITHABORT OFF باشد: در صورت بروز خطای سرریز، یک هشدار صادر می‌شود اما کوئری ادامه می‌یابد و نتیجه NULL خواهد بود. در مورد تقسیم بر صفر، اگر ANSI_WARNINGS OFF باشد، نتیجه NULL و کوئری ادامه می‌یابد.

مانند گزینه‌های قبلی، ARITHABORT نیز باید همیشه روی ON تنظیم شود تا از صحت داده‌ها و محاسبات اطمینان حاصل شود. این گزینه برای استفاده از نماهای ایندکس‌شده و ستون‌های محاسباتی پایدار ضروری است و همچنین برای بهینه‌سازی طرح‌های اجرای کوئری (Query Execution Plans) اهمیت دارد.

مثال سرریز عددی:

SET ARITHABORT ON;
DECLARE @val INT = 2147483647; -- حداکثر مقدار INT
SELECT @val + 1; -- این دستور خطا می‌دهد (سرریز).

SET ARITHABORT OFF;
DECLARE @val2 INT = 2147483647;
SELECT @val2 + 1; -- این دستور NULL برمی‌گرداند (با هشدار).

SET CONCAT_NULL_YIELDS_NULL

گزینه SET CONCAT_NULL_YIELDS_NULL نحوه رفتار عملگر الحاق رشته (concatenation operator) (+) را هنگام مواجهه با مقادیر NULL تعیین می‌کند.

• هنگامی که CONCAT_NULL_YIELDS_NULL روی ON تنظیم شده باشد (که توصیه می‌شود)، الحاق هر رشته‌ای با NULL، نتیجه NULL را برمی‌گرداند. این رفتار با استاندارد ANSI مطابقت دارد و منطقی‌ترین روش برای مدیریت NULL به عنوان یک مقدار نامعلوم است.
• هنگامی که CONCAT_NULL_YIELDS_NULL روی OFF باشد، الحاق یک رشته با NULL، NULL را نادیده گرفته و تنها قسمت غیر-NULL را برمی‌گرداند. این می‌تواند منجر به نتایج غیرمنتظره و از دست رفتن اطلاعات شود.

برای اطمینان از رفتار استاندارد و قابل پیش‌بینی الحاق رشته‌ها، این گزینه باید همیشه ON باشد. این گزینه نیز برای نماهای ایندکس‌شده و ستون‌های محاسباتی پایدار لازم است.

مثال الحاق رشته:

SET CONCAT_NULL_YIELDS_NULL ON;
SELECT 'Hello' + NULL + ' World' AS Concat_ON;

SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT 'Hello' + NULL + ' World' AS Concat_OFF;

در حالت ON، نتیجه NULL خواهد بود، در حالی که در حالت OFF، نتیجه ‘Hello World’ است.

SET QUOTED_IDENTIFIER

گزینه SET QUOTED_IDENTIFIER نحوه تفسیر SQL Server از رشته‌های محصور شده در گیومه دوتایی (double quotation marks) را کنترل می‌کند. این یک جنبه مهم در مدیریت اشیاء پایگاه داده است.

• هنگامی که QUOTED_IDENTIFIER روی ON تنظیم شود (که توصیه می‌شود)، رشته‌های داخل گیومه دوتایی به عنوان شناسه‌ها (identifiers) (نام جدول، ستون، تابع و غیره) در نظر گرفته می‌شوند. این به شما امکان می‌دهد از کلمات کلیدی SQL به عنوان نام اشیاء و همچنین از شناسه‌هایی که حاوی فاصله یا کاراکترهای خاص هستند، استفاده کنید.
• هنگامی که QUOTED_IDENTIFIER روی OFF باشد، رشته‌های داخل گیومه دوتایی به عنوان لیترال‌های رشته‌ای (string literals) تفسیر می‌شوند، نه شناسه‌ها. در این حالت، برای ارجاع به شناسه‌ها باید از براکت [] استفاده کنید.

این گزینه برای ایجاد و استفاده از نماهای ایندکس‌شده، ستون‌های محاسباتی پایدار، توابع تعریف شده توسط کاربر (UDFs)، ایندکس‌های XML و بسیاری از ویژگی‌های پیشرفته SQL Server ضروری است. توصیه می‌شود همیشه QUOTED_IDENTIFIER ON باشد.

مثال شناسه‌ها:

CREATE TABLE #TestQuoted (
    "My Column" INT
);
-- این دستور اگر QUOTED_IDENTIFIER OFF باشد، خطا می‌دهد.

SET QUOTED_IDENTIFIER ON;
SELECT "My Column" FROM #TestQuoted;

SET QUOTED_IDENTIFIER OFF;
-- SELECT "My Column" FROM #TestQuoted; -- این دستور خطا می‌دهد
SELECT [My Column] FROM #TestQuoted;

DROP TABLE #TestQuoted;

تأثیر بر عملکرد و ایندکس‌گذاری

یکی از مهم‌ترین دلایل برای تنظیم این گزینه‌های SET روی ON، تأثیر مستقیم آن‌ها بر قابلیت استفاده و عملکرد برخی از ویژگی‌های کلیدی SQL Server است. نماهای ایندکس‌شده و ستون‌های محاسباتی پایدار برای ارائه کارایی بالا در کوئری‌ها طراحی شده‌اند. با این حال، استفاده از آن‌ها مستلزم محیطی کاملاً مشخص و سازگار با استاندارد است تا اطمینان حاصل شود که داده‌ها همیشه به یک شکل ذخیره و تفسیر می‌شوند. اگر هر یک از گزینه‌های ANSI_NULLS، ANSI_PADDING، ANSI_WARNINGS، ARITHABORT، CONCAT_NULL_YIELDS_NULL یا QUOTED_IDENTIFIER در زمان ایجاد یا دسترسی به این اشیاء روی OFF باشند، SQL Server نمی‌تواند از ایندکس‌های مربوطه استفاده کند یا حتی ممکن است اجازه ایجاد آن‌ها را ندهد. این به نوبه خود منجر به کاهش عملکرد کوئری و افزایش بار کاری روی سرور می‌شود.

علاوه بر این، درک و تنظیم صحیح این گزینه‌ها برای حافظه پنهان طرح کوئری (Query Plan Cache) نیز اهمیت دارد. SQL Server طرح‌های اجرای کوئری را بر اساس بسیاری از عوامل، از جمله تنظیمات SET فعلی، ذخیره می‌کند. اگر تنظیمات SET در طول زمان تغییر کنند یا بین جلسات مختلف متفاوت باشند، SQL Server ممکن است مجبور شود برای یک کوئری مشابه، چندین طرح اجرای مختلف را کامپایل و ذخیره کند، که این خود به هدر رفتن منابع و کاهش بهره‌وری منجر می‌شود.

نتیجه‌گیری و توصیه‌ها

درک و مدیریت صحیح گزینه‌های SET در استاندارد ANSI SQL-92 برای هر متخصص SQL Server ضروری است. فعال کردن این گزینه‌ها روی ON (به جز مواردی که دلیل مستدل و خاصی برای غیرفعال کردن آن‌ها وجود دارد) تضمین‌کننده رفتار استاندارد، افزایش یکپارچگی داده‌ها و امکان بهره‌مندی کامل از ویژگی‌های پیشرفته SQL Server مانند نماهای ایندکس‌شده و ستون‌های محاسباتی پایدار است. با رعایت این توصیه‌ها، می‌توانید بهینه‌سازی عملکرد پایگاه داده خود را تضمین کرده و از بروز خطاهای پنهان و رفتارهای غیرمنتظره جلوگیری کنید. همیشه از تنظیمات پیش‌فرض سرور (که معمولاً استاندارد هستند) مطلع باشید و در صورت نیاز، این گزینه‌ها را به صراحت در ابتدای اسکریپت‌ها یا روی سطح بانک اطلاعاتی تنظیم کنید.

-- تنظیمات توصیه شده برای سازگاری با ANSI SQL-92
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;

این تنظیمات به عنوان بهترین روش‌ها در توسعه و مدیریت SQL Server شناخته شده‌اند و می‌توانند تفاوت قابل توجهی در پایداری و عملکرد سیستم‌های شما ایجاد کنند.