بهینه سازی عملکرد SQL Server با SET QUOTED_IDENTIFIER و SET ANSI_PADDING

بهینه‌سازی SQL Server: راهنمای جامع SET QUOTED_IDENTIFIER و SET ANSI_PADDING برای عملکرد بهتر

این مقاله به دو تنظیم مهم در SQL Server یعنی `SET QUOTED_IDENTIFIER` و `SET ANSI_PADDING` می‌پردازد. درک صحیح و استفاده مناسب از این تنظیمات برای جلوگیری از مشکلات احتمالی، به خصوص در زمان کار با اشیاء پایگاه داده که به این تنظیمات حساس هستند، ضروری است.

SET QUOTED_IDENTIFIER

`SET QUOTED_IDENTIFIER` یکی از تنظیمات اتصال (connection setting) در SQL Server است که نحوه تفسیر رشته‌ها را توسط سرور مشخص می‌کند. وقتی این گزینه `ON` باشد (که حالت پیش‌فرض و توصیه‌شده است)، شناسه‌ها (identifier) که در کوتیشن دوتایی (double quotes) قرار می‌گیرند، به عنوان نام آبجکت‌ها (مانند نام ستون یا جدول) در نظر گرفته می‌شوند. رشته‌هایی که در کوتیشن تکی (single quotes) هستند، به عنوان لیترال‌های رشته‌ای (string literals) تفسیر می‌شوند.

این تنظیم قابلیت تعریف آبجکت‌هایی با نام‌های شامل کاراکترهای خاص یا کلمات کلیدی رزرو شده SQL را فراهم می‌کند. اگر `SET QUOTED_IDENTIFIER` روی `OFF` تنظیم شده باشد، شناسه‌ها نمی‌توانند با کوتیشن دوتایی مشخص شوند و فقط با براکت `[]` قابل استفاده هستند. در این حالت، رشته‌های داخل کوتیشن دوتایی نیز به عنوان لیترال‌های رشته‌ای تفسیر می‌شوند که می‌تواند باعث ابهام شود.

SQL Server این تنظیم را به شکل زیر مدیریت می‌کند:

SET QUOTED_IDENTIFIER { ON | OFF }

برای ایجاد و دستکاری Viewهای ایندکس‌دار (Indexed Views)، ستون‌های محاسباتی (Computed Columns) و توابع تعریف‌شده توسط کاربر (UDFs) روی ستون‌های محاسباتی، `SET QUOTED_IDENTIFIER` باید روی `ON` باشد. اگر هنگام ایجاد این آبجکت‌ها این تنظیم روی `OFF` باشد، با خطا مواجه خواهید شد. این الزام به دلیل ماهیت این آبجکت‌هاست که برای اطمینان از صحت داده‌ها و عملکرد، نیاز به تفسیر دقیق شناسه‌ها دارند.

بیایید یک مثال را بررسی کنیم. اگر `SET QUOTED_IDENTIFIER` را روی `OFF` تنظیم کنیم و سعی کنیم جدولی با یک ستون که نام آن در کوتیشن دوتایی قرار دارد ایجاد کنیم:

SET QUOTED_IDENTIFIER OFF;
GO
CREATE TABLE TestQuotedIdentifier (id INT, "Test Column" VARCHAR(50));
GO

این دستور با خطا مواجه می‌شود زیرا SQL Server عبارت `”Test Column”` را به عنوان یک ستون معتبر با نام حاوی فاصله در نظر نمی‌گیرد؛ در عوض آن را یک لیترال رشته‌ای تفسیر می‌کند.

حالا اگر `SET QUOTED_IDENTIFIER` را روی `ON` تنظیم کنیم:

SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE TestQuotedIdentifier (id INT, "Test Column" VARCHAR(50));
GO

این دستور با موفقیت اجرا می‌شود، زیرا `”Test Column”` به عنوان یک نام ستون معتبر با فاصله تفسیر می‌شود.

در زمان فراخوانی (query) داده‌ها نیز `SET QUOTED_IDENTIFIER` اهمیت دارد. فرض کنید جدولی با نام ستون `”Test Column”` داریم. اگر `SET QUOTED_IDENTIFIER` روی `OFF` باشد و بخواهیم این ستون را با کوتیشن دوتایی انتخاب کنیم:

SET QUOTED_IDENTIFIER OFF;
GO
SELECT "Test Column" FROM TestQuotedIdentifier;
GO

این دستور نیز با خطا مواجه می‌شود زیرا SQL Server `”Test Column”` را به عنوان یک لیترال رشته‌ای می‌شناسد نه یک نام ستون.

با `SET QUOTED_IDENTIFIER ON`، همان دستور به درستی اجرا می‌شود:

SET QUOTED_IDENTIFIER ON;
GO
SELECT "Test Column" FROM TestQuotedIdentifier;
GO

این مثال نشان می‌دهد که برای کار با نام‌های آبجکت که نیاز به کوتیشن دوتایی دارند، `SET QUOTED_IDENTIFIER` باید `ON` باشد. توصیه می‌شود همیشه این تنظیم را روی `ON` نگه دارید تا از ابهامات و خطاهای احتمالی جلوگیری شود.

SET ANSI_PADDING

`SET ANSI_PADDING` یکی دیگر از تنظیمات اتصال است که نحوه برخورد SQL Server با کاراکترهای اضافی (trailing spaces) در ستون‌های نوع `CHAR`، `VARCHAR` و بیت‌های صفر اضافی (trailing zeros) در ستون‌های `BINARY` و `VARBINARY` را تعیین می‌کند. این تنظیم بر نحوه ذخیره‌سازی و مقایسه این مقادیر تأثیر می‌گذارد.

تنظیم `SET ANSI_PADDING` به شکل زیر است:

SET ANSI_PADDING { ON | OFF }

وقتی `SET ANSI_PADDING` روی `ON` است (که حالت توصیه‌شده و پیش‌فرض در نسخه‌های جدید SQL Server است)، رشته‌های `CHAR` و `BINARY` با کاراکترهای خالی یا صفرهای بایت به طول کامل ستون پر می‌شوند. در مورد `VARCHAR` و `VARBINARY`، هیچ فضای خالی یا صفر اضافی اضافه نمی‌شود و تمام فضاهای خالی انتهایی یا صفرهای بایت انتهایی ذخیره می‌شوند.

اگر `SET ANSI_PADDING` روی `OFF` باشد، مقادیر `CHAR` و `BINARY` همچنان به طول کامل ستون پر می‌شوند، اما مقادیر `VARCHAR` و `VARBINARY` که فقط شامل فضاهای خالی انتهایی یا صفرهای بایت انتهایی هستند، این کاراکترهای اضافی را از دست می‌دهند. همچنین، در مقایسه‌ها، فضاهای خالی انتهایی نادیده گرفته می‌شوند، که می‌تواند باعث نتایج غیرمنتظره در کوئری‌ها شود.

برای ایجاد ایندکس‌ها روی ستون‌های `CHAR`، `VARCHAR`، `BINARY` و `VARBINARY` و همچنین برای ستون‌هایی که شامل محدودیت‌های `UNIQUE` یا `PRIMARY KEY` هستند، `SET ANSI_PADDING` باید `ON` باشد. این الزام تضمین می‌کند که مقایسه‌ها و ذخیره‌سازی داده‌ها به طور دقیق و یکسان انجام شود.

بیایید با یک مثال `SET ANSI_PADDING` را بررسی کنیم. ابتدا `SET ANSI_PADDING` را روی `OFF` تنظیم می‌کنیم:

SET ANSI_PADDING OFF;
GO
CREATE TABLE TestAnsiPadding (col1 CHAR(10), col2 VARCHAR(10));
GO
INSERT INTO TestAnsiPadding VALUES ('abc   ', 'xyz   ');
GO

در این حالت، `col1` (از نوع `CHAR(10)`) با فضاهای خالی به طول ۱۰ کاراکتر پر می‌شود، اما `col2` (از نوع `VARCHAR(10)`) ممکن است فضاهای خالی انتهایی را از دست بدهد.

حالا `SET ANSI_PADDING` را روی `ON` تنظیم می‌کنیم:

SET ANSI_PADDING ON;
GO
CREATE TABLE TestAnsiPaddingOn (col1 CHAR(10), col2 VARCHAR(10));
GO
INSERT INTO TestAnsiPaddingOn VALUES ('abc   ', 'xyz   ');
GO

در این سناریو، `col1` باز هم با فضاهای خالی به طول کامل پر می‌شود. اما نکته مهم در `col2` این است که تمام فضاهای خالی انتهایی حفظ می‌شوند.

تفاوت اصلی در هنگام مقایسه و استفاده از توابع مانند `DATALENGTH` مشخص می‌شود.
با `SET ANSI_PADDING OFF`:

SET ANSI_PADDING OFF;
GO
SELECT DATALENGTH(col1), DATALENGTH(col2) FROM TestAnsiPadding WHERE col1 = 'abc';
GO

در این حالت، `col1 = ‘abc’` ممکن است نتایج غیرمنتظره‌ای بدهد زیرا فضاهای خالی انتهایی در مقایسه نادیده گرفته می‌شوند. `DATALENGTH(col1)` معمولاً ۱۰ را برمی‌گرداند (به دلیل پر شدن) و `DATALENGTH(col2)` ممکن است ۳ را برگرداند (اگر فضاهای خالی انتهایی حذف شده باشند).

با `SET ANSI_PADDING ON`:

SET ANSI_PADDING ON;
GO
SELECT DATALENGTH(col1), DATALENGTH(col2) FROM TestAnsiPaddingOn WHERE col1 = 'abc';
GO

در این حالت، `col1 = ‘abc’` فقط در صورتی مطابقت دارد که ستون دقیقاً سه کاراکتر `abc` و هفت فضای خالی داشته باشد. `DATALENGTH(col1)` همچنان ۱۰ را برمی‌گرداند. اما `DATALENGTH(col2)` در این سناریو مقدار اصلی وارد شده یعنی ۶ را برمی‌گرداند (برای ‘xyz ‘)، زیرا فضاهای خالی انتهایی حفظ شده‌اند.

توصیه‌ها

برای اطمینان از عملکرد صحیح و جلوگیری از بروز خطاها در SQL Server، به شدت توصیه می‌شود که هر دو تنظیم `SET QUOTED_IDENTIFIER` و `SET ANSI_PADDING` همیشه روی `ON` باشند. این حالت نه تنها با استانداردهای ANSI مطابقت دارد، بلکه برای بسیاری از ویژگی‌های پیشرفته SQL Server مانند Viewهای ایندکس‌دار و ستون‌های محاسباتی الزامی است. همچنین، استفاده از این تنظیمات در حالت `ON` به افزایش خوانایی و قابلیت نگهداری کد SQL کمک کرده و از رفتارهای غیرمنتظره در مقایسه‌ها و ذخیره‌سازی داده‌ها جلوگیری می‌کند. این کار به پایداری پایگاه داده و دقت نتایج کوئری‌ها کمک شایانی می‌کند.