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

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

حذف اطلاعات جدول در SQL Server

امروز می‌خواهم روشی را به شما آموزش دهم که مناسب حذف تعداد رکورد زیاد از جداول خیلی بزرگ و نگه داشتن بخشی از اطلاعات آن جدول است.دقت کنید که در این سناریو جدول اصلی هیچ کلید خارجی ندارد . اگر جداول کلید خارجی داشته باشه ، شما باید یک سناریوی چند جدولی داشته باشید تا جامعیت اطلاعات حفظ گردد. این آموزش برای یک سناریوی ساده مثل جدول‌های 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];

مرحله ۶: حذف اطلاعات جدول در SQL Server

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

سخن پایانی

حذف اطلاعات جدول در SQL Server، در SQL Server گاهی اوقات لازم می شود که از یک جدول کوئری هایی را به دلایل مختلف حذف کنیم. حذف اطلاعات کمک می کند تا فقط داده های مفید داخل پایگاه داده باقی بمانند. مخصوصا در جدول های با تعداد کوئری بالا. در این مقاله به حذف اطلاعات از جدول به صورت کامل پرداخته ایم. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.