افزایش عرض ستون در SQL Server بدون سربار I/O زیاد (ALTER COLUMN)

افزایش عرض ستون در SQL Server بدون سربار I/O زیاد (ALTER COLUMN)

در SQL Server، هنگام افزایش عرض یک ستون (مثلاً از `VARCHAR(50)` به `VARCHAR(100)`)، ممکن است تصور کنید که عملیات `ALTER TABLE` باعث بازنویسی کل جدول و ایجاد سربار قابل توجه ورودی/خروجی (I/O) می‌شود. اما همیشه اینطور نیست و درک نحوه ذخیره‌سازی داده‌ها می‌تواند به بهینه‌سازی عملیات کمک کند.

این مقاله به بررسی این موضوع می‌پردازد که چگونه می‌توانید عرض یک ستون را در SQL Server افزایش دهید بدون اینکه بلافاصله سربار I/O غیرضروری ایجاد شود، و دلایل فنی پشت این رفتار را توضیح می‌دهد.

یک ستون از نوع `VARCHAR`، `NVARCHAR` یا `VARBINARY` در SQL Server به گونه‌ای طراحی شده است که تا زمانی که طول واقعی داده از حداکثر ظرفیت قابل جای‌گیری در صفحه (حدود ۸۰۶۰ بایت) تجاوز نکند، سربار I/O اضافی ایجاد نمی‌شود. این کار به لطف نحوه ذخیره‌سازی داده‌ها در صفحات داده و آرایه VarNet انجام می‌شود.

**بررسی رفتار پیش‌فرض SQL Server**

دستور `ALTER TABLE` برای تغییر عرض ستون، فقط متادیتای جدول را به‌روزرسانی می‌کند و نیازی به بازنویسی کامل داده‌ها در دیسک ندارد. به عنوان مثال، فرض کنید جدولی با ستون `MyColumn` از نوع `VARCHAR(50)` دارید. اگر آن را به `VARCHAR(100)` تغییر دهید:


ALTER TABLE dbo.MyTable ALTER COLUMN MyColumn VARCHAR(100);


این تغییر بلافاصله باعث سربار I/O قابل توجهی نخواهد شد، مگر اینکه شرایط خاصی پیش بیاید.

**چرا سربار I/O فوری ایجاد نمی‌شود؟**

SQL Server داده‌های با طول متغیر را در صفحه داده ذخیره می‌کند. در هر صفحه داده، یک ساختار به نام “VarNet array” وجود دارد که آفست (محل شروع) هر ستون با طول متغیر در رکورد را ذخیره می‌کند. اندازه این آرایه و هدر ردیف، صرف نظر از حداکثر طول تعریف شده برای ستون، ثابت باقی می‌ماند.

یعنی، `VARCHAR(50)` و `VARCHAR(100)` هر دو به یک تعداد بایت در آرایه VarNet نیاز دارند تا آفست خود را ذخیره کنند. تفاوت زمانی ایجاد می‌شود که طول واقعی داده‌ها از ظرفیت اولیه صفحه تجاوز کند.

**داده‌های In-Row و Off-Row**

وقتی طول واقعی داده‌های یک ستون با طول متغیر از حداکثر اندازه ردیف (`8060 بایت`) فراتر رود، SQL Server داده‌ها را به خارج از ردیف اصلی منتقل می‌کند (off-row storage). در این صورت، ردیف اصلی فقط یک اشاره‌گر به مکان واقعی داده‌ها در صفحه‌ای دیگر را نگهداری می‌کند.

(Page ID and File ID)

این انتقال به داده‌های خارج از ردیف (off-row) است که منجر به سربار I/O و Page Split می‌شود. این انتقال تنها زمانی رخ می‌دهد که شما داده‌هایی را درج یا به‌روزرسانی کنید که طول واقعی آن‌ها از ۸۰۶۰ بایت بیشتر باشد، نه صرفاً با تغییر متادیتای عرض ستون.

برای ستون‌های `VARCHAR(MAX)`، `NVARCHAR(MAX)`، و `VARBINARY(MAX)`:

VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)

این ستون‌ها نیز به همین روش مدیریت می‌شوند، اما حد آستانه ۸۰۶۰ بایت برای آن‌ها متفاوت است و می‌توانند داده‌های بسیار بزرگتری را ذخیره کنند که تقریباً همیشه خارج از ردیف اصلی ذخیره می‌شوند.

**بررسی وضعیت ذخیره‌سازی فعلی**

برای درک اینکه آیا داده‌های شما در حال حاضر به صورت in-row یا off-row ذخیره شده‌اند و میزان فضای مصرفی، می‌توانید از دستورات زیر استفاده کنید:

1. **بررسی اطلاعات ستون‌ها:**
این دستور به شما کمک می‌کند تا `max_length` ستون‌ها را پس از تغییر مشاهده کنید.


SELECT name, max_length
FROM sys.columns
WHERE object_id = OBJECT_ID('YourTableName')
AND name = 'YourColumnName';


2. **استفاده از `sp_spaceused`:**
این رویه ذخیره شده، اطلاعات کلی در مورد فضای مصرفی جدول را ارائه می‌دهد.


EXEC sp_spaceused 'YourTableName';


3. **استفاده از `sys.dm_db_partition_stats`:**
این DMV (Dynamic Management View) جزئیات دقیقی در مورد فضای مصرفی هر پارتیشن (و در نتیجه هر جدول) ارائه می‌دهد، شامل تعداد صفحات مصرفی و تعداد بایت‌های ذخیره شده خارج از ردیف (off-row data).


SELECT
OBJECT_NAME(p.object_id) AS TableName,
p.index_id,
p.partition_id,
p.in_row_data_page_count,
p.in_row_data_used_pages,
p.row_overflow_data_page_count,
p.row_overflow_data_used_pages,
p.lob_used_pages
FROM
sys.dm_db_partition_stats p
WHERE
p.object_id = OBJECT_ID('YourTableName');


* `in_row_data_page_count`: تعداد صفحات داده‌ای که داده‌های درون ردیفی را نگهداری می‌کنند.
* `row_overflow_data_page_count`: تعداد صفحاتی که داده‌های خارج از ردیف (off-row) را نگهداری می‌کنند. افزایش این مقدار نشان‌دهنده وقوع سربار I/O و Page Split است.
* `lob_used_pages`: تعداد صفحاتی که برای ذخیره‌سازی LOB (Large Object) (مانند `text`, `ntext`, `image` و `(MAX)` types) استفاده می‌شوند.

**تاثیر Vardecimal Storage Format**

در SQL Server 2008 و نسخه‌های جدیدتر، امکان استفاده از `vardecimal storage format` وجود دارد. اگر این ویژگی فعال باشد، ستون‌های `DECIMAL` و `NUMERIC` نیز به عنوان ستون‌های با طول متغیر ذخیره می‌شوند. این بدان معناست که افزایش دقت (precision) یک ستون `DECIMAL` ممکن است تا زمانی که طول واقعی داده‌ها افزایش نیابد، سربار I/O فوری ایجاد نکند.

**نتیجه‌گیری**

تغییر تعریف طول یک ستون با طول متغیر (مانند `VARCHAR`) در SQL Server، تا زمانی که طول واقعی داده‌های موجود از آستانه ۸۰۶۰ بایت (که باعث انتقال به ذخیره‌سازی خارج از ردیف می‌شود) تجاوز نکند، لزوماً باعث سربار I/O قابل توجه یا بازسازی جدول نمی‌شود. SQL Server به صورت هوشمندانه متادیتای ستون را به‌روزرسانی می‌کند. سربار I/O و Page Split زمانی رخ می‌دهد که شما داده‌های جدیدی را درج یا به‌روزرسانی کنید که طول آن‌ها از ظرفیت فعلی صفحه بیشتر باشد و مجبور به ذخیره‌سازی خارج از ردیف شوند. استفاده از Dynamic Management Views مانند `sys.dm_db_partition_stats` به شما کمک می‌کند تا وضعیت ذخیره‌سازی داده‌های خود را به دقت پایش کنید.