حذف اطلاعات از جدول با تعداد رکورد بالا با کمترین Log در SQL Server

مقدمه

به عنوان یک DBA یا Developer گاها پیش می‌آید که لازم باشد از یک جدول خیلی بزرگ تعداد محدودی از رکوردها رو نگه داریم و بخش زیادی از مقادیر آن جدول رو حذف کنیم (به عنوان مثال ۱۰۰۰ رکورد از یک جدول ۱ میلیاردی را نگه داریم و مابقی رکوردها رو حذف کنیم) که اگر برای حذف رکوردها بخواهیم از روش‌های معمول استفاده کنیم زمان خیلی زیادی جهت حذف رکوردها مورد نیاز است و لاگ زیادی تولید می‍شود و در نهایت سیستم دچار مشکل می‌گردد امروز می‌خواهم روشی را به شما آموزش دهم که مناسب حذف تعداد رکورد زیاد از جداول خیلی بزرگ و نگه داشتن بخشی از اطلاعات آن جدول است.
دقت کنید که در این سناریو جدول اصلی هیچ کلید خارجی ندارد . اگر جداول کلید خارجی داشته باشه ، شما باید یک سناریوی چند جدولی داشته باشید تا جامعیت اطلاعات حفظ گردد. این آموزش برای یک سناریوی ساده مثل جدول‌های Log و بدون محدودیت کلید خارجی در نظر گرفته شده است .
خب برای شروع کار یک جدول تستی ایجاد می‌کنیم

مرحله ۱: ایجاد جدول

CREATE TABLE [dbo].[Test1]  (
   Col1_id INT IDENTITY(1,1),
   Col2_D DATETIME DEFAULT GETDATE(),
   Col3_C NCHAR(20) DEFAULT 'My test desc!',
   Col4_T NVARCHAR(MAX) DEFAULT REPLICATE('0123456789', 100)
);
GO

در مرحله بعد تعدادی رکورد تستی در جدول وارد می‌کنیم.
برای این مثال ۵۰۰۰ رکورد و ۲ ایندکس ایجاد می‌کنیم.

مرحله ۲: ایجاد دیتای تستی

--۲. Load Test Data
SET NOCOUNT ON;
INSERT [dbo].[Test1]  (Col2_D, Col3_C, Col4_T)
VALUES (DEFAULT, 'My test desc!', DEFAULT);
GO 5000
--Check it.
SELECT top 10 * FROM [dbo].[Test1];
SELECT COUNT(*) As 'Test1' FROM [dbo].[Test1];
GO
--Create Indexes
CREATE CLUSTERED INDEX [ClusteredIndex_Test1_Col2_D] ON [dbo].[Test1]
([Col2_D] ASC)
GO
CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex_Test1_Col1_ID] ON [dbo].[Test1]
([Col1_id] ASC)
GO

تصویر زیر نتیجه کوری بالا را نمایش می‌دهد:

مرحله ۳: ایجاد یک جدول خالی با ساختار مشابه جدول اصلی

--۳. Now let's duplicate the original schema and change the table and index names
CREATE TABLE [dbo].[Test1_SWITCH] (
   Col1_ID INT IDENTITY(1,1),
   Col2_D DATETIME DEFAULT GETDATE(),
   Col3_C NCHAR(20) DEFAULT 'My test desc!',
   Col4_T NVARCHAR(MAX) DEFAULT REPLICATE('0123456789', 201)
);
CREATE CLUSTERED INDEX [ClusteredIndex_Test1_SWITCH_Col2_D] ON [dbo].[Test1_SWITCH]
([Col2_D] ASC)
GO
CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex_Test1_SWITCH_Col1_ID] ON [dbo].[Test1_SWITCH]
([Col1_id] ASC)
GO
--Check it
SELECT COUNT(*) AS 'Test1' FROM [dbo].[Test1];
SELECT COUNT(*) AS 'Test1_SWITCH' FROM [dbo].[Test1_SWITCH];

تصویر زیر ۵۰۰۰ ردیف در جدول اصلی و ۰ ردیف در جدول جدید را نشان می‌دهدمرحله ۴: سویج جداول

دستور Alter Table SWITCH را اجرا و تعداد رکوردها را بررسی کند.

--۴. Switch the data to a new table
ALTER TABLE [dbo].[Test1] SWITCH to [dbo].[Test1_SWITCH];
SELECT COUNT(*) AS 'Test1' FROM [dbo].[Test1];
SELECT COUNT(*) AS 'Test1_SWITCH' FROM [dbo].[Test1_SWITCH];

هماهنطور که مشاهده می‌کنید اطلاعات با سرعت بالا به جدول جدید انتقال پیدا کردند.

مرحله ۵: انتقال دیتای مورد نیاز به جدول اصلی

حالا می‌توانید مجموعه کوچکی از رکوردها را به جدول اصلی منتقل کنید در این مثال من یک فیلد Identity دارم ، بنابراین برای حفظ مقادیر شمارشی این فیلد باید از IDENTITY_INSERT استفاده کنیم. این مورد ممکنه است در سناریوی دنیای واقعی شما یکسان نباشد.

--۵. Now we can load the small subset of data back to the table.
SET NOCOUNT ON;
SET IDENTITY_INSERT [dbo].[Test1] ON;
INSERT dbo.Test1 (Col1_ID, Col2_D, Col3_C, Col4_T)
SELECT Col1_ID, Col2_D, Col3_C, Col4_T FROM [dbo].[Test1_SWITCH] WHERE Col1_ID <= 1000;
SET IDENTITY_INSERT [dbo].[Test1] OFF;
SELECT COUNT(*) AS 'Test1' FROM [dbo].[Test1];
SELECT COUNT(*) AS 'Test1_SWITCH' FROM [dbo].[Test1_SWITCH];

مرحله ۶: حذف جدول Test1_Switch

--۶. Finally drop the newly created switch table.
DROP TABLE [dbo].[Test1_SWITCH];