حذف داده در SQL Server با DELETE و TRUNCATE

روش‌های حذف داده در SQL Server: راهنمای جامع و بهترین شیوه‌ها

دستور DELETE در SQL برای حذف رکوردهای موجود از یک جدول استفاده می‌شود و یک دستور از نوع زبان دستکاری داده (DML) است. درک نحوه استفاده مؤثر و ایمن از DELETE برای حفظ یکپارچگی داده‌ها و عملکرد پایگاه داده ضروری است. دستورات DELETE می‌توانند بسیار قدرتمند باشند و اگر با دقت استفاده نشوند، ممکن است منجر به از دست دادن داده شوند. همیشه اطمینان حاصل کنید که از یک عبارت WHERE برای فیلتر کردن ردیف‌هایی که می‌خواهید حذف کنید، استفاده می‌کنید.

حذف داده با استفاده از دستور DELETE پایه

ساده‌ترین شکل دستور DELETE برای حذف همه ردیف‌ها از یک جدول (بدون عبارت WHERE) یا ردیف‌های خاص بر اساس یک شرط استفاده می‌شود.

برای حذف تمام ردیف‌ها از یک جدول:

DELETE FROM YourTableName;

این دستور تمام ردیف‌ها را از YourTableName حذف می‌کند اما ساختار جدول و سیدینگ (seeding) ستون‌های IDENTITY را حفظ می‌کند.

برای حذف ردیف‌های خاص که یک شرط را برآورده می‌کنند:

DELETE FROM YourTableName WHERE ColumnName = 'Value';

این رایج‌ترین و ایمن‌ترین روش برای حذف ردیف‌های خاصی است که یک شرط تعریف شده را برآورده می‌کنند.

حذف داده با استفاده از JOIN

گاهی اوقات نیاز دارید ردیف‌هایی را از یک جدول بر اساس شرایطی از جدول دیگر حذف کنید. این کار با استفاده از JOIN امکان‌پذیر است. می‌توانید از Common Table Expression (CTE) یا سینتکس مستقیم JOIN استفاده کنید.

مثال با CTE:

WITH UsersToDelete AS
(SELECT UserID FROM Users WHERE AccountStatus = 'Inactive' AND LastLogin < GETDATE() - 365)
DELETE FROM Users
WHERE UserID IN (SELECT UserID FROM UsersToDelete);

یک Common Table Expression (CTE) به تعریف یک مجموعه نتایج موقت و نام‌گذاری شده کمک می‌کند که می‌توان آن را در یک دستور SELECT، INSERT، UPDATE، DELETE یا MERGE ارجاع داد. این کار عملیات حذف پیچیده را خواناتر و قابل مدیریت‌تر می‌کند.

مثال با سینتکس JOIN مستقیم:

DELETE T1
FROM Orders T1
JOIN Customers T2 ON T1.CustomerID = T2.CustomerID
WHERE T2.CustomerStatus = 'Churned';

این روش به شما امکان می‌دهد ردیف‌هایی را از جدول هدف (T1) حذف کنید که با شرایط مشخص شده در یک جدول JOIN شده (T2) مطابقت دارند. این یک روش قدرتمند برای حذف داده‌های مرتبط در بین جداول است.

حذف داده با استفاده از Subquery

می‌توانید ردیف‌ها را بر اساس نتیجه یک Subquery (پرس و جوی فرعی) حذف کنید.

DELETE FROM Products
WHERE CategoryID IN (SELECT CategoryID FROM Categories WHERE CategoryName = 'Obsolete');

یک Subquery می‌تواند در عبارت WHERE برای فیلتر کردن ردیف‌ها جهت حذف، بر اساس مجموعه‌ای از نتایج که توسط یک پرس و جوی دیگر بازگردانده می‌شود، استفاده شود. این برای معیارهای فیلتر پیچیده مفید است.

حذف TOP N ردیف

برای حذف تعداد مشخصی از ردیف‌ها، می‌توانید از عبارت TOP استفاده کنید.

DELETE TOP (100) FROM ErrorLogs
WHERE LogDate < GETDATE() - 30
ORDER BY LogDate ASC;

عبارت TOP می‌تواند با DELETE استفاده شود تا تعداد یا درصد ردیف‌ها برای حذف مشخص شود. استفاده از یک عبارت ORDER BY برای اطمینان از نتایج قطعی (deterministic results) ضروری است، به خصوص هنگام مدیریت عملکرد یا لاگ‌ها.

حذف از جداول بزرگ (بچینگ/تکه‌تکه کردن)

برای جداول بسیار بزرگ، حذف همه ردیف‌ها به یکباره می‌تواند باعث افزایش قابل توجه لاگ تراکنش، قفل شدن (blocking) و مشکلات عملکردی شود. پردازش دسته‌ای (chunking) یک بهترین روش است.

WHILE EXISTS (SELECT 1 FROM OldData WHERE Processed = 0)
BEGIN
DELETE TOP (50000) FROM OldData WHERE Processed = 0;
WAITFOR DELAY '00:00:05'; -- Pause for 5 seconds to reduce contention
END

این حلقه به طور مکرر تعداد کمی از ردیف‌ها (یک بچ) را حذف می‌کند تا زمانی که تمام ردیف‌های مورد نظر حذف شوند. دستور WAITFOR DELAY یک مکث بین بچ‌ها اضافه می‌کند که فشار بر سیستم را کاهش داده و قفل شدن برای سایر تراکنش‌ها را به حداقل می‌رساند. این رویکرد به مدیریت اندازه لاگ تراکنش و منابع سیستم در طول عملیات حذف بزرگ کمک می‌کند.

مقایسه DELETE و TRUNCATE TABLE

انتخاب بین DELETE و TRUNCATE TABLE بستگی به نیازهای شما دارد:

• DELETE:

  یک دستور DML است.

  ردیف به ردیف حذف می‌کند (حذف منطقی).

  هر ردیف حذف شده را در لاگ ثبت می‌کند (لاگ بزرگ).

  می‌تواند دارای عبارت WHERE باشد.

  تریگرها (Triggers) فعال می‌شوند.

  ستون IDENTITY فقط با TRUNCATE ریست می‌شود.

  برای جداول بزرگ کندتر است.

• TRUNCATE TABLE:

  یک دستور DDL است.

  صفحات داده را آزاد می‌کند (حذف فیزیکی).

  حداقل ثبت لاگ را دارد (سریع).

  نمی‌تواند دارای عبارت WHERE باشد.

  تریگرها فعال نمی‌شوند.

  ستون IDENTITY را به مقدار اولیه ریست می‌کند.

  برای جداول بزرگ بسیار سریع‌تر است.

TRUNCATE TABLE YourTableName;

TRUNCATE TABLE یک دستور Data Definition Language (DDL) است که به سرعت با آزاد کردن صفحات داده، تمام ردیف‌ها را از یک جدول حذف می‌کند. این کار برای جداول بزرگ بسیار سریع‌تر از DELETE است زیرا فقط آزاد شدن صفحات را ثبت می‌کند، نه حذف ردیف‌های فردی. با این حال، در برخی سناریوها به راحتی قابل بازگردانی نیست و اجازه استفاده از عبارت WHERE را نمی‌دهد.

بهترین شیوه‌ها برای دستورات DELETE

برای اطمینان از عملیات DELETE ایمن و کارآمد، بهترین شیوه‌های زیر را رعایت کنید:

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

• تراکنش‌ها (Transactions): دستورات DELETE خود را در تراکنش‌های صریح (BEGIN TRAN, COMMIT TRAN, ROLLBACK TRAN) قرار دهید تا در صورت بروز خطا، امکان بازگردانی فراهم شود. این کار به شما کنترل بیشتری بر عملیات می‌دهد.

• عبارت WHERE: همیشه از یک عبارت WHERE برای جلوگیری از حذف تصادفی تمام داده‌ها استفاده کنید. اگر قصد حذف تمام داده‌ها را دارید، برای عملکرد بهتر، TRUNCATE TABLE را در نظر بگیرید، اما پیامدهای آن را درک کنید.

• بچینگ/تکه‌تکه کردن (Batching/Chunking): برای جداول بزرگ، داده‌ها را در بچ‌های کوچکتر حذف کنید تا از رشد لاگ تراکنش، مشکلات قفل‌شدن و تنگناهای عملکرد جلوگیری شود. این کار به مدیریت منابع سیستم کمک می‌کند.

• ایندکس‌گذاری (Indexing): اطمینان حاصل کنید که ایندکس‌های مناسب روی ستون‌های استفاده شده در عبارت WHERE وجود دارند تا عملکرد حذف بهبود یابد. ایندکس‌ها به موتور پایگاه داده کمک می‌کنند تا ردیف‌های مورد نظر را سریع‌تر پیدا کند.

• کلیدهای خارجی (Foreign Keys): از محدودیت‌های کلید خارجی آگاه باشید. حذف رکوردهای والد ممکن است مسدود شود یا ممکن است حذف آبشاری (cascading deletes) را (اگر پیکربندی شده باشد) فعال کند. تأثیر آن را درک کنید تا از خطاهای پیش‌بینی نشده جلوگیری شود.

• نظارت بر عملکرد (Performance Monitoring): عملکرد پایگاه داده (CPU، I/O، مصرف لاگ تراکنش) را در طول و پس از عملیات حذف بزرگ نظارت کنید. این کار به شما کمک می‌کند تا هرگونه مشکل عملکردی را شناسایی و رفع کنید.

• مدیریت خطا (Error Handling): مکانیزم‌های قوی مدیریت خطا را، به خصوص در اسکریپت‌های خودکار، پیاده‌سازی کنید. این اطمینان می‌دهد که هرگونه خطا در طول فرآیند حذف به درستی شناسایی و مدیریت می‌شود.