راهنمای جامع اشیای موقت در SQL Server؛ از #Table تا In-Memory
مقدمه
اشیای موقت در SQL Server ابزارهایی برای شکستن Query پیچیده، نگهداری خروجی میانی، تبادل محدود داده و کاهش محاسبه تکراری هستند. شباهت ظاهری آنها نباید باعث شود یک نسخه را برای همه سناریوها انتخاب کنیم؛ هر گزینه دامنه، طول عمر، آمار، ایندکس، تراکنش و هزینه منابع متفاوتی دارد.
جدول موقت محلی و سراسری در tempdb قرار میگیرند، متغیر جدولی دامنه Batch دارد و ساختارهای Memory-Optimized از موتور In-Memory OLTP استفاده میکنند. خود SQL Server برخی اشیای موقت را Cache میکند تا هزینه ساخت مجدد کاهش یابد، اما این به معنای حذف نیاز به طراحی tempdb، Schema باریک یا آزمون همزمانی نیست.
هدف این مقاله ارائه یک چارچوب تصمیمگیری است. ابتدا دسترسی سریع به چهار مقاله تخصصی میآید، سپس معماری، مقایسه، مثالهای اجرایی، خطاها، Performance، FAQ و سؤالات مصاحبه بررسی میشود. لینکها داخلی و مستقل از دامنه هستند.
دسترسی سریع به آموزشهای تخصصی
مدل ذهنی: دامنه، طول عمر و ذخیرهسازی
دامنه دید پاسخ میدهد چه کدی میتواند شیء را ببیند. #Table به نشست سازنده محدود است و کد تو در توی همان نشست معمولاً آن را میبیند. ##Table میان نشستها مشترک است. @Table در دامنه نحوی اعلان باقی میماند و Dynamic SQL بیرونی آن را نمیبیند. Memory-Optimized Table یک شیء metadata دائمی است، هرچند داده SCHEMA_ONLY پس از بازیابی باقی نمیماند.
طول عمر با دامنه مرتبط است اما عین آن نیست. جدول موقت میتواند صریح Drop شود، هنگام پایان Scope حذف شود یا تا پایان آخرین ارجاع فعال باقی بماند. برای سامانه قابل پشتیبانی، مالکیت ساخت و حذف را صریح کنید و به قطع اتصال بهعنوان برنامه پاکسازی تکیه نکنید.
ذخیرهسازی نیز تصمیم Performance را شکل میدهد. tempdb محل Table، Sort، Spill، Version Store و اشیای داخلی است؛ بنابراین یک Query بد میتواند با دیگر بارها رقابت کند. Memory-Optimized SCHEMA_ONLY فشار tempdb و Log I/O داده را کم میکند، اما حافظه، سازگاری و عملیات Deployment تازهای میطلبد.
معرفی چهار گزینه
جدولهای موقت محلی (#Table)
جدول موقت محلی با پیشوند # ساخته میشود، در tempdb قرار میگیرد و نام منطقی آن فقط در نشست سازنده و دامنههای تو در توی همان نشست قابل مشاهده است. SQL Server برای جداسازی همنامها یک پسوند داخلی به نام فیزیکی اضافه میکند. این ساختار مانند جدول عادی ستون، قید، آمار و ایندکس دارد و برای مجموعهدادههای میانی با اندازه متغیر انتخابی انعطافپذیر است.
مطالعه مقاله مستقل جدولهای موقت محلی (#Table) با ده مثال عملی
جدولهای موقت سراسری (##Table)
جدول موقت سراسری با پیشوند ## در tempdb ساخته میشود و برخلاف نوع محلی، نشستهای دیگر SQL Server نیز میتوانند آن را ببینند. عمر آن به نشست سازنده و آخرین دستور فعالی که به آن ارجاع دارد وابسته است. همین اشتراکپذیری، هماهنگی نام، مالکیت داده، امنیت و پاکسازی قطعی را به بخش اصلی طراحی تبدیل میکند.
مطالعه مقاله مستقل جدولهای موقت سراسری (##Table) با ده مثال عملی
متغیرهای جدولی (Table Variables)
متغیر جدولی با DECLARE و نوع table در دامنه Batch، تابع یا Stored Procedure تعریف میشود. این ساختار برای مجموعههای کوچک و قراردادهای جدولی محدود، کد فشرده و دامنه روشن فراهم میکند. با این حال نبود آمار توزیعی و محدودیت تغییر ساختار میتواند در حجمهای بزرگ، Joinهای پیچیده و تصمیمهای موازیسازی به برآورد نامناسب منجر شود.
مطالعه مقاله مستقل متغیرهای جدولی (Table Variables) با ده مثال عملی
جدولهای بهینهشده برای حافظه (Memory-Optimized)
جدول Memory-Optimized بخشی از In-Memory OLTP است و ردیفها و ایندکسهای خود را با ساختارهای بدون Page در حافظه مدیریت میکند. کنترل همزمانی خوشبینانه و نسخهبندی ردیف جای قفلگذاری سنتی را میگیرد. جدول میتواند SCHEMA_AND_DATA برای دوام کامل یا SCHEMA_ONLY برای نگهداری صرفاً ساختار پس از بازیابی باشد؛ حالت دوم در برخی سناریوها جایگزین مرحلهبندی tempdb میشود.
مطالعه مقاله مستقل جدولهای بهینهشده برای حافظه (Memory-Optimized) با ده مثال عملی
جدول مقایسهای اشیای موقت
| ساختار | کاربرد اصلی | دامنه و نکته مهم | لینک آموزش کامل |
|---|
| Local Temporary Tables (#Table) | مرحلهبندی نتایج بزرگ میان چند Query و جلوگیری از محاسبه چندباره | جدول موقت محلی یک مقدار اسکالر برنمیگرداند؛ یک شیء جدولی رابطهای ایجاد میکند که تا پایان دامنه یا حذف صریح در دسترس است. نوع هر ستون دقیقاً از تعریف CREATE TABLE یا خروجی SELECT INTO به دست میآید. | آموزش جدول موقت محلی |
| Global Temporary Tables (##Table) | تبادل کوتاهمدت داده میان Jobها یا نشستهای هماهنگشده در یک Instance | این دستور نیز مقدار اسکالر ندارد و یک شیء جدولی مشترک در tempdb ایجاد میکند. تمام نشستهای مجاز میتوانند با نام منطقی واحد به آن دسترسی یابند، بنابراین قرارداد ستونها و زمان حذف باید میان تولیدکننده و مصرفکننده ثابت باشد. | آموزش جدول موقت سراسری |
| Table Variables | نگهداری چند ردیف تنظیم یا کلید در یک Batch کوتاه | متغیر جدولی یک ظرف رابطهای با Schema ثابت است، نه مقدار برگشتی اسکالر. میتوان آن را در SELECT، JOIN، INSERT، UPDATE و DELETE همان دامنه به کار برد و نوع جدول تعریفشده توسط کاربر را نیز بهعنوان TVP به رویه ارسال کرد. | آموزش متغیر جدولی |
| Memory Optimized Tables | جایگزینی Staging پرتکرار و پررقابت tempdb با جدول SCHEMA_ONLY و جداسازی SessionID | CREATE TABLE یک شیء دائمی در metadata دیتابیس ایجاد میکند که اجرای آن از موتور In-Memory OLTP استفاده میکند. در حالت SCHEMA_ONLY داده پس از Restart یا بازیابی پایدار نمیماند، اما خود جدول باقی است و باید هنگام استقرار ساخته شود، نه برای هر فراخوانی. | آموزش جدول بهینهشده برای حافظه |
چارچوب تصمیمگیری حرفهای
اگر داده فقط چند ردیف و منطق یک Batch کوتاه است، @Table گزینه سادهای برای آزمایش است. اگر ردیفها زیاد یا توزیع داده برای Join مهم است، #Table به دلیل آمار و ایندکس انعطافپذیر اغلب نقطه شروع قویتری است. ##Table را تنها زمانی انتخاب کنید که اشتراک میان نشستها واقعاً الزام باشد و برخورد نام، امنیت و Cleanup طراحی شده باشد.
برای بار بسیار پرتکرار با گلوگاه Lock، Latch یا tempdb، Memory-Optimized ممکن است مناسب باشد. آن را یک ارتقای خودکار تلقی نکنید؛ پیشنیاز Filegroup، ظرفیت حافظه، نوع ایندکس، Conflict خوشبینانه و دوام داده باید بررسی شود. گاهی اصلاح Query یا کاهش ستونها از مهاجرت فناوری ارزش بیشتری دارد.
- حجم معمول و اوج ردیفها را اندازه بگیرید.
- تعداد مصرف مجدد و نوع Join یا Filter را ثبت کنید.
- دامنه دید و نیاز اشتراک میان نشستها را مشخص کنید.
- دو گزینه را با ورودی یکسان و Plan واقعی مقایسه کنید.
- آزمون همزمانی، خطا، Rollback و پاکسازی اجرا کنید.
- معیار پذیرش و برنامه بازگشت را پیش از انتشار بنویسید.
مثالهای ترکیبی و کاربردی
شش مثال زیر الگوهای پایه را کنار هم قرار میدهند. هدف تقلید کورکورانه نیست؛ هر Query یک نقطه شروع برای اندازهگیری در محیط آزمایشی است.
مثال 1: مرحلهبندی با #Table
برای پردازش چندمرحلهای، جدول محلی میسازیم و مجموع را محاسبه میکنیم.
DROP TABLE IF EXISTS #Stage;
CREATE TABLE #Stage(ID INT PRIMARY KEY,Amount DECIMAL(10,2));
INSERT INTO #Stage VALUES(1,100.00),(2,250.00);
SELECT SUM(Amount) AS TotalAmount FROM #Stage;
نکته کاربردی: #Table برای داده میانی دارای Join، آمار و ایندکس انعطاف بیشتری دارد.
مثال 2: اشتراک کنترلشده با ##Table
یک شیء مشترک میسازیم و مالک اجرای جاری را ثبت میکنیم.
DROP TABLE IF EXISTS ##SharedDemo;
CREATE TABLE ##SharedDemo(SessionID INT,MessageText NVARCHAR(40));
INSERT INTO ##SharedDemo VALUES(@@SPID,N'آماده');
SELECT MessageText FROM ##SharedDemo WHERE SessionID=@@SPID;
نکته کاربردی: نام مشترک، امنیت و پاکسازی باید پیش از استفاده چندنشستی طراحی شوند.
مثال 3: مجموعه کوچک با @Table
یک فهرست کوتاه کلیدها را بدون DDL جداگانه نگه میداریم.
DECLARE @IDs TABLE(ID INT PRIMARY KEY);
INSERT INTO @IDs VALUES(10),(20),(30);
SELECT COUNT(*) AS KeyCount FROM @IDs;
نکته کاربردی: برای حجم و Join بزرگ، اختلاف برآورد را با #Table مقایسه کنید.
مثال 4: انتخاب بر اساس Cardinality
تعداد واقعی ردیفهای یک مجموعه میانی را پیش از تصمیم ثبت میکنیم.
DECLARE @Candidate TABLE(ID INT PRIMARY KEY);
WITH n AS(SELECT TOP(500) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) ID FROM sys.all_objects)
INSERT INTO @Candidate SELECT ID FROM n;
SELECT COUNT(*) AS ActualRows,MIN(ID) AS MinID,MAX(ID) AS MaxID FROM @Candidate;
| ActualRows | MinID | MaxID |
|---|
| 500 | 1 | 500 |
نکته کاربردی: حجم فقط یکی از عوامل است؛ توزیع، دفعات مصرف و مسیر Join نیز تصمیم را تغییر میدهد.
مثال 5: ایندکس روی خروجی میانی
روی کلید فیلتر جدول موقت ایندکس میسازیم.
DROP TABLE IF EXISTS #Indexed;
CREATE TABLE #Indexed(CustomerID INT,OrderID INT,Amount DECIMAL(10,2));
INSERT INTO #Indexed VALUES(1,11,50),(2,21,70),(2,22,90);
CREATE INDEX IX_Indexed_Customer ON #Indexed(CustomerID) INCLUDE(OrderID,Amount);
SELECT OrderID,Amount FROM #Indexed WHERE CustomerID=2 ORDER BY OrderID;
| OrderID | Amount |
|---|
| 21 | 70.00 |
| 22 | 90.00 |
نکته کاربردی: INCLUDE میتواند Lookup را کم کند، اما هزینه درج و فضا را باید اندازه گرفت.
مثال 6: پایش فضای tempdb
مصرف داخلی نشست جاری در tempdb را از DMV میخوانیم.
SELECT session_id,
user_objects_alloc_page_count-user_objects_dealloc_page_count AS UserObjectPages,
internal_objects_alloc_page_count-internal_objects_dealloc_page_count AS InternalObjectPages
FROM sys.dm_db_session_space_usage
WHERE session_id=@@SPID;
| session_id | UserObjectPages | InternalObjectPages |
|---|
| شناسه نشست | صفحات اشیای کاربر | صفحات داخلی |
نکته کاربردی: این Snapshot را پیش و پس از مرحله سنگین ثبت کنید تا مصرف همان نشست قابل مقایسه شود.
tempdb، آمار و طرح اجرا
جدولهای موقت و متغیرهای جدولی میتوانند از caching داخلی بهره ببرند و SQL Server در نسخههای جدید کاهش contention metadata و تخصیص را بهبود داده است. با این وجود رشد فایل، توزیع نامتعادل فایلها، Autogrowth کوچک، دیسک کند و Queryهای Spillدار هنوز میتوانند گلوگاه بسازند.
برای #Table آمار توزیعی به Optimizer کمک میکند تعداد ردیف هر Predicate را بهتر تخمین بزند. @Table آمار توزیعی ندارد؛ Deferred Compilation در Compatibility Level 150 تعداد واقعی نخستین اجرا را برای کامپایل میبیند، اما توزیع کامل مقادیر را فراهم نمیکند. Parameter Sensitivity و تغییر شدید حجم همچنان نیازمند آزموناند.
همیشه Actual Execution Plan را همراه STATISTICS IO و TIME بخوانید. Scan به خودی خود بد نیست و Seek همیشه خوب نیست؛ هزینه کل، تعداد ردیف، Lookup، Sort، Spill و Memory Grant تعیینکنندهاند. Query Store برای مشاهده تغییر Plan پس از انتشار و مقایسه بازههای زمانی مفید است.
خطاهای رایج
خطای 1
استفاده از یک نوع شیء برای همه Queryها بدون Baseline. راه اصلاح، تعریف فرض قابل اندازهگیری و مقایسه با گزینه جایگزین در داده و همزمانی نزدیک به تولید است.
خطای 2
ذخیره ستونهای زیاد یا LOBهای غیرضروری در داده میانی. راه اصلاح، تعریف فرض قابل اندازهگیری و مقایسه با گزینه جایگزین در داده و همزمانی نزدیک به تولید است.
خطای 3
ساخت ایندکسهای متعدد که هزینه درج را از سود خواندن بیشتر میکند. راه اصلاح، تعریف فرض قابل اندازهگیری و مقایسه با گزینه جایگزین در داده و همزمانی نزدیک به تولید است.
خطای 4
اتکا به ##Table با نام ثابت در اجرای همزمان. راه اصلاح، تعریف فرض قابل اندازهگیری و مقایسه با گزینه جایگزین در داده و همزمانی نزدیک به تولید است.
خطای 5
فرض اینکه @Table همیشه در حافظه است یا tempdb را مصرف نمیکند. راه اصلاح، تعریف فرض قابل اندازهگیری و مقایسه با گزینه جایگزین در داده و همزمانی نزدیک به تولید است.
خطای 6
ایجاد و حذف Memory-Optimized Table در هر درخواست. راه اصلاح، تعریف فرض قابل اندازهگیری و مقایسه با گزینه جایگزین در داده و همزمانی نزدیک به تولید است.
خطای 7
نادیده گرفتن امنیت، داده کهنه و مسیر Cleanup. راه اصلاح، تعریف فرض قابل اندازهگیری و مقایسه با گزینه جایگزین در داده و همزمانی نزدیک به تولید است.
بهترین روشهای عملی
- Schema را کوچک، صریح و سازگار با نوع ستونهای مبدأ نگه دارید.
- نام شیء و ستونها را بر اساس نقش مرحلهای انتخاب کنید.
- طول عمر و Cleanup را در همان واحد کد مالک کنید.
- ایندکس را از Predicate و Join واقعی استخراج کنید.
- برای بازه زمانی شرط نیمهباز و SARGable بنویسید.
- مصرف tempdb و XTP را با روند زمانی پایش کنید.
- تغییر را با تست بار، Query Store و برنامه Rollback منتشر کنید.
سؤالات متداول
1. شیء موقت در SQL Server چیست؟
ساختاری برای نگهداری نتیجه میانی با طول عمر محدود یا الگوی مصرف ویژه است. #Table، ##Table، @Table و جدول Memory-Optimized دامنه و هزینه یکسان ندارند.
2. چگونه گزینه مناسب را انتخاب کنیم؟
دامنه دید، تعداد ردیف، نیاز به آمار و ایندکس، دفعات بازاستفاده، همزمانی و دوام را روی یک ماتریس بنویسید و دو گزینه برتر را با داده واقعی آزمایش کنید.
3. آیا اشیای موقت هزینه پروژه را کم میکنند؟
اگر منطق پیچیده را به مراحل روشن تبدیل کنند، توسعه و عیبیابی سریعتر میشود؛ طراحی اشتباه نیز میتواند هزینه زیرساخت و SLA را بالا ببرد.
4. چه زمانی بهینهسازی tempdb ارزش تجاری دارد؟
وقتی Waitهای تخصیص، رشد فایل یا زمان گزارش روی کاربر و درآمد اثر دارد، Baseline و تست بار میتواند بازگشت سرمایه تغییر را نشان دهد.
5. #Table بهتر است یا @Table؟
#Table آمار و ایندکس انعطافپذیرتری دارد؛ @Table دامنه ساده و سربار رفتاری متفاوتی ارائه میکند. حجم، Complexity و Compatibility Level تعیینکنندهاند.
6. آیا مشاوره انتخاب و مهاجرت اشیای موقت ممکن است؟
بله؛ بررسی حرفهای شامل Query Store، Execution Plan، Wait Statistics، ظرفیت tempdb، تست گزینه جایگزین و برنامه Rollback است.
7. رایجترین خطای اشیای موقت چیست؟
انتخاب بر اساس عادت و بدون اندازهگیری رایجترین خطاست. خطاهای Scope، برخورد نام، آمار ضعیف و پاکسازی مبهم نیز فراواناند.
8. Performance را چگونه بسنجیم؟
Logical Reads، CPU، Duration، Tempdb Pages، Memory، Estimated/Actual Rows و P95 Latency را در بار همزمان قبل و بعد مقایسه کنید.
9. بهترین روش عمومی چیست؟
Schema باریک، عمر روشن، ایندکس محدود و هدفمند، نامگذاری واضح، پاکسازی قطعی و تست با داده تولیدی پایههای طراحی سالماند.
10. سازگاری نسخهها چه اثری دارد؟
بهبودهای tempdb، Deferred Compilation و In-Memory به نسخه و Compatibility Level وابستهاند؛ پس از Upgrade آزمون رگرسیون الزامی است.
سؤالات مصاحبه
پرسش 1: تفاوت دامنه #Table، ##Table و @Table چیست؟
پاسخ خوب باید تعریف فنی، یک Trade-off، یک مثال پروژهای و روش اندازهگیری داشته باشد. داوطلب حرفهای از حکم مطلق دوری میکند و تصمیم را به Cardinality، Scope، Plan و همزمانی پیوند میدهد.
پرسش 2: چرا آمار توزیعی در انتخاب Join اهمیت دارد؟
پاسخ خوب باید تعریف فنی، یک Trade-off، یک مثال پروژهای و روش اندازهگیری داشته باشد. داوطلب حرفهای از حکم مطلق دوری میکند و تصمیم را به Cardinality، Scope، Plan و همزمانی پیوند میدهد.
پرسش 3: Deferred Compilation چه مشکلی را کم میکند و چه چیزی را حل نمیکند؟
پاسخ خوب باید تعریف فنی، یک Trade-off، یک مثال پروژهای و روش اندازهگیری داشته باشد. داوطلب حرفهای از حکم مطلق دوری میکند و تصمیم را به Cardinality، Scope، Plan و همزمانی پیوند میدهد.
پرسش 4: چه زمانی SCHEMA_ONLY جایگزین tempdb میشود؟
پاسخ خوب باید تعریف فنی، یک Trade-off، یک مثال پروژهای و روش اندازهگیری داشته باشد. داوطلب حرفهای از حکم مطلق دوری میکند و تصمیم را به Cardinality، Scope، Plan و همزمانی پیوند میدهد.
پرسش 5: چگونه Bucket Count ایندکس Hash را انتخاب میکنید؟
پاسخ خوب باید تعریف فنی، یک Trade-off، یک مثال پروژهای و روش اندازهگیری داشته باشد. داوطلب حرفهای از حکم مطلق دوری میکند و تصمیم را به Cardinality، Scope، Plan و همزمانی پیوند میدهد.
پرسش 6: برای تشخیص فشار tempdb چه DMV و معیارهایی میسنجید؟
پاسخ خوب باید تعریف فنی، یک Trade-off، یک مثال پروژهای و روش اندازهگیری داشته باشد. داوطلب حرفهای از حکم مطلق دوری میکند و تصمیم را به Cardinality، Scope، Plan و همزمانی پیوند میدهد.
پرسش 7: چرا نام ثابت ## در Job همزمان خطرناک است؟
پاسخ خوب باید تعریف فنی، یک Trade-off، یک مثال پروژهای و روش اندازهگیری داشته باشد. داوطلب حرفهای از حکم مطلق دوری میکند و تصمیم را به Cardinality، Scope، Plan و همزمانی پیوند میدهد.
پرسش 8: معیار تصمیم میان مرحلهبندی و یک Query واحد چیست؟
پاسخ خوب باید تعریف فنی، یک Trade-off، یک مثال پروژهای و روش اندازهگیری داشته باشد. داوطلب حرفهای از حکم مطلق دوری میکند و تصمیم را به Cardinality، Scope، Plan و همزمانی پیوند میدهد.
جمعبندی و مسیر مطالعه
اشیای موقت ابزار طراحیاند، نه میانبر ثابت. #Table برای مرحلهبندی قابل ایندکس، ##Table برای اشتراک محدود و کنترلشده، @Table برای دامنه کوچک و Memory-Optimized برای گلوگاههای مشخص هر کدام جایگاه خود را دارند. انتخاب خوب با مسئله آغاز و با عدد تأیید میشود.
برای ادامه، مقالههای تخصصی زیر را بخوانید و مثالها را با داده واقعی خود اجرا کنید:
پس از انتخاب اولیه، Baseline و معیار پذیرش را ثبت کنید. اگر نتیجه بهتر نشد یا پیچیدگی عملیاتی افزایش یافت، برنامه بازگشت باید بدون حدس قابل اجرا باشد.