DELETE و Truncate در SQL

در این مقاله تفاوت‌های بین دستورات DELETE و TRUNCATE در SQL به‌صورت عمقی و سیستمی شامل جنبه‌های مختلفی می‌شود:

1. عملکرد:

• DELETE:

  • عملکرد: این دستور ردیف‌های مشخص شده را از جدول حذف می‌کند، معمولاً بر اساس شرطی که در دستور مشخص شده است.

  کد sql

• DELETE FROM Employees WHERE Age > 30; 

  • سیستمی: هر ردیف به طور جداگانه حذف شده و حذف هر ردیف به صورت انفرادی ثبت می‌شود. به همین دلیل، عملکرد DELETE کندتر است.

• TRUNCATE:

  • عملکرد: این دستور تمامی ردیف‌ها را از جدول حذف می‌کند، اما ساختار جدول و همه ایندکس‌ها و محدودیت‌ها را حفظ می‌کند.

  کد sql

• TRUNCATE TABLE Employees; 

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

2. تراکنش‌ها:

• DELETE:

  • تراکنش‌ها: عملیات DELETE می‌تواند به عنوان بخشی از یک تراکنش باشد و می‌تواند تحت تراکنش‌های ROLLBACK و COMMIT قرار بگیرد.

  sql

• BEGIN TRANSACTION;
  DELETE FROM Employees WHERE Age > 30;
  ROLLBACK TRANSACTION;

  • سیستمی: هر حذف ردیف به صورت انفرادی وارد لاگ می‌شود، بنابراین می‌توان ردیف‌های خاصی را بازگرداند.

• TRUNCATE:

  • تراکنش‌ها: TRUNCATE نمی‌تواند به صورت جزیی بازگردانده شود، زیرا همه ردیف‌ها به طور همزمان حذف می‌شوند. اما کل عملیات TRUNCATE می‌تواند در یک تراکنش قرار بگیرد و ROLLBACK شود.

  sql

• BEGIN TRANSACTION;
  TRUNCATE TABLE Employees;
  ROLLBACK TRANSACTION;

  • سیستمی: از لحاظ سیستمی، در لاگ تراکنش تنها صفحاتی که بازنشانی شده‌اند، ثبت می‌شوند.

3. محرک‌ها (Triggers):

• DELETE:

  • محرک‌ها: محرک‌های BEFORE DELETE و AFTER DELETE فعال می‌شوند و برای هر ردیف که حذف می‌شود اجرا می‌شوند.

  sql

• CREATE TRIGGER trgDelete
  ON Employees
  AFTER DELETE
  AS
  BEGIN
      PRINT 'Record deleted';
  END;

• TRUNCATE:

  • محرک‌ها: محرک‌ها فعال نمی‌شوند، زیرا TRUNCATE ردیف‌ها را به صورت توده‌ای حذف می‌کند.

4. شمارش ردیف‌ها (Row Count):

• DELETE:

  • شمارش ردیف‌ها: دستور DELETE تعداد ردیف‌های حذف شده را بازمی‌گرداند.

  sql

• DECLARE @DeletedRows INT;
  DELETE FROM Employees WHERE Age > 30;
  SET @DeletedRows = @@ROWCOUNT;
  PRINT @DeletedRows;

• TRUNCATE:

  • شمارش ردیف‌ها: دستور TRUNCATE هیچ شمارش ردیفی را بازنمی‌گرداند.

نتیجه‌گیری:

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

بسیار خوب! بیایید به عمق بیشتری در مورد تفاوت‌های سیستمی DELETE و TRUNCATE بپردازیم:

1. مکانیزم حذف داده‌ها:

• DELETE:

  • عملیات ردیفی: DELETE هر رکورد را به صورت جداگانه حذف می‌کند. برای هر ردیف که حذف می‌شود، یک ورودی در لاگ تراکنش ایجاد می‌شود.

  • جدول‌های Logging: هر عملیات DELETE باید در جدول لاگ ثبت شود تا قابلیت بازگرداندن یا ROLLBACK وجود داشته باشد.

  • اثر جانبی: این می‌تواند بسیار کند باشد به خصوص زمانی که تعداد ردیف‌ها زیاد باشد زیرا هر ردیف باید به صورت جداگانه پردازش شود.

• TRUNCATE:

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

  • جدول‌های Logging: در TRUNCATE تنها تغییرات در متا دیتا لاگ می‌شود و ردیف‌های جداگانه ثبت نمی‌شوند.

  • اثر جانبی: به دلیل آزاد کردن صفحات داده، عملیات TRUNCATE معمولاً کمتر از DELETE زمان می‌برد و منابع کمتری مصرف می‌کند.

2. قفل گذاری:

• DELETE:

  • قفل ردیفی: DELETE قفل‌های انحصاری بر روی ردیف‌هایی که حذف می‌شوند، اعمال می‌کند.

  • تاثیر بر کارایی: اعمال قفل‌های متعدد می‌تواند به کاهش کارایی سیستم منجر شود، به خصوص در جداول بزرگ.

• TRUNCATE:

  • قفل جدول: TRUNCATE یک قفل انحصاری بر روی کل جدول اعمال می‌کند. این باعث می‌شود که هیچ عملیات خواندن یا نوشتن دیگری نتواند در حین اجرای TRUNCATE انجام شود.

  • تاثیر بر کارایی: قفل‌گذاری بر روی کل جدول باعث می‌شود که عملیات سریع‌تر انجام شود، اما در صورت نیاز به دسترسی همزمان به داده‌ها، ممکن است باعث ایجاد تاخیر شود.

3. محرک‌ها (Triggers):

• DELETE:

  • پشتیبانی از محرک‌ها: DELETE باعث فعال شدن محرک‌های (Triggers) BEFORE DELETE و AFTER DELETE می‌شود. این امر به کاربران اجازه می‌دهد تا عملیات خاصی قبل و بعد از حذف ردیف‌ها انجام دهند.

  • کدینگ محرک‌ها: اجرای محرک‌ها می‌تواند به پیچیدگی‌های بیشتری در زمان و منابع منجر شود.

• TRUNCATE:

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

4. مدیریت فضا و عملکرد:

• DELETE:

  • مدیریت فضا: فضای آزاد شده توسط DELETE معمولاً بلافاصله به سیستم فایل باز نمی‌گردد و باید عملیات فشرده‌سازی (Shrink) اجرا شود.

  • آمار و شاخص‌ها: هر عملیات DELETE می‌تواند بر آمارهای (Statistics) و شاخص‌های (Indexes) جدول تاثیر بگذارد.

• TRUNCATE:

  • مدیریت فضا: فضای آزاد شده توسط TRUNCATE بلافاصله به سیستم فایل بازگردانده می‌شود.

  • آمار و شاخص‌ها: TRUNCATE آمارهای جدول را به‌روزرسانی کرده و شاخص‌ها را دوباره محاسبه می‌کند، بنابراین می‌تواند تاثیر مثبتی بر عملکرد جداول بزرگ داشته باشد.

5. کاربرد‌ها:

• DELETE:

  • استفاده در تراکنش‌ها: DELETE به دلیل قابلیت استفاده در تراکنش‌ها و امکان بازگشت (ROLLBACK)، معمولاً در عملیات‌هایی که نیاز به دقت بیشتری دارند، استفاده می‌شود.

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

• TRUNCATE:

  • پاکسازی کامل جداول: TRUNCATE معمولاً برای پاکسازی کامل جداول بدون نیاز به حذف ساختار جدول استفاده می‌شود.

  • سرعت بالا: در مواقعی که نیاز به حذف تمامی ردیف‌ها به سرعت باشد، TRUNCATE به دلیل سرعت بالا و کاهش مصرف منابع، انتخاب بهتری است.

نتیجه‌گیری:

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

به بحث جذاب و تخصصی رسیدیم! ببین، دستور TRUNCATE در SQL Server فرآیند آزادسازی صفحات داده‌ای را به صورت خاصی انجام می‌دهد. اجازه بده به عمق بیشتری بپردازیم:

مکانیزم آزادسازی صفحات توسط TRUNCATE:

1. آزادسازی صفحات داده:

   • در دیتابیس‌ها، جداول بر روی صفحات داده‌ای ذخیره می‌شوند. هر صفحه معمولاً ۸ کیلوبایت است.

   • زمانی که دستور TRUNCATE اجرا می‌شود، تمام صفحات داده‌ای که به جدول تخصیص داده شده‌اند، بازنشانی می‌شوند. این کار به معنای آن است که تمامی صفحات داده‌ای به یکباره آزاد می‌شوند و برای استفاده مجدد آماده می‌گردند.

2. به‌روزرسانی سیستم فایل:

   • زمانی که صفحات داده آزاد می‌شوند، دیتابیس این صفحات را به سیستم فایل برمی‌گرداند. این آزادسازی باعث می‌شود که فضای دیسک بلافاصله در دسترس قرار گیرد.

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

3. مدیریت متادیتا:

   • SQL Server به‌روزرسانی‌هایی را در متادیتای داخلی خود انجام می‌دهد تا نشان دهد که جدول دیگر داده‌ای ندارد.

   • این تغییرات شامل به‌روزرسانی ساختارهای داده‌ای داخلی مانند تخصیص بیت‌مپ (GAM)، تخصیص بیت‌مپ توزیع (SGAM)، و غیره است که نشان می‌دهند کدام صفحات آزاد شده‌اند.

4. تأثیر بر آمارها و شاخص‌ها:

   • TRUNCATE تمامی آمارها و شاخص‌های مرتبط با جدول را به‌روزرسانی می‌کند. این می‌تواند عملکرد جستجو و بازیابی داده‌ها را بهبود بخشد.

   • چون تمام رکوردها به‌طور همزمان حذف می‌شوند، شاخص‌ها به طور خودکار به‌روزرسانی می‌شوند و نیازی به بازسازی مجدد نیست.

تفاوت با DELETE:

• در DELETE، هر رکورد به صورت جداگانه حذف می‌شود و صفحات داده‌ای که تمام رکوردهایشان حذف شده‌اند ممکن است بلافاصله آزاد نشوند.

• DELETE نیاز به ثبت تراکنش برای هر رکورد جداگانه دارد، در حالی که TRUNCATE تنها تغییرات در متادیتای صفحات داده‌ای را ثبت می‌کند.

• از لحاظ کارایی، TRUNCATE به دلیل آزادسازی صفحات به صورت دسته‌ای و کاهش تعداد عملیات‌های لاگینگ به مراتب سریع‌تر از DELETE است.

نتیجه‌گیری:

TRUNCATE روشی بسیار کارآمد برای حذف تمامی داده‌های یک جدول و آزادسازی صفحات داده‌ای به سیستم فایل است. این مکانیزم باعث می‌شود TRUNCATE برای سناریوهایی که نیاز به پاکسازی سریع داده‌ها است، انتخابی عالی باشد.

بسیار خوب، صفحات (Pages) یکی از اساسی‌ترین واحدهای ذخیره‌سازی در SQL Server هستند. هر صفحه معمولاً ۸ کیلوبایت است و انواع مختلفی از صفحات بسته به نوع داده‌های ذخیره شده وجود دارد. بیایید به جزئیات بپردازیم:

اندازه صفحه (Page Size)

• اندازه ثابت: همه صفحات در SQL Server اندازه‌ای برابر با ۸ کیلوبایت دارند. این اندازه استاندارد برای مدیریت حافظه و بهبود عملکرد بهینه است.

• ساختار صفحه: هر صفحه شامل یک سربرگ (Header) و ناحیه داده (Data Area) است.

  • سربرگ صفحه: حدود ۹۶ بایت از هر صفحه را به خود اختصاص می‌دهد و شامل اطلاعات کنترلی مانند شماره صفحه، نوع صفحه، و وضعیت صفحه است.

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

انواع صفحات (Page Types)

1. صفحات داده (Data Pages)

   • این صفحات حاوی ردیف‌های جدول هستند.

   • هر ردیف اطلاعاتی شامل طول ثابت و متغیر را می‌تواند در خود نگه دارد.

   • ساختار: [Header (96 bytes) | Row Offset Array | Data Rows]

2. صفحات ایندکس (Index Pages)

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

   • شامل صفحات برگ (Leaf) و صفحات میانی (Intermediate) هستند.

   • صفحات برگ شامل کلیدهای ایندکس و مقادیر رکوردها هستند.

   • صفحات میانی شامل فقط کلیدها برای پیمایش سریع هستند.

   • ساختار: مشابه صفحات داده، اما با تمرکز بر ایندکس‌ها.

3. صفحات تخصیص (Allocation Pages)

   • شامل انواع مختلفی مانند Global Allocation Map (GAM)، Shared Global Allocation Map (SGAM)، و Page Free Space (PFS) هستند.

   • GAM و SGAM برای مدیریت تخصیص و آزادسازی فضای دیسک استفاده می‌شوند.

   • PFS برای پیگیری فضای آزاد در صفحات استفاده می‌شود.

4. صفحات فرمت (Text/Image Pages)

   • برای ذخیره‌سازی داده‌های نوع متن یا تصویر استفاده می‌شوند که اندازه آنها بیش از ۸۰۰۰ بایت است.

   • این صفحات داده‌هایی مانند VARCHAR(MAX) یا VARBINARY(MAX) را نگه‌داری می‌کنند.

5. صفحات لاب (LOB Pages)

   • برای ذخیره‌سازی داده‌های بزرگتر از ۸۰۰۰ بایت مانند انواع XML، VARCHAR(MAX)، NVARCHAR(MAX) استفاده می‌شوند.

   • ساختار: معمولاً شامل یک pointer به صفحات اصلی که داده‌های بزرگ را نگه‌داری می‌کنند.

ساختار صفحات

1. صفحات داده:

   • سربرگ صفحه (Header): 96 بایت.

   • آرایه افست ردیف (Row Offset Array): شامل آدرس هر ردیف در داخل صفحه است.

   • داده‌ها (Data): ردیف‌های داده اصلی که در صفحه ذخیره می‌شوند.

2. صفحات ایندکس:

   • صفحات برگ (Leaf Nodes): حاوی کلیدهای ایندکس و مقادیر.

   • صفحات غیر برگ (Non-leaf Nodes): حاوی فقط کلیدها و pointers به صفحات زیرین.

3. صفحات تخصیص:

   • GAM: نشان‌دهنده تخصیص ۸ صفحه در یک extent است.

   • SGAM: نشان‌دهنده تخصیص و آزادسازی صفحات در extents مختلط.

   • PFS: پیگیری فضای آزاد در هر صفحه.

نتیجه‌گیری

صفحات (Pages) در SQL Server نقش حیاتی در سازمان‌دهی و مدیریت داده‌ها ایفا می‌کنند. اندازه ثابت صفحات به مدیریت بهینه حافظه و عملکرد بهتر دیتابیس کمک می‌کند. درک ساختار و انواع صفحات برای بهینه‌سازی و عیب‌یابی عملکرد دیتابیس ضروری است.