آشنایی با Temporary Table در SQL Server

آشنایی با Temporary Table  در SQL Server

در SQL Server، Temporary Table یا «جدول موقت» جدولی است که به طور موقت در tempdb ایجاد شده و عمدتاً برای ذخیره و پردازش داده‌های موقت در طی یک جلسه یا یک بچ خاص استفاده می‌شود.
دو نوع اصلی آن محلی (با پیشوند #) است که فقط برای همان اتصال (Connection) قابل مشاهده و پس از بسته شدن آن حذف می‌شود و سراسری (با پیشوند ##) که بین همه اتصال‌ها به اشتراک می‌آید و با بسته شدن آخرین اتصال از بین می‌رود.
جداول موقت عملکرد بهتری نسبت به جدول معمولی برای حجم بالای داده‌های موقت دارند، می‌توانند ایندکس، کلید اصلی و محدودیت (Constraint) داشته باشند و به کاهش قفل‌شدگی روی جداول اصلی کمک می‌کنند.

Temporary Table چیست؟

جدول موقتی نوع خاصی از جدول در SQL Server است که فقط برای مدت زمان مشخصی (معمولاً تا پایان یک جلسه یا تا زمانی که دیگر به آن نیاز نباشد) وجود دارد.
زمانی که جدول از محدوده خود خارج شود، موتور پایگاه داده به طور خودکار آن را حذف می‌کند.
این مکانیزم برای ذخیره نتایج میانی، بهینه‌سازی کوئری‌ها یا دستکاری داده‌ها بدون تأثیر بر جداول دائمی استفاده می‌شود.

ویژگی‌های کلیدی Temporary Table  در SQL Server

ویژگی	توضیح
محل ذخیره	همه Temporary Table‌ها در دیتابیس سیستمی TempDB ذخیره می‌شوند
طول عمر	به طور خودکار پس از خروج از محدوده حذف می‌شوند
قابلیت‌ها	مانند جداول عادی می‌توان از آن‌ها کوئری گرفت، ایندکس داشت و دیتا درج/به‌روزرسانی/حذف کرد
لاگینگ	مانند جداول عادی لاگ می‌شوند، اما TempDB از حداقل لاگینگ استفاده می‌کند

   
   

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

این دوره به شما آموزش می‌دهد که چگونه داده‌ها را به بهترین شکل مدیریت کنید، گزارش‌های قدرتمند بسازید و به تحلیل‌های عمیق دست یابید.

با محتوای جذاب و پروژه‌های واقعی، شما نه تنها تئوری را یاد می‌گیرید، بلکه توانایی‌های عملی خود را نیز تقویت می‌کنید.

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 ⇐همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

   

انواع Temporary Table در SQL Server

SQL Server چهار نوع اصلی برای ذخیره داده‌های موقتی ارائه می‌دهد. در این راهنما بر دو نوع اصلی تمرکز می‌کنیم اما همه را معرفی می‌کنیم.

نوع	پیشوند	محدوده (Scope)	ویژگی اصلی
محلی (Local)	#	فقط جلسه فعلی	پس از اتمام جلسه خودکار حذف می‌شود
سراسری (Global)	##	همه جلسات	پس از اتمام آخرین جلسه حذف می‌شود
متغیر جدول	@	دسته یا رویه جاری	سبک، مناسب داده‌های کم
Memory-Optimized	-	کل دیتابیس	بسیار سریع، مقیم در حافظه

            
      
       

Local Temporary Table (جدول موقتی محلی)

این نوع رایج‌ترین نوع است که با یک علامت # در ابتدای نام جدول مشخص می‌شود.

-- ایجاد جدول موقتی محلی
CREATE TABLE #MyLocalTempTable (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100),
    CreateDate DATETIME DEFAULT GETDATE()
);

ویژگی‌های کلیدی Local Temporary Table

• فقط در جلسه فعلی قابل مشاهده است
• پس از اتمام جلسه یا بسته شدن اتصال، به طور خودکار حذف می‌شود
• اگر در Stored Procedure ایجاد شود، پس از اتمام اجرای رویه حذف می‌شود
• حداکثر طول نام: 116 کاراکتر (SQL Server یک پسوند منحصر‌به‌فرد داخلی اضافه می‌کند)

مثال عملی از Temporary Table در SQL Server

فرض کنید می‌خواهیم مشتریانی که در ۳۰ روز گذشته سفارش بیش از ۵۰۰ داشته‌اند را گزارش دهیم:

-- گام ۱: ایجاد جدول موقتی برای ذخیره نتایج میانی
CREATE TABLE #AggregatedOrders (
    CustomerID INT,
    TotalOrderValue DECIMAL(10, 2)
);

-- گام ۲: پر کردن جدول با داده‌های تجمیعی
INSERT INTO #AggregatedOrders (CustomerID, TotalOrderValue)
SELECT 
    o.CustomerID, 
    SUM(o.OrderAmount) AS TotalOrderValue
FROM Orders o
WHERE o.OrderDate >= DATEADD(DAY, -30, GETDATE())
GROUP BY o.CustomerID
HAVING SUM(o.OrderAmount) > 500;

-- گام ۳: دریافت نتیجه نهایی با JOIN ساده
SELECT 
    c.CustomerID, 
    c.CustomerName, 
    t.TotalOrderValue
FROM Customers c
JOIN #AggregatedOrders t ON c.CustomerID = t.CustomerID
ORDER BY t.TotalOrderValue DESC;

-- جدول به طور خودکار پس از اتمام جلسه حذف می‌شود

همان‌طور که می‌بینید، استفاده از Temporary Table یک کوئری پیچیده را به چند مرحله ساده و خواناتر تبدیل می‌کند.

Global Temporary Table (جدول موقتی سراسری)

این نوع با دو علامت ## در ابتدای نام جدول مشخص می‌شود و برای اشتراک‌گذاری داده بین جلسات مختلف استفاده می‌شود.

-- ایجاد جدول موقتی سراسری
CREATE TABLE ##MyGlobalTempTable (
    ID INT PRIMARY KEY,
    Data NVARCHAR(4000)
);

ویژگی‌های کلیدی Global Temporary Table

• برای همه جلسات (sessions) قابل مشاهده است
• تا زمانی که جلسه ایجادکننده بسته شود و همه جلسات دیگر ارجاع به آن را تمام کنند، باقی می‌ماند
• برای به اشتراک‌گذاری داده بین چندین کاربر یا فرایند مناسب است
• رفتار حذف خودکار با تنظیم GLOBAL_TEMPORARY_TABLE_AUTO_DROP قابل کنترل است (پیش‌فرض ON)

Table Variable (متغیر جدول)

اگرچه تکنیکاً یک Temporary Table نیست، اما اغلب در کنار آن‌ها استفاده می‌شود. متغیر جدول با علامت @ تعریف می‌شود.

-- تعریف متغیر جدول
DECLARE @OrderTable TABLE (
    OrderID INT,
    ProductName NVARCHAR(100),
    Quantity INT
);

-- درج داده
INSERT INTO @OrderTable VALUES (1, 'Product A', 5);

مقایسه با Temporary Table‌های معمولی

معیار	Local Temp Table	Table Variable     (#)
محل ذخیره	TempDB (در حافظه بهینه می‌شود)	TempDB (روی دیسک)
به‌روزرسانی آمار	ندارد (تخمین ۱ ردیف)	دارد (آمار به‌روز می‌شود)
کمپایل مجدد رویه	کمتر ایجاد می‌کند	می‌تواند ایجاد کند
مناسب برای	داده‌های کم (تعداد ردیف کم)	داده‌های حجیم
ایندکس	محدود (از SQL Server 2014 به بعد)	کامل
بازگشت به عقب (Rollback)	تأثیر نمی‌پذیرد	تحت تأثیر تراکنش است

نکته مهم: قبل از SQL Server 2019، Query Optimizer فرض می‌کرد Table Variable فقط یک ردیف دارد که می‌توانست باعث عملکرد ضعیف در JOIN شود. از SQL Server 2019 به بعد، ویژگی Table Variable Deferred Compilation این مشکل را حل کرده است.

Memory-Optimized Temp Table (جدول موقتی بهینه‌شده حافظه)

این نوع پیشرفته‌ترین نوع است که از In-Memory OLTP استفاده می‌کند و عملکردی تا ۱۰ برابر سریع‌تر از Temporary Table‌های معمولی دارد.

ویژگی‌ها:

• کاملاً در حافظه ذخیره می‌شود (بدون عملیات I/O روی دیسک)
• هیچ استفاده‌ای از TempDB ندارد (کاهش رقابت و قفل)
• باید حداقل یک ایندکس داشته باشد
• با گزینه DURABILITY = SCHEMA_ONLY، داده‌ها پس از ریستارت سرور از دست می‌روند (مناسب داده‌های موقتی)

تبدیل یک Local Temp Table به Memory-Optimized

-- ابتدا: جدول موقتی معمولی
CREATE TABLE #tempSessionC (
    Column1 INT NOT NULL,
    Column2 NVARCHAR(4000)
);

-- تبدیل به Memory-Optimized با SCHEMA_ONLY
CREATE TABLE dbo.soSessionC (
    Column1 INT NOT NULL,
    Column2 NVARCHAR(4000) NULL,
    SpidFilter SMALLINT DEFAULT (@@spid) NOT NULL,
    CONSTRAINT CHK_soSessionC_SpidFilter CHECK (SpidFilter = @@spid),
    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

-- افزودن سیاست امنیتی برای جداسازی جلسات
CREATE SECURITY POLICY dbo.soSessionC_SpidFilter_Policy
    ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter) ON dbo.soSessionC
    WITH (STATE = ON);
GO

پیش‌نیازها برای SQL Server (نه Azure)

برای استفاده از این ویژگی در SQL Server محلی، باید یک FILEGROUP با گزینه MEMORY_OPTIMIZED_DATA به دیتابیس اضافه کنید:

ALTER DATABASE YourDatabaseName
    ADD FILEGROUP FgMemOptim CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE YourDatabaseName
    ADD FILE (NAME = N'FileMemOptim', FILENAME = N'C:\DATA\FileMemOptim')
    TO FILEGROUP FgMemOptim;
GO

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

ساختار ذخیره‌سازی

همه Temporary Table‌ها (به جز نوع Memory-Optimized) در دیتابیس سیستمی TempDB ذخیره می‌شوند. TempDB:

• یک دیتابیس ساده (Simple Recovery Model) است
• پس از هر بار ریستارت SQL Server پاک می‌شود
• فقط حداقل لاگینگ لازم برای پشتیبانی از Rollback انجام می‌دهد

پسوند داخلی (Internal Suffix)

هنگامی که دو جلسه همزمان یک Local Temp Table با نام یکسان ایجاد می‌کنند، SQL Server برای جلوگیری از تداخل، یک پسوند عددی منحصربه‌فرد به نام جدول در TempDB اضافه می‌کند:

نام واقعی در TempDB: #MyLocalTempTable______00000000001E

شما فقط می‌نویسید: #MyLocalTempTable
این پسوند فقط در سطح داخلی قابل مشاهده است و شما همچنان با نام اصلی خود به جدول دسترسی دارید.

کش شدن در حافظه

برخلاف باور عمومی، Temporary Table‌ها تنها در حافظه نیستند. آن‌ها روی دیسک در TempDB ذخیره می‌شوند اما ممکن است در حافظه کش شوند اگر:

• به طور مکرر استفاده شوند
• حافظه کافی موجود باشد

کاربردهای عملی  Temporary Table در SQL Server

۱. شکستن کوئری‌های پیچیده

به جای نوشتن یک کوئری بزرگ با JOIN‌های زیاد و توابع تجمیعی:

-- بدون Temporary Table (پیچیده)
SELECT c.CustomerID, c.CustomerName, 
       (SELECT SUM(Amount) FROM Orders WHERE CustomerID = c.CustomerID AND OrderDate > '2024-01-01')
FROM Customers c
WHERE c.Status = 'Active' AND ...

می‌توانید:

-- با Temporary Table (ساده و خواناتر)
CREATE TABLE #ActiveOrders (CustomerID INT, TotalAmount DECIMAL);

INSERT INTO #ActiveOrders
SELECT CustomerID, SUM(Amount)
FROM Orders
WHERE OrderDate > '2024-01-01'
GROUP BY CustomerID;

SELECT c.CustomerID, c.CustomerName, a.TotalAmount
FROM Customers c
JOIN #ActiveOrders a ON c.CustomerID = a.CustomerID
WHERE c.Status = 'Active';

۲. استفاده مجدد از نتایج میانی

اگر به یک مجموعه داده در چندین مرحله از یک رویه نیاز دارید:

CREATE PROCEDURE GenerateMonthlyReport
AS
BEGIN
    -- ایجاد داده‌های میانی یک بار
    SELECT * INTO #MonthlySales
    FROM Sales
    WHERE SaleDate BETWEEN '2024-01-01' AND '2024-01-31';
    
    -- استفاده مجدد چندین بار
    SELECT Region, SUM(Amount) FROM #MonthlySales GROUP BY Region;
    SELECT ProductID, COUNT(*) FROM #MonthlySales GROUP BY ProductID;
    SELECT CustomerID, MAX(Amount) FROM #MonthlySales GROUP BY CustomerID;
    
    DROP TABLE #MonthlySales; -- اختیاری، خودکار حذف می‌شود
END

۳. بهینه‌سازی عملکرد با حذف عملیات تکراری

اگر در یک کوئری، یک جدول بزرگ چندین بار Join شود:

-- قبل: جدول Orders 3 بار Scan می‌شود
SELECT o1.CustomerID, o2.Total, o3.Average
FROM Orders o1
JOIN (SELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID) o2 ...
JOIN (SELECT CustomerID, AVG(Amount) FROM Orders GROUP BY CustomerID) o3 ...

-- بعد: فقط یک بار جدول خوانده می‌شود
SELECT CustomerID, SUM(Amount) AS Total, AVG(Amount) AS Average
INTO #OrderStats
FROM Orders
GROUP BY CustomerID;

SELECT CustomerID, Total, Average FROM #OrderStats;

جمع‌بندی

چه زمانی از کدام نوع استفاده کنیم؟

سناریو	نوع پیشنهادی	دلیل
داده‌های کم (کمتر از ۱۰۰ ردیف) در یک دسته	Table Variable	سبک، کاهش recompile
داده‌های حجیم در یک جلسه	Local Temp Table (#)	ایندکس، آمار، کارایی بالا
نیاز به اشتراک داده بین جلسات	Global Temp Table (##)	دسترسی چند جلسه
عملکرد بحرانی و داده زیاد	Memory-Optimized Temp Table	سرعت ۱۰ برابر، بدون I/O
کوئری ساده و یکبار مصرف	CTE	خوانایی بالا، بدون مدیریت جدول