جدول موقت ( Temporary Table- tempTable) در SQL چیست؟

جدول موقت یک جدول base است که در دیتابیس ذخیره نمی شود، اما در عوض تنها زمانی وجود دارد که database session ای که در آن ایجاد شده فعال باشد. تا حد خیلی زیادی مثل جداول دائمی هستند. temp table ها در TempDB ایجاد می شوند و به محض پایان آخرین اتصال به طور خودکار حذف می شوند.

جداول موقت به ما کمک می کند تا نتایج میانی را ذخیره و پردازش کنیم. temp table ها زمانی بسیار مفید هستند که ما نیاز به ذخیره داده های موقت داشته باشیم. جداول موقت را نمی توان پارتیشن بندی کرد. اگر بیش از یک جدول موقت در یک sp یا batch ای ایجاد شود، باید نام های متفاوتی داشته باشند. اگر یک schema_name را هنگام ایجاد یا دسترسی به یک جدول موقت اضافه کنید، نادیده گرفته می شود (ignore میشه). تمام جداول موقت در شمای dbo ایجاد می شوند.

اگر یک جدول موقت محلی در یک sp یا برنامه ای ایجاد شود که می تواند به طور همزمان توسط چندین session اجرا شود، موتور پایگاه داده باید بتواند جداول ایجاد شده توسط session های مختلف را تشخیص دهد. موتور پایگاه داده این کار را با اضافه کردن داخلی یک پسوند عددی به اسم هر جدول موقت محلی انجام می دهد. اسم کامل یک جدول موقت که در جدول sys.sysobjects در tempdb ذخیره شده است از اسم جدول مشخص شده در عبارت CREATE TABLE و پسوند عددی تولید شده توسط سیستم تشکیل شده است.برای اجازه دادن به پسوند، table_name مشخص شده برای اسم یک جدول موقت محلی نمی تواند از 116 کاراکتر تجاوز کند.

در نگاه اول، ممکن است این یک View به نظر برسد، اما view ها و temp table ها تا حدودی متفاوت هستند: ▪ یک view فقط برای یک Query وجود دارد. هر بار که از اسم یک View استفاده می کنید، جدول آن از داده های موجود دوباره ایجاد (recreate) می شود. اما یک temp table برای کل session پایگاه داده ای که در آن ایجاد شده است وجود دارد.

• یک View به طور خودکار با داده های بازیابی شده (retrieve شده) توسط کوئری که آن را تعریف می کند، پر می شود. اما باید داده ها را با دستورات SQL INSERT به یک جدول موقت اضافه کنید.
• فقط view هایی که معیارهای به روز رسانی view را دارند می توانند برای تغییرات داده (data modification) استفاده شوند. اما از آنجایی که جداول موقت جداول پایه (base table) هستند، همه آنها را می توان update کرد. (آنها را می توان update کرد، اما به خاطر داشته باشید که تغییراتی که انجام می دهید در پایگاه داده حفظ نمی شوند.)
• از آنجایی که هر بار که از اسم view استفاده می شود، محتوای یک view تولید می شود، داده های یک view همیشه جاری (current) هستند. داده های موجود در یک جدول موقت، وضعیت پایگاه داده را در زمانی که جدول با داده لود می شود، برمی گرداند. اگر داده‌هایی که جدول موقت از آنها لود شده است، پس از دریافت داده‌های جدول موقت تغییر کند(modify شود)، ممکن است محتویات جدول موقت با سایر بخش‌های پایگاه داده هماهنگ نباشد.

اگر با تغییر داده های منبع، محتویات یک جدول موقت قدیمی می شود، اصلاً چرا از یک جدول موقت استفاده کنیم؟ آیا بهتر نیست به سادگی از view ای استفاده کنیم که محتوای آن به طور مدام بازسازی (regenerate) می شود؟

جواب در performance نهفته است. زمان پردازش برای ایجاد جدول view طول می کشد. اگر قرار است در طول یک session پایگاه داده فقط یک بار از داده ها استفاده کنید، در واقع یک View بهتر از یک جدول موقت عمل می کند زیرا نیازی به ایجاد ساختار برای آن ندارید. اما، اگر قرار است از داده ها به طور مکرر در طول یک session استفاده کنید، یک جدول موقت عملکرد (performance) بهتری را ارائه می دهد زیرا فقط یک بار باید ایجاد شود.

در نتیجه:

استفاده مکرر از یک view زمان بیشتری می برد، اما داده ها را به طور مداوم به روز می کند. استفاده مکرر از جدول موقت باعث صرفه جویی در زمان می شود، اما شما این خطر را دارید که محتوای جدول قدیمی باشد.

چگونه یک temp table در کوئری SQL ایجاد کنیم؟

ا syntax ایجاد یک temp table به شرح زیر است:

برای ایجاد temp table :

 CREATE TABLE #EmpDetails (id INT, name VARCHAR(25))

برای وارد کردن (insert کردن) مقادیر در temp table:

 INSERT INTO #EmpDetails VALUES (01, 'Lalit'), (02, 'Atharva')

برای select کردن مقادیر از temp table:

SELECT * FROM #EmpDetails

دو نوع temp table وجود داره:

1- جدول موقت محلی - local temp table

یک temp table محلی فقط برای session ای که آن را ایجاد کرده است در دسترس است. هنگامی که connection ای که آن را ایجاد کرده است بسته شود، به طور خودکار حذف می شود. برای ایجاد جدول موقت محلی (local)، تنها یک "#" به عنوان پیشوند نام جدول استفاده می شود. همچنین کاربر می تواند با استفاده از کوئری “DROP TABLE #EmpDetails” این جدول موقت را حذف کند. اعداد رندوم به اسم جدول اضافه می شوند. اگر جدول موقت در داخل sp ایجاد شود، پس از اتمام اجرای sp ، به طور خودکار حذف می شود.

مثال

CREATE PROCEDURE ProcTemp 
AS
BEGIN
CREATE TABLE #EmpDetails
INSERT INTO #EmpDetails VALUES ( 01, 'Lalit'), ( 02, 'Atharva')
SELECT * FROM #EmpDetails
END
EXECUTE ProcTemp

2- جدول موقت سراسری - global temporary table

برای ایجاد یک جدول موقت سراسری، نماد "##" را قبل از نام جدول اضافه کنید.

مثال

CREATE TABLE ##EmpDetails (id INT, name VARCHAR(25))

جداول موقت سراسری ( global temporary table ) برای همه کانکشن ها قابل مشاهده است و با بسته شدن آخرین کانکشنی که به جدول ر فرنس می دهد، حذف (drop) می شوند. در tempdb ذخیره می شوند و در بین تمام session های کاربران در کلِ اینستنسِ SQL Server به اشتراک گذاشته می شوند. user session از پایگاه های داده دیگر نمی توانند به temp table های سراسری دسترسی پیدا کنند. اسم جدول سراسری باید یک اسم جدول منحصر به فرد داشته باشد. هیچ عدد تصادفی پسوندی در انتهای اسم جدول وجود نخواهد داشت.

• جداول موقت زمانی که از محدوده خارج می شوند به طور خودکار حذف می شوند، مگر اینکه به صراحت با استفاده از DROP TABLE حذف شوند:
• یک جدول موقت محلی ایجاد شده در یک sp به طور خودکار پس از اتمام sp حذف می شود. جدول را می توان با هر sp تو در تو که توسط sp ای که جدول را ایجاد کرده است، رفرنس داد. جدول نمی تواند با sp ای که sp ای که جدول را ایجاد کرده را فراخوانی کرده است، ارجاع داده شود.
• تمام جداول موقت محلی دیگر به طور خودکار در پایان session جاری drop می شوند. جداول موقت سراسری به طور خودکار حذف می شوند وقتی session ای که جدول را ایجاد کرده است به پایان می رسد و همه task های دیگر رفرنس دادن به آنها را متوقف می کنند.
• ارتباط بین یک task و یک جدول فقط برای life time ِ یک دستور Transact-SQL حفظ می شود. ( a single Transact-SQL statement). این بدان معناست که یک جدول موقت سراسری پس از تکمیل آخرین دستور Transact-SQL که به طور فعال به جدول هنگامِ پایانِ session ایجاد رفرنس داده میشد، حذف می شود.

یک جدول موقت محلی ایجاد شده در یک SP یا trigger می تواند نام یک جدول موقتی را داشته باشد که قبل از فراخوانی SP یا trigger ایجاد شده است. با این حال، اگر یک query به یک جدول موقت اشاره کند و دو جدول موقت با همان نام در آن زمان وجود داشته باشد، مشخص نیست که کوئری با کدام جدول حل شده است.

ا SP های تودرتو همچنین می توانند جداول موقتی با همان نام جدول موقت ایجاد کنند که توسط SP ای که آن را فراخوانی می کند ایجاد شده است. با این حال، برای رفع تغییرات در جدولی که در SP تودرتو ایجاد شده است، جدول باید همان ساختار، با همان نام ستون‌ها، مانند جدول ایجاد شده در SP فراخوانی کننده باشد.

این در مثال زیر نشان داده شده است.

این مجموعه ی نتایج است:

هنگامی که جداول موقت محلی یا سراسری ایجاد می کنید، دستور CREATE TABLE از constraint definition ها به جز constraint های FOREIGN KEY پشتیبانی می کند. اگر یک محدودیت FOREIGN KEY در یک جدول موقت مشخص شده باشد، این statement یک پیام هشدار برمی‌گرداند که بیان می‌کند محدودیت نادیده گرفته شده است (constraint was skipped)جدول همچنان بدون محدودیت های FOREIGN KEY ایجاد می شود. جداول های موقت نمی توانند به محدودیت های FOREIGN KEY ارجاع بدهند.

اگر یک جدول موقت با یک محدودیت با نام ایجاد شود و جدول موقت در محدوده یک transaction تعریف شده توسط کاربر (user-defined) ایجاد شود، تنها یک کاربر در هر زمان می تواند statement ای را که temp tableرا ایجاد می کند، execute کند. به عنوان مثال، اگر یک SP یک جدول موقت با یک Constraint کلید اولیه (primary key) ایجاد کند، SP نمی تواند به طور همزمان توسط چندین کاربر اجرا شود.

اPermission ها

هر user میتواند object های موقت سراسری ایجاد کند. user ها فقط می توانند به object های خود دسترسی داشته باشند مگر اینکه permission های اضافی دریافت کنند.