Delete-Truncate-SQL Server-حذف رکوردها-عملکرد SQL-تفاوت دستورات SQL-اسکیوال-اسکیوال سرور-حذف رکوردها در SQL Server-Delete VS Truncate-Commit

در این مطلب، تفاوت‌های دستورات Delete و Truncate را بررسی می‌کنیم. هر دو دستور برای حذف رکوردها در SQL Server استفاده می‌شوند، اما تفاوت‌های قابل توجهی دارند.

دستور Delete

  1. حذف رکوردها به صورت جداگانه: دستور Delete تمام رکوردها یا بخشی از رکوردها را به صورت جداگانه حذف می‌کند. (رکوردها تا زمان Commit تراکنش با علامت حذف‌شده مشخص می‌شوند)
  2. ثبت اطلاعات رکوردهای حذف‌شده: اطلاعات رکوردهای حذف‌شده در فایل تراکنش ثبت می‌شود.
  3. فعال‌سازی Delete Trigger: پس از حذف رکوردها، Delete Trigger فعال می‌شود.
  4. تغییر متادیتاهای ایندکس‌ها: متادیتاهای Clustered Index یا Heap تغییر می‌کند.
  5. عدم تغییر مقدار Identity: مقدار Identity تغییر نمی‌کند.

دستور Truncate

  1. علامت‌گذاری Data Pageها: دستور Truncate تمامی Data Pageهای جدول یا ایندکس را با علامت حذف‌ شده مشخص می‌کند تا زمان Commit تراکنش.
  2. ثبت شماره‌های FileID و PageID: در هنگام علامت‌گذاری، شماره FileID و PageID در فایل تراکنش ثبت می‌شود.
  3. عدم فعال‌سازی Delete Trigger: پس از حذف Data Pageها، Delete Trigger فعال نمی‌شود.
  4. تغییر متادیتاهای ایندکس‌ها: متادیتاهای Clustered Index یا Heap تغییر می‌کند.
  5. صفر شدن مقدار Identity: مقدار Identity به صفر می‌رسد.

مقایسه دستور DELETE و TRUNCATE در SQL Server

در مقایسه عملکرد این دو دستور، می‌توان گفت که سرعت اجرای دستور Truncate به مراتب بیشتر از Delete است. این به دلیل نحوه علامت‌گذاری و حذف Data Pageها در دستور Truncate است که باعث کاهش زمان اجرا و افزایش کارایی می‌شود.

ویژگیDELETETRUNCATE
نوع عملیاتDML (Data Manipulation Language)DDL (Data Definition Language)
نحوه حذفحذف ردیف به ردیفعلامت‌گذاری کل Data Pageها
ثبت در لاگ تراکنشبله، به‌صورت کامل (سطر به سطر)بله، فقط FileID و PageID
امکان فیلتر (WHERE)✅ دارد❌ ندارد
سرعت اجراکندترسریع‌تر
Trigger فعال می‌شود؟✅ بله❌ خیر
مقدار Identityتغییر نمی‌کندReset می‌شود (به مقدار اولیه)
میزان مصرف لاگ تراکنشزیادکم
امکان Rollback✅ دارد✅ دارد
کاربرد اصلیحذف بخشی از داده‌ها یا کنترل دقیقپاک‌سازی کامل جدول

نکات مهم درباره DELETE و TRUNCATE

  1. اگر نیاز دارید فقط بخشی از داده‌ها رو حذف کنید (مثلاً رکوردهای یک ماه خاص)، باید از DELETE استفاده کنید.

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

  3. به خاطر Reset شدن Identity در TRUNCATE، اگه نمی‌خوای شمارنده ستون Identity صفر بشه باید بعدش دوباره با DBCC CHECKIDENT تنظیمش کنی.

  4. هر دو دستور قابل Rollback هستن (تا زمانی که تراکنش Commit نشده باشه).

مثال

کد زیر یک جدول با مقداری رکورد برای نمایش این موضوع می‌سازد:

-- کد ایجاد جدول و تراکنش نمونه
CREATE TABLE ExampleTable (
    ID INT IDENTITY(1,1),
    Data NVARCHAR(100)
);

INSERT INTO ExampleTable (Data)
VALUES ('Record1'), ('Record2'), ('Record3'), ('Record4'), ('Record5');

حال عکس‌العمل SQL Server در مقابل دستور Delete را مشاهده کنید:

-- کد نمونه برای دستور Delete
BEGIN TRANSACTION;
DELETE FROM ExampleTable WHERE ID = 1;
-- بررسی وضعیت قفل‌ها و Data Pageها
SELECT * FROM sys.dm_tran_locks WHERE resource_associated_entity_id = OBJECT_ID('ExampleTable');
COMMIT TRANSACTION;

SQL Server جدول را با نوع قفل Exclusive قفل می‌کند، که تنها با استفاده از دستور NOLOCK می‌توان به داده‌ها دسترسی داشت. حالا یک‌بار دیگر جدول را پر از داده می‌کنیم و دستور Truncate را اجرا می‌کنیم:

-- کد نمونه برای دستور Truncate
TRUNCATE TABLE ExampleTable;
-- بررسی وضعیت قفل‌ها و Data Pageها
SELECT * FROM sys.dm_tran_locks WHERE resource_associated_entity_id = OBJECT_ID('ExampleTable');

مثال تکمیلی برای Reset نشدن Identity بعد از TRUNCATE

-- بعد از اجرای TRUNCATE، Identity صفر می‌شود
TRUNCATE TABLE ExampleTable;— بازگرداندن Identity به عدد دلخواه (مثلاً ۱۰۰۰)
DBCC CHECKIDENT (‘ExampleTable’, RESEED, ۱۰۰۰);

پیشنهاد مطالعه: حذف ردیف‌های تکراری در جداول SQL Server

نتیجه گیری

در نهایت، دستور Truncate قفلی از نوع Sch-M اعمال می‌کند که اجازه نوشتن و خواندن به تراکنش‌های دیگر را نمی‌دهد. به همین دلیل، SQL Server دستور Truncate را به عنوان DDL در فایل تراکنش ذخیره می‌کند.

FAQ (پرسش‌های متداول)

۱. آیا دستور TRUNCATE قابل Rollback است؟
بله، اگر داخل یک تراکنش اجرا شود و قبل از Commit کردن Rollback کنید، داده‌ها برمی‌گردند.

۲. آیا می‌توان روی جدول دارای Foreign Key دستور TRUNCATE زد؟
خیر، اگر جدول با کلید خارجی به جدول دیگری مرتبط باشد، باید ابتدا Constraintها را غیرفعال کنید یا از DELETE استفاده کنید.

۳. کدام دستور سریع‌تر است؟ DELETE یا TRUNCATE؟
TRUNCATE بسیار سریع‌تر است چون رکورد به رکورد لاگ نمی‌نویسد و فقط Data Pageها را علامت‌گذاری می‌کند.

۴. بعد از TRUNCATE ستون Identity از کجا شروع می‌شود؟
مقدار Identity به عدد اولیه (Seed) برمی‌گردد. می‌توانید با دستور DBCC CHECKIDENT آن را تغییر دهید.

۵. اگر بخواهم فقط بخشی از داده‌ها را پاک کنم، کدام دستور مناسب‌تر است؟
باید از DELETE استفاده کنید، چون TRUNCATE امکان استفاده از شرط (WHERE) را ندارد.

ارتباط و مشاوره

برای اطلاعات بیشتر و مشاوره می‌توانید از طریق زیر با ما در ارتباط باشید:

روزبه امیرعصامی

SQL Server

اسکیوال سرورپایگاه دادهطراحی دیتابیس

Xلینکدینواتساپتلگرامایمیل

نوشته های مرتبط

نظری داده نشده

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *