مقدمه

دستور TRUNCATE TABLE یکی از مباحث کلیدی Microsoft SQL Server در حوزه پاک‌سازی انبوه است. یادگیری درست آن فقط به حفظ Syntax محدود نمی‌شود؛ باید ترتیب منطقی پردازش، نوع داده، رفتار NULL، Unicode، Collation، اثر ایندکس، شکل Execution Plan، سطح دسترسی و شرایط اجرای هم‌زمان نیز در نظر گرفته شود. در این مقاله موضوع TRUNCATE TABLE از سطح مقدماتی آغاز می‌شود و تا نکات حرفه‌ای، خطاهای رایج، سناریوهای واقعی و روش‌های جایگزین ادامه پیدا می‌کند.

TRUNCATE TABLE با آزادسازی Pageها همه ردیف‌ها یا Partition مشخص را حذف می‌کند، WHERE ندارد، Trigger DELETE را اجرا نمی‌کند و معمولاً Identity را Reset می‌کند. در Transaction قابل Rollback است.

تعریف و نحوه عملکرد

TRUNCATE TABLE در T-SQL بخشی از چرخه پردازش Query یا تغییر داده است. SQL Server ابتدا متن Statement را Parse و Bind می‌کند، سپس بهینه‌ساز بر اساس آمار، ایندکس‌ها، Constraintها و تخمین Cardinality یک Plan انتخاب می‌کند. نتیجه واقعی می‌تواند با حجم داده، مقدار پارامتر، نسخه موتور و وضعیت آمار تغییر کند؛ بنابراین کدی که روی چند ردیف سریع است الزاماً در Production سریع یا حتی از نظر هم‌زمانی ایمن نیست.

در استفاده از دستور TRUNCATE TABLE باید Grain داده و هدف کسب‌وکار روشن باشد. Grain مشخص می‌کند هر ردیف خروجی یا هر ردیف تحت اثر چه معنایی دارد. وقتی این تعریف مبهم باشد، امکان تولید تکرار، حذف یا تغییر بیش از انتظار، شمارش اشتباه و Plan پرهزینه افزایش می‌یابد.

زمان استفاده

• وقتی نیاز مستقیم کسب‌وکار با قابلیت TRUNCATE TABLE هم‌راستا است.
• وقتی نوع داده، تعداد ردیف و ترتیب یا محدوده عملیات از قبل مشخص شده است.
• وقتی نتیجه با Query کنترلی، Constraint یا شمارش ردیف قابل اعتبارسنجی است.
• وقتی اثر آن بر Log، Lock، TempDB و ایندکس‌ها با حجم واقعی آزمایش شده است.

زمان عدم استفاده

• وقتی هدف فقط با یک میانبر ظاهری حل می‌شود ولی علت اصلی طراحی یا Join اشتباه باقی می‌ماند.
• وقتی ورودی کاربر بدون پارامترسازی یا اعتبارسنجی وارد Statement می‌شود.
• وقتی عملیات تغییر داده بدون Transaction، Backup یا راه برگشت اجرا می‌شود.
• وقتی نسخه SQL Server مقصد یا محدودیت‌های Schema بررسی نشده است.

Syntax، پارامترها و نوع خروجی

نحو عمومی

TRUNCATE TABLE dbo.StageData;

BEGIN TRANSACTION;
TRUNCATE TABLE dbo.TestData;
ROLLBACK TRANSACTION;

اجزای اصلی

نوع خروجی و اثر Statement

نوع خروجی TRUNCATE TABLE به ستون‌ها و عبارت‌های استفاده‌شده وابسته است. در Statementهای خواندنی یک Result Set ساخته می‌شود و در عملیات DML معمولاً @@ROWCOUNT تعداد ردیف‌های تحت اثر را گزارش می‌کند. هرجا OUTPUT پشتیبانی شود، می‌توان کلید یا مقدار قبل و بعد را به شکل کنترل‌شده دریافت کرد.

در محاسبات عددی باید Precision، Scale و احتمال Overflow بررسی شود. در متن، طول nvarchar و Collation اهمیت دارد. در تاریخ، نوع date یا datetime2 و مرزهای بازه باید صریح باشند. تبدیل ضمنی ممکن است هم نتیجه را تغییر دهد و هم باعث از دست رفتن Index Seek شود.

مثال‌های عملی و نتیجه نمونه

Stage

TRUNCATE TABLE dbo.ImportStage;

برای Stage بدون FK و Audit ردیفی مناسب است.

Rollback

BEGIN TRANSACTION;

TRUNCATE TABLE dbo.TestData;
SELECT COUNT(*) AS RowsAfter FROM dbo.TestData;

ROLLBACK TRANSACTION;

در SQL Server قابل Rollback است.

Identity

TRUNCATE TABLE dbo.QueueStage;

INSERT INTO dbo.QueueStage(MessageText)
VALUES (N'پیام جدید');

SELECT SCOPE_IDENTITY() AS NewIdentity;

Identity معمولاً از Seed اولیه آغاز می‌شود.

رفتار با NULL، انواع داده، Unicode و Collation

رفتار با NULL

به مقدار و NULL توجهی ندارد و تمام ردیف محدوده هدف حذف می‌شود.

NULL به معنای صفر یا رشته خالی نیست و منطق سه‌حالته SQL را ایجاد می‌کند. برای جلوگیری از نتیجه غیرمنتظره، داده‌های NULL، مقدار خالی و مقدار واقعی را در تست‌های جداگانه پوشش دهید. در محاسبات تجمعی نیز تفاوت COUNT(*) و COUNT(column) و رفتار توابع نسبت به NULL اهمیت دارد.

انواع داده

محدودیت اصلی مربوط به FK، Temporal، Replication و وابستگی Table است نه نوع ستون.

نوع پارامتر بهتر است دقیقاً با نوع و طول ستون هماهنگ باشد. پارامتر nvarchar(4000) برای ستونی با طول بسیار کوتاه یا مقایسه int با متن می‌تواند تخمین Cardinality و استفاده از ایندکس را خراب کند. CAST و CONVERT را روی مقدار ورودی انجام دهید، نه بدون ضرورت روی ستون ایندکس‌شده.

Unicode و Collation

Unicode و Collation اثری بر Deallocation ندارند.

برای ثابت فارسی از N استفاده کنید. اختلاف Collation میان Databaseها، TempDB و ستون‌های متنی ممکن است خطا یا Sort و Convert اضافه ایجاد کند. COLLATE در سطح Query راه‌حل موقت است و باید اثر آن بر Seek و ترتیب ایندکس اندازه‌گیری شود.

خطاهای رایج، محدودیت‌ها و Edge Caseها

• جدول اشتباه
• فرض غیرقابل Rollback
• نادیده گرفتن Reset Identity
• FK ارجاع‌دهنده
• انتظار Trigger DELETE

Edge Caseهای مهم TRUNCATE TABLE شامل جدول خالی، مقدار NULL، کلید تکراری، چند ردیف هم‌رتبه، مقدار بسیار بزرگ، اجرای هم‌زمان و تغییر Schema است. هرکدام می‌تواند نتیجه‌ای متفاوت از تست ساده ایجاد کند. تست باید هم روی داده مرزی و هم روی حجم نزدیک Production انجام شود.

برای Statementهای تغییردهنده، Trigger، Foreign Key، Cascade، Indexed View، Temporal Table و Replication نیز ممکن است رفتار یا محدودیت اضافه ایجاد کنند. برای Queryهای خواندنی، Row-Level Security، View و Permission می‌توانند ردیف قابل مشاهده را تغییر دهند.

Performance Considerations، Index و Execution Plan

• معمولاً سریع و کم‌Logتر از DELETE است.
• Sch-M Lock می‌تواند Blocking بسازد.
• Log همچنان لازم است.
• Partition Truncate را بررسی کنید.

نکات مربوط به Index

ایندکس مناسب برای TRUNCATE TABLE باید بر اساس Predicate، Join، ترتیب Sort و ستون‌های خروجی طراحی شود. افزودن هر ایندکس هزینه نگهداری INSERT، UPDATE و DELETE را افزایش می‌دهد؛ بنابراین ایندکس صرفاً برای زیبا شدن یک Plan ساخته نمی‌شود. Logical Reads، تعداد Lookup، اندازه Index و فراوانی اجرای Query باید هم‌زمان سنجیده شوند.

Execution Plan

در Actual Execution Plan به تفاوت Estimated Rows و Actual Rows، نوع Scan یا Seek، Sort، Hash Match، Nested Loops، Spool، Memory Grant و هشدار Spill یا Implicit Conversion توجه کنید. اختلاف بزرگ تخمین و واقعیت می‌تواند از آمار قدیمی، پارامتر حساس، Predicate هم‌بسته یا داده Skew شده ناشی شود.

SET STATISTICS IO و SET STATISTICS TIME را در محیط تست فعال کنید و Query Store را برای مقایسه Planها و Regression به کار ببرید. زمان یک اجرای گرم به‌تنهایی معیار کافی نیست؛ CPU، I/O، TempDB، Blocking، Deadlock و پایداری Plan نیز باید اندازه‌گیری شوند.

Best Practices و نکات امنیتی

• نام Schema و ستون‌ها را صریح بنویسید.
• ورودی برنامه را با sp_executesql یا پارامتر واقعی ارسال کنید.
• برای DML ابتدا SELECT پیش‌نمایش با همان WHERE اجرا کنید.
• تعداد ردیف مورد انتظار را با @@ROWCOUNT یا OUTPUT کنترل کنید.
• در عملیات حساس از SET XACT_ABORT ON، TRY/CATCH و Transaction استفاده کنید.
• کد را با داده NULL، تکراری، مرزی و حجم بالا آزمایش کنید.

امنیت

مجوز ALTER حساس است و باید محدود شود.

اصل کمترین سطح دسترسی را رعایت کنید. نام جدول، ستون، جهت Sort و سایر Identifierها با پارامتر عادی جایگزین نمی‌شوند؛ آن‌ها باید از فهرست سفید انتخاب و در Dynamic SQL با QUOTENAME محافظت شوند. داده حساس را فقط در صورت نیاز برگردانید و Audit عملیات تغییردهنده را نگه دارید.

مقایسه با روش‌های مشابه

روش‌های جایگزین

DELETE برای شرط انتخابی و Partition Switch برای Retention جایگزین‌اند.

دستورات و توابع مرتبط

DELETE، DROP TABLE، Identity، Foreign Key، Partitioning و Transaction

سناریوی واقعی و طراحی Production

در یک سامانه فروش یا تولید، TRUNCATE TABLE ممکن است روی میلیون‌ها ردیف و هم‌زمان با تراکنش‌های دیگر اجرا شود. طراحی Production باید محدوده عملیات، Isolation Level، Lock Duration، ظرفیت Transaction Log، سیاست Retry و زمان‌بندی Job را مشخص کند. اجرای درست فقط به موفق شدن Statement محدود نیست؛ نتیجه باید قابل Audit و قابل تکرار باشد.

در ETL بهتر است داده ابتدا به جدول Stage وارد شود، تعداد و کیفیت آن کنترل گردد و سپس به مقصد منتقل شود. در گزارش‌گیری نیز Snapshot زمانی یا مرز ثابت لازم است تا کاربر هنگام صفحه‌بندی یا محاسبه شاخص با جابه‌جایی غیرمنتظره داده روبه‌رو نشود.

برای عملیات انبوه، Batch Size را با آزمون تعیین کنید. Batch بسیار کوچک overhead زیادی دارد و Batch بسیار بزرگ Log، Lock و زمان Rollback را افزایش می‌دهد. مانیتورینگ باید تعداد ردیف، مدت اجرا، خطا، Deadlock و مصرف منابع را ثبت کند.

نسخه‌های پشتیبانی‌شده، تاریخچه و منابع

TRUNCATE TABLE طبق این مقاله برای همه نسخه‌ها؛ Partition در نسخه‌های جدید در نظر گرفته شده است. بااین‌حال برخی Optionها یا رفتارهای بهینه‌ساز به Compatibility Level و Cumulative Update وابسته‌اند. قبل از استفاده در سامانه حیاتی، Syntax را در نسخه مقصد و با همان Compatibility Level آزمایش کنید.

برای مرجع دقیق، صفحه رسمی Microsoft Learn مربوط به TRUNCATE TABLE Transact-SQL، مستندات Query Processing Architecture، Index Design Guide، Transaction Locking Guide و Release Notes نسخه مقصد را مطالعه کنید.

ترفندهای حرفه‌ای و Cheat Sheet

• قبل از اجرای TRUNCATE TABLE تعداد ردیف مورد انتظار را یادداشت کنید.
• Actual Plan را با حداقل دو مقدار پارامتر متفاوت مقایسه کنید.
• در کد فارسی ثابت‌های متنی را با N بنویسید.
• هشدارهای Plan را نادیده نگیرید؛ به‌ویژه Spill و Convert.
• برای تغییر داده، نتیجه کنترل و Audit پس از Commit داشته باشید.

-- Cheat Sheet: TRUNCATE TABLE
-- 1) هدف، Grain و تعداد ردیف مورد انتظار را مشخص کنید.
-- 2) نوع داده، NULL، Unicode و Collation را کنترل کنید.
-- 3) Predicate و Index را با Actual Plan بسنجید.
-- 4) عملیات حساس را داخل TRY/CATCH و Transaction اجرا کنید.
-- 5) @@ROWCOUNT، OUTPUT یا SELECT کنترلی را بررسی کنید.

تمرین و Quiz

تمرین

اسکریپت امن Truncate Stage با کنترل DB_NAME، TRY/CATCH و Transaction بنویسید.

Quiz

مهم‌ترین کنترل قبل از استفاده از TRUNCATE TABLE در محیط Production چیست؟

پاسخ: تعریف دقیق محدوده و تعداد ردیف، هماهنگی نوع داده، بررسی Execution Plan و داشتن روش اعتبارسنجی و بازگشت.

سؤالات مصاحبه

1. TRUNCATE TABLE در ترتیب منطقی پردازش SQL Server چه جایگاهی دارد؟
2. رفتار TRUNCATE TABLE با NULL و نوع داده ناسازگار چیست؟
3. کدام Operatorهای Execution Plan برای TRUNCATE TABLE رایج‌اند؟
4. چه زمانی جایگزین TRUNCATE TABLE انتخاب بهتری است؟
5. چگونه امنیت و هم‌زمانی TRUNCATE TABLE را در Production کنترل می‌کنید؟

سؤالات متداول

آیا TRUNCATE TABLE همیشه از ایندکس استفاده می‌کند؟

خیر. انتخاب Scan یا Seek به Predicate، Selectivity، آمار، پوشش ستون‌ها و هزینه تخمینی بستگی دارد. وجود ایندکس به‌تنهایی تضمین استفاده نیست.

چرا نتیجه یا سرعت TRUNCATE TABLE بین دو اجرا فرق می‌کند؟

تغییر داده، پارامتر، Cache Plan، آمار، فشار هم‌زمانی، Memory Grant و Compatibility Level می‌توانند Plan یا زمان اجرا را تغییر دهند.

چگونه TRUNCATE TABLE را امن‌تر اجرا کنیم؟

پارامترسازی، حداقل Permission، Transaction مناسب، کنترل تعداد ردیف، Audit و آزمایش در محیط مشابه Production مهم‌ترین اقدامات هستند.

چک‌لیست نهایی

• Syntax TRUNCATE TABLE با نسخه مقصد سازگار است.
• همه News/Business Keyها و محدوده ردیف کنترل شده‌اند.
• NULL، رشته خالی، Unicode و Collation تست شده‌اند.
• نوع داده پارامتر با ستون هماهنگ است.
• Actual Plan، Logical Reads و TempDB بررسی شده‌اند.
• ورودی پارامتری و Permission حداقلی است.
• برای DML راه Rollback و Audit وجود دارد.
• خروجی با Query کنترلی اعتبارسنجی می‌شود.

جمع‌بندی

دستور TRUNCATE TABLE زمانی قابل اعتماد است که Syntax، معنای منطقی و هزینه فیزیکی آن هم‌زمان فهمیده شود. برای TRUNCATE TABLE فقط رسیدن به نتیجه ظاهراً درست کافی نیست؛ نتیجه باید در برابر NULL، تکرار، تغییر حجم، اجرای هم‌زمان و تغییر Plan پایدار باشد.

کد خوانا، ستون‌های صریح، Predicate دقیق، نوع داده هماهنگ، Index سنجیده، پارامترسازی و کنترل پس از اجرا مهم‌ترین اصول‌اند. با رعایت این موارد می‌توان مقاله و نمونه کد را مستقیماً در CMS آموزشی منتشر کرد و همان الگوها را با احتیاط در پروژه واقعی به کار برد.