تفاوت DELETE و TRUNCATE در SQL Server راهنمای حذف داده‌ها با کارایی بالا

DELETE در مقابل TRUNCATE در SQL Server: راهنمای جامع حذف داده‌ها با کارایی بالا

در دنیای مدیریت پایگاه داده، دو دستور رایج برای حذف رکوردها از یک جدول وجود دارد: DELETE و TRUNCATE TABLE. هر دو عملیات حذف داده‌ها را انجام می‌دهند، اما نحوه کار و تأثیر آن‌ها بر روی عملکرد و تراکنش‌های پایگاه داده به طور قابل توجهی متفاوت است. درک این تفاوت‌ها برای هر توسعه‌دهنده و مدیر پایگاه داده SQL Server ضروری است تا بتواند بهترین ابزار را برای نیازهای خاص خود در مدیریت داده‌ها انتخاب کند.

دستور DELETE

دستور DELETE برای حذف یک یا چند رکورد از یک جدول استفاده می‌شود. این دستور به شما امکان می‌دهد تا با استفاده از بند WHERE، ردیف‌های خاصی را حذف کنید. هر ردیف حذف شده به صورت جداگانه در لاگ تراکنش (Transaction Log) ثبت می‌شود و عملیات آن قابل بازگردانی (Rollback) است.

نحوه استفاده از DELET

ساختار کلی دستور DELETE برای حذف داده‌ها به شکل زیر است:

DELETE FROM TableName WHERE Condition;

در اینجا، TableName نام جدولی است که می‌خواهید از آن ردیف حذف کنید و Condition شرطی است که مشخص می‌کند کدام ردیف‌ها باید حذف شوند. اگر بند WHERE را حذف کنید، تمام ردیف‌های جدول حذف خواهند شد.

مثال‌هایی از دستور DELETE

برای حذف مشتری با CustomerID = 1 از جدول Customers، از دستور زیر استفاده می‌کنیم:

DELETE FROM Customers WHERE CustomerID = 1;

برای حذف تمام سفارش‌ها با OrderDate قبل از تاریخ مشخص، مثلاً سال 2023، دستور زیر را اجرا می‌کنیم:

DELETE FROM Orders WHERE OrderDate < '2023-01-01';

و برای حذف تمامی ردیف‌ها از جدول TempTable بدون هیچ شرطی، به این صورت عمل می‌کنیم:

DELETE FROM TempTable;

دستور TRUNCATE TABLE

دستور TRUNCATE TABLE برای حذف سریع و کامل تمام ردیف‌ها از یک جدول استفاده می‌شود. این دستور نمی‌تواند با بند WHERE همراه باشد و همیشه تمام ردیف‌ها را حذف می‌کند. TRUNCATE TABLE یک عملیات حداقل لاگ‌نشده (minimal logging) است که به سرعت فضای تخصیص یافته به داده‌ها را آزاد می‌کند و به همین دلیل معمولاً بسیار سریع‌تر از DELETE (بدون بند WHERE) عمل می‌کند و برای بهینه‌سازی SQL در حذف حجم زیاد داده بسیار مناسب است.

نحوه استفاده از TRUNCATE TABLE

ساختار کلی دستور TRUNCATE TABLE به سادگی به شکل زیر است:

TRUNCATE TABLE TableName;

در اینجا، TableName نام جدولی است که تمام ردیف‌های آن حذف خواهند شد. این دستور بلافاصله شناسه هویت (Identity) را به مقدار اولیه خود بازنشانی می‌کند.

مثال‌هایی از دستور TRUNCATE TABLE

برای حذف تمامی ردیف‌ها از جدول LogEntries با استفاده از TRUNCATE TABLE، دستور زیر را اجرا می‌کنیم:

TRUNCATE TABLE LogEntries;

این دستور به سرعت تمام داده‌ها را از جدول حذف کرده و هر ستون Identity را به مقدار اولیه (معمولاً 1) بازنشانی می‌کند.

تفاوت‌های کلیدی بین DELETE و TRUNCATE TABLE

درک تفاوت‌های اساسی بین DELETE و TRUNCATE TABLE برای انتخاب صحیح در سناریوهای مختلف حذف داده‌ها در SQL Server حیاتی است. این دو دستور، علی‌رغم شباهت در هدف نهایی، از نظر عملکرد، نحوه برخورد با تراکنش‌ها، و امکانات، تفاوت‌های چشمگیری دارند:

• قابلیت شرطی:

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

• ثبت در لاگ تراکنش (Transaction Logging):

  DELETE: یک عملیات لاگ‌نشده (row-by-row logging) است. هر ردیف حذف شده به صورت جداگانه در لاگ تراکنش ثبت می‌شود. این بدان معناست که DELETE می‌تواند Rollback (بازگردانی) شود.
  TRUNCATE TABLE: یک عملیات حداقل لاگ‌نشده (minimal logging) است. فقط صفحات داده‌ای که آزاد می‌شوند در لاگ تراکنش ثبت می‌شوند، نه هر ردیف به صورت جداگانه. به همین دلیل، TRUNCATE TABLE بسیار سریع‌تر است اما قابل Rollback نیست.

• سرعت و کارایی:

  DELETE: معمولاً کندتر از TRUNCATE TABLE است، به خصوص برای جداول بزرگ، زیرا هر ردیف را به صورت جداگانه پردازش و لاگ می‌کند.
  TRUNCATE TABLE: بسیار سریع‌تر از DELETE است، زیرا صفحات داده را به طور کامل آزاد می‌کند و لاگ‌نویسی حداقل دارد.

• بازگردانی (Rollback):

  DELETE: عملیات DELETE را می‌توان با استفاده از تراکنش‌ها (transactions) بازگرداند.
  TRUNCATE TABLE: عملیات TRUNCATE TABLE به طور کلی قابل Rollback نیست.

• بازنشانی IDENTITY:

  DELETE: مقدار ستون IDENTITY را بازنشانی نمی‌کند. شماره‌گذاری از آخرین مقدار استفاده شده ادامه می‌یابد.
  TRUNCATE TABLE: مقدار ستون IDENTITY را به مقدار اولیه آن (معمولاً 1) بازنشانی می‌کند.

• تراکنش‌ها و قفل‌ها:

  DELETE: هر ردیف را قفل می‌کند (row-level locks).
  TRUNCATE TABLE: یک قفل انحصاری (exclusive lock) بر روی جدول ایجاد می‌کند و تمام صفحات را آزاد می‌کند.

• فراخوانی تریگرها (Triggers):

  DELETE: تریگرهای ON DELETE را فراخوانی می‌کند.
  TRUNCATE TABLE: تریگرهای ON DELETE را فراخوانی نمی‌کند.

• محدودیت‌های FOREIGN KEY:

  DELETE: در صورتی که رکوردها در یک جدول دیگر توسط FOREIGN KEY ارجاع داده شده باشند، با توجه به تنظیمات ON DELETE CASCADE یا سایر قوانین، ممکن است حذف شود یا خطا دهد.
  TRUNCATE TABLE: در صورتی که جدول مورد نظر توسط یک FOREIGN KEY از جدولی دیگر ارجاع داده شده باشد، اجرا نمی‌شود و خطا می‌دهد. برای استفاده از آن، باید محدودیت FOREIGN KEY را موقتاً غیرفعال کنید یا قبل از آن، جدول وابسته را خالی کنید.

چه زمانی از کدام دستور برای حذف داده‌ها استفاده کنیم؟

انتخاب بین DELETE و TRUNCATE TABLE به نیازهای خاص شما بستگی دارد و نقش مهمی در کارایی SQL ایفا می‌کند:

• از DELETE استفاده کنید زمانی که:

  • نیاز به حذف ردیف‌های خاصی از جدول دارید (با استفاده از بند WHERE).
  • می‌خواهید عملیات حذف قابل بازگردانی (Rollback) باشد.
  • جدول شما دارای ستون IDENTITY است و نمی‌خواهید شماره‌گذاری آن بازنشانی شود.
  • تریگرهای ON DELETE باید فعال شوند.
  • جدول شما توسط FOREIGN KEY به جدول دیگری ارجاع داده شده است و نمی‌توانید آن را غیرفعال کنید.

• از TRUNCATE TABLE استفاده کنید زمانی که:

  • قصد دارید تمام ردیف‌ها را از یک جدول حذف کنید.
  • نیازی به Rollback کردن عملیات ندارید و می‌خواهید حذف با حداکثر سرعت انجام شود.
  • می‌خواهید ستون IDENTITY به مقدار اولیه خود بازنشانی شود.
  • کارایی بالا برای حذف حجم زیادی از داده‌ها اولویت دارد و می‌خواهید از بهینه‌سازی حذف در SQL Server بهره‌مند شوید.
  • می‌توانید محدودیت‌های FOREIGN KEY را موقتاً مدیریت یا غیرفعال کنید.