تفاوت بین دستورات Truncate و Delete در SQL Server

نوشته شده توسط: تیم فنی نیک آموز

تاریخ انتشار: ۳۰ بهمن ۱۴۰۰

آخرین بروزرسانی: ۲۳ آبان ۱۴۰۲

زمان مطالعه: 13 دقیقه

مقدمه

زمانی که ما نیاز به حذف داده ها از جدول SQL رابطه ای را دریافت می کنیم. می توانیم از دستور Delete و Truncate برای حذف داده ها استفاده کنیم. درک تفاوت بین این دستورات به توسعه دهندگان SQL کمک می کند تا داده های خود را به خوبی مدیریت کنند.

تفاوت بین دستور حذف و کوتاه کردن را به من بگویید.

من اکثر کاندیداها را دیده ام که با تفاوت این دستورات آشنا نیستند. این مقاله یک نمای کلی از عبارات Delete و Truncate همراه با تفاوت ها را به شما ارائه می دهد.

دستور Delete در SQL Server

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

مثال ۱) حذف تمام رکوردهای یک جدول

فرض کنید می خواهیم تمام رکوردها را از جدول Employee حذف کنیم، کوئری زیر را اجرا کنید.

DELETE FROM [SQLShackDemo].[dbo].[Employee];

مثال ۲) حذف بخشی از رکوردهای یک جدول

فرض کنید می خواهیم تمام سوابق کارمندان متعلق به یک شهر خاص سن آنتونیو را حذف کنیم. می‌توانیم این شرط را در یک عبارت Where با دستور Delete در SQLمانند شکل زیر مشخص کنیم.
پس از نوشتن کلمه delete from ابتدا نام دیتابیس و سپس نام اسکیمای و سپس نام جدول را می نویسیم و در قسمت where تنها رکوردهای را می خواهیم که شهر آن ها ‘San Antonio’باشد برای این منظور کوئری زیر را می نویسیم.

DELETE FROM [SQLShackDemo].[dbo].[Employee]
where City='San Antonio'

ما باید از مقدار رشته در گیومه های تکی استفاده کنیم. می‌توانیم مستقیماً مقدار را بدون تک کوتیشن در عبارت Where مانند EmpID درکوئری زیر مشخص کنیم.
پس از نوشتن کلمه delete from ابتدا نام دیتابیس و سپس نام اسکیمای و سپس نام جدول را می نویسیم و در قسمت where تنها رکوردهای را می خواهیم که کد پرسنلی کارمندی آن ها برابر با ۱۰۰۱ باشد.

DELETE [SQLShackDemo].[dbo].[Employee]
WHERE EmpID = 1001;

در برنامه اجرای واقعی یک عبارت Delete، می‌توانیم ببینیم که از عملگر Clustered Index Delete برای حذف یک ردیف خاص استفاده می‌کند.

مثال ۳) مثال از Delete statement, identity Values

دستور Delete رکوردها را یکی یکی حذف می کند و هر ورودی را در گزارش تراکنش ثبت می کند. این یک عبارت DML یا data manipulation languageاست.

فرض کنید با استفاده از دستور DELETE یک ردیف از جدول حذف می کنیم و آن جدول حاوی مقادیر SQL IDENTITY است. مقادیر IDENTITY در هر درج رکورد جدید ایجاد می شود. ما ممکن است یک سوال داشته باشیم –
وقتی یک رکورد را حذف می کنیم برای یک مقدار identity چه اتفاقی می افتد؟
بیایید با استفاده از یک مثال به این سناریو نگاه کنیم. در جدول Employee داده های زیر را داریم.ستون EmpID یک ستون identity است. با استفاده از دستور sp_help می‌توانیم ستون identity را در جدول بررسی کنیم.

sp_help '[Employee]'

باید EmpID 1014 را از جدول Employee حذف کنیم.
برای این منظور پس از نوشتن کلمه delete from ابتدا نام دیتابیس و سپس نام اسکیمای و سپس نام جدول را می نویسیم و در قسمت where تنها رکوردهای را می خواهیم که کد پرسنلی کارمندی آن ها برابر با ۱۰۱۴ باشد برا ی این منظور کوئری زیر را می نویسیم

DELETE [SQLShackDemo].[dbo].[Employee]
WHERE EmpID = 1014;

پس از حذف رکورد، رکورد را از جدول مشاهده کنید. در تصویر زیر می بینیم که مقدار identity 1014 در دسترس نیست زیرا آن رکورد را از جدول حذف کردیم.دستور SQL Delete مقادیر identity در جدول را بازنشانی نمی کند. ما می توانیم از DBCC CHECKIDENT برای بررسی مقدار identity فعلی استفاده کنیم و به صورت دستی یک مقدار identity جدید برای ستون identity تنظیم کنیم.
به عنوان مثال، در کوئری زیر می خواهیم مقادیر identity را به ۵۰۰ بازنشانی کنیم. برای این منظور کوئری زیر را می نویسیم.

DBCC CHECKIDENT('Employee', RESEED,500)

که پس از اجرای کوئری فوق با پیغام زیر مواجه می شویم.اگر یک رکورد جدید وارد کنیم، از مقدار identity 501 استفاده می کند.

حال در ادامه با استفاده از دستور insert-into values یک رکورد جدید به جدول اضافه می کنیم

insert into employee values('bb','bb','b','bb',1,555)

در صورتیکه روی جدول دستور select * from employee بزنیم خروجی کوئری فوق مطابق تصویر زیر می باشد

مثال ۴) بازگرداندن تراکنش با استفاده از دستور truncuation در SQL Server

ما می‌توانیم یک تراکنش را با استفاده از دستور delete برگردانیم. بیایید از یک عبارت حذف با BEGIN Transaction استفاده کنیم
برای این منظور کوئری زیر را می نویسیم

BEGIN TRANSACTION;
DELETE FROM [SQLShackDemo].[dbo].[Employee]
WHERE EmpID = 1010;

رکورد دارای کد کارمندی یا EmpId 1010 را در جدول کارمند حذف کردیم.
خروجی کوئری فوق مطابق تصویر زیر می باشد

ما می توانیم برای بازگرداندن تراکنش از دستور transactionاستفاده کنیم. این دستور rollback را اجرا کنید و رکوردهای جدول را مشاهده کنید. می‌توانیم ببینیم که رکورد حذف‌شده بازگشتی دارد و می‌توانیم آن را در جدول Employee ببینیم.

Rollback transaction

حال در ادامه با اجرای کوئری فوق می توان رکوردی از جدول Employee با EMPID یا کد پرسنلی و کارمندی ۱۰۱۰ را برگرداند.
خروجی کوئری فوق مطابق تصویر زیر می باشد.

دستور Truncate در SQL Server

Truncate یک دستور زبان تعریف داده (DDL) یا Data Defrnition Language است. تمام ردیف های جدول را حذف می کند. SQL Server داده های یک جدول را در Pages ذخیره می کند. دستور truncate با جدا کردن Pages، ردیف ها را حذف می کند. این یک ورودی برای عدم تخصیص pages در گزارش تراکنش ایجاد می کند. حذف هر ردیف را در گزارش تراکنش ثبت نمی کند.

ما نمی توانیم هیچ شرطی را در دستور Truncate تعیین کنیم. در سطح بالا، می‌توانید دستور truncateرا شبیه به دستور Delete بدون بند Where در نظر بگیرید. جدول و pages را به جای یک ردیف قفل می کند.

SQL Server برنامه اجرای واقعی یک دستور Truncate را نشان نمی دهد. در اسکرین شات زیر می توانید نقشه اجرای برآورد شده را مشاهده کنید.

مثال ۵) حذف تمام ردیف های جدول employee با استفاده از دستور truncuate

دستور truncate در SQL Server یک دستور Forward هست فقط باید نام جدول را ارسال کنید تا همه سطرها حذف شوند.

TRUNCATE TABLE [SQLShackDemo].[dbo].[Employee];

مثال ۶) دستور truncate و مقادیر identity جدول

ابتدا دستور مثال ۵ را اجرا کنید تا تمام سطرهای جدول حذف شود.

TRUNCATE TABLE [SQLShackDemo].[dbo].[Employee];

برای اجرای دستور Truncate ابتدا نام دیتابیس و سپس نام اسکیما و سپس نام جدول را می آوریم جدول الان خالی است بیایید یک رکورد جدید در جدول وارد کنیم.

INSERT into employee values('bb','bb','b','bb',1,555)

می بینید که مقدار Identity دوباره از ۱ شروع می شود که در ویژگی های جدول تعریف شده است.
خروجی کوئری فوق مطابق تصویر زیر می باشد

مثال ۷) اجرای دستور truncate با دستور roll back در SQL Server

در مثال ۴، ما برای برگرداندن تراکنش با دستور delete کاوش کرد. این یک تصور اشتباه در بین DBA ها است که ما نمی توانیم تراکنش انجام شده با دستور TRUNCATE را به عقب برگردانیم. آیا حقیقت دارد؟

BEGIN TRAN;
TRUNCATE TABLE [SQLShackDemo].[dbo].[Employee];

پس از تکمیل دستور، بررسی کنید که هیچ رکوردی در جدول وجود ندارد.برای این منظور با دستورات select * کلیه رکوردهای جدول را فراخوانی می کنیماکنون دستور Rollback Tran را صادر کرده و رکوردهای جدول را بررسی کنید. ما داده های خود را دوباره در جدول دریافت می کنیم.این نشان می‌دهد که می‌توانیم delete و همچنین دستورtruncate را که در یک تراکنش آغاز شده است، برگردانیم.
بیایید تفاوت بین دستور Delete و Truncate را در جدول زیر بررسی کنیم.

Truncate command	Delete command
دستور truncate تمام ردیف های جدول را حذف می کند. ما نمی توانیم از بند Where در این مورد استفاده کنیم.	دستور Delete برای حذف تمام یا سطرهای خاص از جدول مشخص شده با استفاده از عبارت Where مفید است
Truncate یک دستور data design language هست	Delete یک دستور data manipulate language هست
دستور truncate برای حذف تمام رکوردهای جدول قفل می شود	دستور delete روی هر سطر که قرار است از جدول حذف می شود قفل می شود
دستور truncate سریعتر از فرمان delete هست	دستور delete کندتر از فرمان truncate هست
با جدا کردن pagesی که برای ذخیره داده های جدول استفاده می شوند، تمام ردیف های جدول را حذف می کند	دستور delete تمام رکوردهای در یک زمان حذف می کند
دستور Truncate identity-به مقدار اولیه آن resetمی کند.	دستور deleteاین identityرا حفظ می کند و آن را به مقدار اولیه restنمی کند.
تمام pages داده یک جدول را اختصاص می دهد. بنابراین، تمام آمار و فضای اختصاص داده شده را نیز حذف می کند.	دستور deleteبه فضای ثبت تراکنش بیشتری نسبت به دستور truncate نیاز دارد.
دستور Truncate نمی تواند یک triggerرا فعال کند. در صورت انجام هر گونه تغییر ردیف، triggerفعال می شود. در این دستور، در SQL Server همه pages را اختصاص می دهد، بنابراین یک triggerرا فعال نمی کند.	دستور Delete می تواند یک triggerرا نیز فعال کند. حذف روی ردیف‌های جداگانه و حذف داده‌ها. بنابراین، یکtrigger را فعال می کند
دستور truncate فقط تمام ردیف های جدول را حذف می کند. این دستور column ستون ، index، constraintو اسکیما هارا حذف نمی کند.	دستور Delete سطرهای مطابق با عبارت Where را حذف می کند. همچنین column، index، constraints، اسکیماها را حذف نمی کند

نتیجه گیری

در این مقاله، دستور SQL Delete و SQL Truncate را برای حذف ردیف‌های جدول بررسی کردیم. امیدوارم این مقاله به پاسخگویی به تمام سوالات شما در رابطه با دستورات deleteو truncateدر SQL Server و همچنین درک تفاوت این دستورات کمک کند. هنگام استفاده از دستور truncate باید مراقب باشید زیرا تمام ردیف های جدول را حذف می کند