انتقال جدولی با فیلدهایی از نوع LOB به فایل گروپ دیگر

هنگامی که یک جدول SQL را به یک فایل گروه جدید از طریق رویه بازایجاد کلاسترد ایندکس منتقل می‌کنید، فقط انواع داده‌های «معمولی» منتقل می‌شوند (مانند «int»، «datetime»، «varchar» و غیره). text، image، varbinary(max) و ستون های مشابه (معروف به “BLOB-fields”) در مکان فعلی خود باقی می مانند. اگر روی تنظیمات “storage” جدول زیر آیتم منوی زمینه “Properties” کلیک کنید، خواهید دید که “text filegroup” مانند قبل است!

” text filegroup ” را نمی توان بدون ایجاد مجدد جدول تغییر داد. باید جدول را دوباره ایجاد کنیم و تمام داده ها را در این جدول جدید کپی کنیم. اما انجام این کار را به صورت دستی مشکل است. جدولی را رها حذف کرده و دوباره ایجاد کنید با همه ایندکس‌ها، کلیدهای خارجی، کلیدهای اصلی، آیدنتیی ها… آیا یک روش خودکار برای این کار وجود دارد؟  پاسخ: بله

روش قدیمی

تا قبل از ورژن 2016 SQL Sever اینکارها را میتوانستیم انجام دهیم:

1. در MS SQL Management Studio روی “Tools – Options – Designer” کلیک کنید و تیک کادر “Prevent saving changes that require table re-creation ” را بردارید.
2. روی جدول کلیک راست کرده و “Design” را برای SQL 2008 تا 2014 SQL یا “Modify” را برای SQL 2005 انتخاب کنید.
3. F4 را فشار دهید تا پنجره “properties” باز شود.
4. مطمئن شوید که جدول (!) را در بالای پنجره “properties” انتخاب کرده اید.
5. «text filegroup» را برای جدول تغییر دهید.
6. صبر کنید! تغییرات خود را ذخیره نکنید. Server SQL به احتمال زیاد یک خطای مهلت زمانی برای جدول بزرگ شما ایجاد می کند.
7. به جای آن “Generate change script” را انتخاب کنید و متن حاصل را دریک پنجره query new کپی کنید.
8. کوئری را اجرا کنید.

روش بالا هنوز کار می کند، اما امروزه در نسخه های جدید SQL Server می توانید این کار را به صورت آنلاین (!) با استفاده از اسکریپت ها انجام دهید. ترفند این است که از پارتیشن بندی استفاده کنید – مکانیزم داخلی که امکان جابجایی خودکار داده ها بین فایل گروه ها را فراهم می کند.

روش جدید

فرض کنید جدول زیر را ایجاد کردیم:

Create TABLE MyTable (ID INT, LOB_Data VARBINARY(MAX))

مراحل زیر را دنبال کنید:

• کلاسترد ایندکس ایجاد و آنرا به فایل گروه دیگری که دارید (دراینجا مثلا Secondary) انتقال دهید:

CREATE UNIQUE CLUSTERED INDEX PK_MyTable

ON MyTable(ID)

WITH (DROP_EXISTING = ON, ONLINE = ON)

ON SECONDARY

همه چیز به فایل گروپ دیگر انتقال پیدا میکند به جز فیلد LOB.

• حداکثر مقدارID را با کوئری SELECT MAX(ID) FROM MyTable دریافت کنید و نتیجه را به خاطر بسپارید. فرض کنید 9999 باشد.
• یک تابع پارتیشن بندی برای مقدار کمی بزرگتر ایجاد کنید:

CREATE PARTITION FUNCTION pf_MyTable_temp (INT) AS RANGE RIGHT FOR VALUES (10100)

• اسکیمای پارتیشن خود را نیز بر اساس تابع پارتیشن ایجاد کنید:

CREATE PARTITION SCHEME ps_MyTable_tempAS PARTITION pf_MyTable_temp 

TO ( [SECONDARY], [PRIMARY] )

• کلاسترد ایندکس خود را بر اساس پارتیشن بندی باز ایجاد کنید:

CREATE UNIQUE CLUSTERED INDEX PK_MyTableON MyTable(ID) WITH (ONLINE = ON, DROP_EXISTING = ON) ON ps_MyTable_temp (UserID)

• در این مرحله داده ها به SECONDARY منتقل می شوند، اما همه آن ها بر اساس اسکیمای پارتیشن بندی ما هستند.
• دوباره کلاسترد را باز ایجاد کنید – بدون پارتیشن بندی:

CREATE UNIQUE CLUSTERED INDEX PK_MyTableON MyTable(ID) WITH (DROP_EXISTING = ON, ONLINE = ON) ON SECONDARY

• کار ما به اتمام رسید و اکنون فیلد BLOB ما هم به فایل گروه دیگر انتقال پیدا کرد. حال دیگر میتوانیم تابع پارتیشن و اسکیمای پارتیشن را پاک کنیم.

;DROP PARTITION SCHEME ps_MyTable_temp

;DROP PARTITION FUNCTION pf_MyTable_temp