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

تفاوت بین دستورات 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 در SQL Server

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

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

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

sp_help '[Employee]'

دستور Delete در SQL Server

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

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

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

DBCC CHECKIDENT('Employee', RESEED,500)

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

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

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

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

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

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

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

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

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

Rollback transaction

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

دستور Truncate در SQL Server

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

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

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

مثال ۵) حذف تمام ردیف های جدول 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 در SQL Server

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

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

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

پس از تکمیل دستور، بررسی کنید که هیچ رکوردی در جدول وجود ندارد.برای این منظور با دستورات select * کلیه رکوردهای جدول را فراخوانی می کنیمدستور Truncate در SQL Serverاکنون دستور Rollback Tran را صادر کرده و رکوردهای جدول را بررسی کنید. ما داده های خود را دوباره در جدول دریافت می کنیم.دستور Truncate در SQL Serverاین نشان می‌دهد که می‌توانیم 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 باید مراقب باشید زیرا تمام ردیف های جدول را حذف می کند

منبع مقاله

https://www.sqlshack.com/difference-between-sql-truncate-and-sql-delete-statements-in-sql-server

 

 

برای بدست آوردن اطلاعات بیش‌تر در مورد دیگر دستورات SQL ، به مقاله زیر مراجعه کنید.
 
دستورهای SQL Server

چه رتبه ای می‌دهید؟

میانگین ۳.۴ / ۵. از مجموع ۵

اولین نفر باش

title sign
دانلود مقاله
تفاوت بین دستورات Truncate و Delete در SQL Server
فرمت PDF
9 صفحه
حجم 1 مگابایت
دانلود مقاله
title sign
معرفی نویسنده
تیم فنی نیک آموز
مقالات
247 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
تیم فنی نیک آموز
پروفایل نویسنده
title sign
معرفی محصول
title sign
دیدگاه کاربران

هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link
جشنواره عیدآموز نیک آموز، سال جدید رو با قدرت شروع کن
مشاهده تخفیف ها
close-image