به روز رسانی کارآمد ستون های MAX در SQL Server با WRITE Clause

به روز رسانی کارآمد داده‌های VARBINARY(MAX)، VARCHAR(MAX) و NVARCHAR(MAX) در SQL Server با WRITE Clause

زمانی که نیاز به به روز رسانی جزئی داده‌ها در ستون‌های بزرگ از نوع `VARBINARY(MAX)`, `VARCHAR(MAX)`, یا `NVARCHAR(MAX)` در SQL Server دارید، استفاده از دستور `UPDATE` معمولی می‌تواند ناکارآمد باشد. این روش، حتی برای تغییرات کوچک، کل داده شیء بزرگ (LOB) را از شبکه به سمت سرور می‌فرستد و سپس آن را بازنویسی می‌کند که منجر به مصرف منابع زیاد و کندی عملکرد می‌شود.

فرض کنید جدولی با یک ستون `VARBINARY(MAX)` برای ذخیره فایل‌های PDF یا تصاویر دارید.

برای ایجاد یک جدول و درج داده آزمایشی، از کد زیر استفاده می‌کنیم:

CREATE TABLE BinaryData (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    DataName VARCHAR(255),
    BinaryContent VARBINARY(MAX)
);

INSERT INTO BinaryData (DataName, BinaryContent) VALUES ('Test File', 0x476C6F62616C2053514C2053657276657220436F6D6D756E697479);

برای تست یک فایل بزرگ، یک فایل dummy با استفاده از ابزار `DUMP` در سیستم عامل لینوکس (یا روش‌های مشابه در ویندوز) ایجاد و آن را در یک ستون `VARBINARY(MAX)` درج می‌کنیم. این فایل حدود 10 مگابایت است.

فایل `dummy_file.bin` با دستور زیر ایجاد شده است:

dd if=/dev/zero of=dummy_file.bin bs=1M count=10

سپس برای درج این فایل در SQL Server از `OPENROWSET` استفاده می‌کنیم. مطمئن شوید که `BULK` در تنظیمات سرور فعال است.

INSERT INTO BinaryData (DataName, BinaryContent)
SELECT 'Large Binary File', BulkColumn
FROM OPENROWSET(BULK 'C:\temp\dummy_file.bin', SINGLE_BLOB) AS x;

اکنون که داده‌های بزرگ را در جدول داریم، به سراغ مشکل به روز رسانی می‌رویم. اگر بخواهیم فقط بخش کوچکی از این داده بزرگ را تغییر دهیم، استفاده از `UPDATE` معمولی باعث ارسال و بازنویسی کل 10 مگابایت داده می‌شود. برای مثال، اگر بخواهیم 10 بایت اول را تغییر دهیم:

UPDATE BinaryData
SET BinaryContent = 0x112233445566778899AA + SUBSTRING(BinaryContent, 11, DATALENGTH(BinaryContent) - 10)
WHERE ID = 2;

این روش ناکارآمد است. راه حل بهینه استفاده از عبارت `WRITE` است که امکان به روز رسانی بخشی از یک ستون `(MAX)` را فراهم می‌کند بدون اینکه کل داده را دستکاری کند.

سینتکس عمومی `WRITE` به شکل زیر است:

UPDATE table_name
SET column_name.WRITE ( expression , offset , length )
WHERE conditions;

اجزا عبارت `WRITE` عبارتند از:
* `expression`: داده جدیدی که قرار است درج شود.
* `offset`: نقطه‌ی شروع در بایت (برای `VARBINARY(MAX)`) یا کاراکتر (برای `VARCHAR(MAX)` و `NVARCHAR(MAX)`) که از آنجا `expression` جایگذاری می‌شود. این یک مقدار صفر-مبنا (zero-based) است.
* `length`: تعداد بایت‌ها یا کاراکترهایی که `expression` جایگزین آن‌ها می‌شود. اگر `length` برابر صفر باشد، `expression` در `offset` درج می‌شود و داده‌های موجود به جلو منتقل می‌شوند.

**مثال برای `VARBINARY(MAX)`:**

برای به روز رسانی 10 بایت در `offset` صفر در ستون `BinaryContent` برای رکورد با `ID = 2` از کد زیر استفاده می‌کنیم:

UPDATE BinaryData
SET BinaryContent.WRITE(0xBBCCDDEEFF0011223344, 0, 10)
WHERE ID = 2;

این دستور به طور موثر فقط 10 بایت ابتدایی را به روز رسانی می‌کند.

**مثال برای `VARCHAR(MAX)`:**

برای نشان دادن `VARCHAR(MAX)`، ابتدا یک جدول جدید ایجاد می‌کنیم و داده درج می‌کنیم:

CREATE TABLE TextData (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    TextName VARCHAR(255),
    LargeText VARCHAR(MAX)
);

INSERT INTO TextData (TextName, LargeText) VALUES ('Long Article', REPLICATE('This is a test sentence. ', 1000));

اکنون برای به روز رسانی قسمتی از این متن بزرگ، مثلاً 20 کاراکتر از موقعیت 50ام، از `WRITE` استفاده می‌کنیم:

UPDATE TextData
SET LargeText.WRITE('New inserted text here!', 50, 20)
WHERE ID = 1;

این دستور ‘New inserted text here!’ را در موقعیت 50ام قرار می‌دهد و 20 کاراکتر موجود را جایگزین می‌کند.

اگر بخواهیم در موقعیت 50ام متنی را **درج** کنیم بدون جایگزینی (یعنی `length` را 0 بگیریم)، از این کد استفاده می‌کنیم:

UPDATE TextData
SET LargeText.WRITE('INSERTED TEXT HERE!', 50, 0)
WHERE ID = 1;

**مثال برای `NVARCHAR(MAX)`:**

برای `NVARCHAR(MAX)` که از کاراکترهای یونیکد استفاده می‌کند، رویکرد مشابهی داریم. یک جدول جدید برای این نوع داده ایجاد می‌کنیم:

CREATE TABLE NTextData (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    NTextName NVARCHAR(255),
    LargeNText NVARCHAR(MAX)
);

INSERT INTO NTextData (NTextName, LargeNText) VALUES ('Unicode Article', N'این یک متن آزمایشی یونیکد است. ' + REPLICATE(N'این یک متن آزمایشی یونیکد است. ', 500));

برای به روز رسانی 25 کاراکتر از موقعیت 100 در ستون `LargeNText` از این کد استفاده می‌کنیم:

UPDATE NTextData
SET LargeNText.WRITE(N'متن جدید فارسی برای جایگذاری.', 100, 25)
WHERE ID = 1;

عبارت `WRITE` در مقایسه با `UPDATE` معمولی، کارایی بسیار بیشتری در مدیریت به روز رسانی‌های جزئی داده‌های LOB (Large Object) ارائه می‌دهد و ترافیک شبکه و استفاده از منابع سرور را به طور قابل توجهی کاهش می‌دهد. این قابلیت در سناریوهایی که نیاز به تغییرات مکرر و کوچک در بخش‌های خاصی از داده‌های بسیار بزرگ دارید، حیاتی است. این روش به خصوص برای داده‌های باینری مانند تصاویر یا اسناد که فقط بخش‌های کوچکی از آنها نیاز به تغییر دارند، بسیار مفید است.

همچنین لازم به ذکر است که `offset` و `length` می‌توانند با استفاده از توابع و متغیرها به صورت پویا تعیین شوند، که انعطاف‌پذیری بیشتری در به روز رسانی‌های پیچیده‌تر فراهم می‌کند. دقت کنید که این قابلیت برای SQL Server 2005 و نسخه‌های جدیدتر در دسترس است.