دستورات Delete و Truncate در SQL

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

همه ی اونهایی که با SQL سر و کله زدن دستور Delete را می شناسن. خب زندگی بدون پاک کردن بعضی چیزها جلو نمی ره ? و خواه ناخواه چیزی را که نمی خوایم و لازم نداریم باید به خاطر هزینه های زیاد نگهداریش پاک کنیم . توی دنیای دیتابیس هم لازمه بعضی رکورد ها را حذف کرد و اون چیزی که برای حذف رکوردها بین متخصصین SQL یا DBA ها رایجه اینه که بنویسن :

Delete from Table_Name Where condition

بیاید یه سناریوی بزرگتر را در نظر بگیریم ، دیتابیس بزرگ و حجیمی در اختیار شماست که چندین گیگ حجم داره و قراره که شما بعضی از اطلاعات اون را پاک کنید. برای چی ؟

دو تا مثال واقعی بزنم :

• در یک نرم افزار انبار داری ، کارفرما از شما می خواد که کل اطلاعات مربوط به انبار بزرگی با هزاران سند ثبت شده در اون انبار را پاک کنید تا اون داده ها اصلا روی سرور موجود نباشند ! شما هم باید بگید چشم .
• یا مثال دیگه ، گاهی اوقات نیاز دارید که بدون حذف اطلاعات موجود در جداول پایه و پیکربندی پایگاه داده یک نرم افزار و با حذف اطلاعات جداولی که transaction ها را نگه می دارند ( صد البته این جداول حجم اصلی پایگاه داده را تشکیل می دن ) به یک کپی ساده و کم حجم برسید تا بتونید یه پایگاه داده دوم را آماده کنید که اطلاعات پیکربندی را داره اما داده خاصی از تراکنشها برای گزارشگیری در اون وجود نداره ، بعد می تونید با اون پایگاه داده دوم یه شعبه ، یک شرکت جدید ، یک محیط نرم افزاری آموزشی یا ... را راه بیاندازید.

نخستین قدم در اجرای اینکار اینه که از پایگاه داده مورد نظر Backup تهیه کنید . ( توصیه اکید می کنم خودتون شخصا فایل پشتیبان را تهیه کنید و از صحت فایل پشتیبان اطمینان پیدا کنید و اینکار را به عهده کس دیگه ای نگذارید. )

در قدم دوم کارشناسان با توجه به شناختی که از ERD مربوطه روی سرور پایگاه داده دارن ، جداول ( tables ) خاصی را نشان می کنند و جدول به جدول شروع به حذف رکوردها می کنند.

اینجا یه توضیح لازمه : Microsoft SQL بانک اطلاعات رابطه ای است و رکوردهای جداول مختلف با کلیدهای خارجی (Foreign Key) با هم در ارتباط هستند و به واسطه این کلیدهای خارجی بین رکوردهای جداول مختلف ، رابطه پدر و فرزندی وجود داره و بدلایل فنی درصد بالایی از این کلیدهای خارجی در تنظیمات خودشون گزینه ی حذف آبشاری (Cascade Delete) را ندارند یعنی اگه شما بخواهید رکوردهای جدول پدر را حذف کنید ، رکوردهای مربوط در جدول فرزند خودکار حذف نمی شن و با پیام خطا مواجه می شید. SQL در پیام خطای خودش به شما اعلام می کنه به دلیل وجود کلید خارجی با نام فلان از جدول بهمان نمی شه داده ها را پاک کرد .

به همین خاطر باید در گام نخست رکوردهای جدول فرزند را پاک کرد و در قدم های بعدی رکوردهای جدول پدر را . البته اگر تنها پای یکی دوتا کلید خارجی وسط بود، می شد قضیه را با تغییر موقت اون کلید حل کرد ، اما وقتی صدها کلید خارجی در دیتابیس وجود داشته باشه به ریسک اعمال این همه تغییر به دیتابیس نمی ارزه چرا که بعد از اعمال تغییرات و حذف داده ها باید دوباره این کلیدهای خارجی را به حالت اول برگردونید و امکان اشتباه وجود داره.

خب ، برای انجام اینکار کارشناسان با شناختی که از جداول بانک اطلاعاتی و ERD دارن می دونن که در ابتدا باید کدام جداول را پاکسازی کنند. اما در اجرای گام دوم با دو تا مشکل مواجه می شن :

• اول اینکه حجم دیتابیس با توجه به تعداد رکورد های حذف شده خیلی خیلی بزرگ می شه. یعنی هرچی رکورد بیشتری را پاک می کنید دیتابیس بزرگتر می شه . چرا واقعا ؟! ما که داریم داده ها را پاک می کنیم پس چرا حجم دیتابیس بزرگ می شه ؟! خب به خواندن ادامه بدید تا دلیلش را متوجه بشید.
• مشکل دومی که بوجود می آد ، کند شدن سرور هست . در حالت عادی کارفرما سرور دیگه ای نداره که در اختیار شما بگذاره ، من و شما هم مجبوریم از دیتابیس اصلی یک کپی تهیه کنیم و اون را با نام دیگه ای روی سرور برگردونیم تا عملیات پاکسازی ! را روی این پایگاه داده دوم انجام بدیم . از آنجا که هم دیتابیس اصلی و هم اون دیتابیسی که شما در حال حذف اطلاعات اون هستید روی یه سرور قرار دارن ، منابع سرور تقسیم می شه و سرور کند خواهد شد و بزودی فریاد اعتراض کاربران نرم افزار را خواهید شنید.

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

وقتی با استفاده از فرمان Delete رکورد های یک جدول را پاک می کنید فضای آزاد شده به سیستم عامل بر نمی گرده ، بلکه در صورتیکه یک یا تعداد بیشتری از Page های پایگاه داده که فضای اونها برای ذخیره سازی اطلاعات رکورد های پاک شده مصرف شده بود ، آزاد بشن ؛ SQL Server این Page ها را به " فضای آزاد دیتابیس" منتقل می کنه ( اگه جدول را Rebuild کنیم ) و در نتیجه مجموعه اندازه فایلهای پایگاه داده تغییری نخواهد کرد و تنها پس از Shrink کردن دیتابیس فضای آزاد به سیستم عامل باز خواهد گشت .

اما دلیل بزرگ شدن دیتابیس پس از حذف تعداد زیادی از رکوردها اینه که در SQL دستور Delete دستوری است که بطور کامل LOG می شه و با حذف رکوردها و با مد نظر قرار دادن پاراگراف بالا ، در حالیکه عملا حجم فایلهای داده (MDF ,NDF ,…) دست نخورده باقی می مونه ؛ به جهت ثبت و ضبط عملیات حذف رکوردها، اندازه فایل یا فایلهای LOG بزرگ و بزرگتر می شه . (باز هم Shrink اینجا می تونه به داد ما برسه یا اینکه Recovery Model را تغییر بدیم و بعد لاگ فایل را shrink کنیم یا فایل log را حذف کنیم و دوباره بسازیم و... بعضی وقتها همه این کارها را می کنید اما "لاگ فایل لعنتی" سرسختانه بدون تغییر و دست نخورده سر جاش می مونه و سردرد می گیرید ! )

به سه تا عکس بعدی دقت کنید ، توی این تصاویر حجم فایلهای دیتابیس AdventureWorks در حالت عادی ، پس از حذف تعدادی رکورد و بعد از Shrink کردن اون مشخص هست .

خب اگر بخواهیم کل اطلاعات یک جدول را پاک کنیم ، به جای استفاده از دستور Delete می تونیم از دستور Truncate table استفاده کنیم :

TRUNCATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }

اگه پیش از این برای حذف داده ها در یه جدول لحظات کسل کننده ای را پای کنسول / سیستم منتظر مانده باشید ، دستور truncate براتون مثل یه معجزه می مونه !!! سریع و با ایجاد حجم کمی اطلاعات Log ، کل رکوردهای جدول را برای شما پاک می کنه .

منتها این دستور برای بکارگیری در این مورد یه مشکل خاص داره ، اگه به هنگام پاک کردن اطلاعت جدول به مشکل کلید خارجی بخوره ( یعنی رکوردهای این جدول ، پدر رکوردهای جدول دیگه ای باشند ) ، در پیام خطایی که به شما نشون می ده نام جدول یا نام FK ای که به این جدول کلید خارجی داره و دارای رکورد هست را نمی آره ! ( به خاطر اینکه این دستور یه دستور DDL هست نه DML ) و خودتون باید زحمت بکشید و جدول فرزند را با استفاده از sp_Fkey یا به کمک دیتا دیاگرام Management Studio یا ... پیدا کنید . (یا شاید هم برگردید سراغ دستور Delete تا در پیام خطاش به شما بگه که مشکل کجاست ! )

اما بابت کند شدن سرور ، هیچ راهی نیست ☹ باید با بقیه کاربرایی که مشغول کار با سرور هستند کنار اومد. یه راه برای راضی نگه داشتن اونها اینه که داده ها را مرحله به مرحله پاک کنید . مثلا یک دهم رکوردهای یه جدول بزرگ را پاک کنید تا سرور بتونه transaction های سایر کاربران را هم جلو ببره .

حالا چطوری یک دهم داده های یه جدول حجیم را پاک کنیم ؟ اولین راهی که به ذهن می رسه اینه که با توجه به مقدار کمینه و بیشینه کلید اصلی جدول ، شرط دستور Delete را طوری بنویسیم که حدود یک دهم رکوردها پاک بشن . به عنوان نمونه اگه کمینه کلید اصلی با نام ID جدولtable _name برابر 1000 و بیشینه 1000000 باشه بنویسیم :

Delete from table_name where id>1 and id<100000

اما راه بهتر و ساده تری هم هست :

Delete top (10) percent from table_name

اس کیو ال سرور بدون رعایت هیچ ضابطه ای در انتخاب رکوردها برای حذف شدن ، 10 درصد رکوردهای جدول را پاک می کنه . بعد از اجرای دستور ، شما می تونید کمی صبر کنید تا سرور سایر تراکنش هاش را انجام بده بعد دوباره 10 تا 15 درصد رکوردها را پاک کنید. این یکی از کاربردهای عبارت Top در دستور Delete هست.

تموم شد ، امیدوارم براتون مفید باشه و یه نکته دیگه ، در صدد ارائه مطلب آموزشی نیستم . یکسری تجربیاتم را با شما به اشتراک گذاشتم به امید اینکه براتون مفید باشه .