SQL Server-جدول موقت-Local Temporary Table-Global Temporary Table-Table Variable-Session-Stored Procedures-عملیات میانی

در SQL Server، یک Temporary Table یا جدول موقت، نوعی جدول است که به مدت محدود وجود دارد و اطلاعات را ذخیره و استفاده می‌کند. این نوع جداول برای ذخیره موقت داده‌ها به منظور انجام عملیات‌های میانی، محاسبات یا ایجاد نتایج موقت در طول اجرای کوئری‌ها و پروسه‌ها استفاده می‌شوند.

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

  • مدت زمان وجود: Temporary Table‌ها به مدت زمان اجرای جلسه (Session) یا به صورت جهانی (Global) موجود هستند. جدول‌های موقت محلی با # و جدول‌های موقت جهانی با ## شروع می‌شوند.
  • ساختار مشابه با جدول عادی: Temporary Table‌ها دارای ساختاری مشابه با جدول‌های عادی هستند و می‌توان در آنها ستون‌ها و شاخص‌ها (Index) را تعریف کرد.
  • نام‌گذاری: نام Temporary Table‌ها با علامت # یا ## شروع می‌شود. اگر با # شروع شود، به جدول موقت محدود به جلسه فعلی اشاره دارد و اگر با ## شروع شود، به جدول موقت جهانی اشاره دارد که در دسترس همه جلسات است.
  • مجوزها: برای ایجاد یا دسترسی به یک Temporary Table، کاربر نیاز به مجوزهای مرتبط با مدیریت داده (Data Definition and Data Manipulation) دارد.

مثال زیر نشان می‌دهد که چگونه یک Temporary Table به نام #TempEmployees ایجاد می‌شود:

CREATE TABLE #TempEmployees
(
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
);

اکنون می‌توانید داده‌ها را به #TempEmployees اضافه کرده و با آن کوئری اجرا کنید. Temporary Table به عنوان ابزاری قدرتمند در استفاده از داده‌ها در میانه یک فرآیند مفید است و پس از پایان اجرای جلسه یا پروسه به صورت خودکار از دسترس خارج می‌شود.

کاربردهای Temporary Table در SQL Server

Temporary Table‌ها در SQL Server برای مواقعی که نیاز به ذخیره موقت داده‌ها در طول یک جلسه (Session) یا به صورت جهانی (Global) دارید، مفید هستند. کاربردهای این نوع جداول شامل موارد زیر است:

  • ذخیره موقت نتایج کوئری: ممکن است نتایج یک کوئری به صورت موقت نیاز به ذخیره داشته باشند تا بتوانید بعداً از آنها در کوئری‌های دیگر استفاده کنید.
  • استفاده در فرآیندهای موازی: در برخی سناریوها، ممکن است نیاز به ایجاد Temporary Table در یک جلسه و استفاده از آن در جلسات دیگر یا پرس و جوهای موازی باشد.
  • ذخیره موقت داده‌ها برای پردازش بعدی: اگر نیاز به انجام چندین مرحله پردازش روی یک مجموعه داده دارید، می‌توانید نتایج هر مرحله را در یک Temporary Table ذخیره و برای مراحل بعدی استفاده کنید.
  • استفاده در پروسه‌های ذخیره شده (Stored Procedures): Temporary Table‌ها به عنوان ساختاری موقت برای اجرا درونی یا برگشتی در پروسه‌های ذخیره شده استفاده می‌شوند.
  • حل مشکلات پایداری و انتقال داده: Temporary Table‌ها می‌توانند به انتقال داده‌ها بین مراحل مختلف یک فرآیند یا دسترسی به موارد خاصی کمک کنند.
  • نگهداری موقت اطلاعات فیلتر شده: در صورتی که کوئری یا پروسه‌ای نیاز به فیلتر یا مرتب‌سازی داده‌ها دارد ولی نمی‌خواهید این تغییرات را در جدول اصلی اعمال کنید، می‌توانید از Temporary Table برای نگهداری موقت نتایج استفاده کنید.

با این حال، باید با دقت از Temporary Table‌ها استفاده کنید، زیرا ممکن است منجر به افزایش مصرف منابع سیستم شود. همچنین، مطمئن شوید که Temporary Table‌ها پس از استفاده به درستی حذف شوند تا جلوی ایجاد مشکلات پایداری گرفته شود.

بررسی Local Temp Table در SQL Server

Local Temporary Table در SQL Server نوعی از Temporary Table است که به مدت زمان یک جلسه (Session) وجود دارد و با علامت # شروع می‌شود. این جدول تنها در جلسه فعلی قابل استفاده است و پس از اتمام جلسه به صورت خودکار حذف می‌شود.

مثال زیر یک Local Temporary Table به نام #LocalTempTable با دو ستون ایجاد می‌کند:

CREATE TABLE #LocalTempTable
(
    ID INT,
    Name NVARCHAR(50)
);

بررسی Global Temp Table در SQL Server

Global Temporary Table در SQL Server نوعی از Temporary Table است که با علامت ## شروع می‌شود و به مدت زمانی که حداقل یک جلسه از آن استفاده می‌کند، موجود می‌ماند. پس از پایان آخرین جلسه، به صورت خودکار حذف می‌شود.

مثال زیر یک Global Temporary Table به نام ##GlobalTempTable با دو ستون ایجاد می‌کند:

CREATE TABLE ##GlobalTempTable
(
    ID INT,
    Name NVARCHAR(50)
);

بررسی Table Variable در SQL Server

Table Variable نوعی متغیر در SQL Server است که برای ذخیره داده‌های موقت درون یک جدول کوچک مورد استفاده قرار می‌گیرد. برخی ویژگی‌های Table Variable شامل موارد زیر است:

  • تعریف متغیر: برای تعریف یک Table Variable از دستور DECLARE و کلمه کلید TABLE استفاده می‌شود.
  • نحوه استفاده: Table Variable‌ها می‌توانند درون دستورات SELECT، INSERT، UPDATE، DELETE و… به عنوان منبع داده موقت مورد استفاده قرار گیرند.
  • دامنه دسترسی: Table Variable‌ها تنها در دسترس جلسه‌ای هستند که در آن تعریف شده‌اند.
  • عملکرد: در برخی موارد، Table Variable‌ها نسبت به Temporary Table‌ها با عملکرد بهتری شناخته می‌شوند.
  • دوره زندگی: Table Variable‌ها تا زمان اتمام جلسه SQL Server در دسترس هستند.
  • استفاده در توابع: می‌توان Table Variable‌ها را در توابع مورد استفاده قرار داد.

مثال زیر نحوه تعریف یک Table Variable را نشان می‌دهد:

DECLARE @MyTableVariable TABLE
(
    ID INT,
    Name NVARCHAR(50)
);

بررسی Table Variable Parameter در SQL Server

Table Variable Parameter یک نوع پارامتر است که می‌تواند به یک Stored Procedure (پروسیجر ذخیره‌شده) در SQL Server ارسال شود. این نوع پارامتر از نوع Table Variable استفاده می‌کند و به صورت یک جدول موقت در داخل پروسیجر به کار می‌رود. این امکان، به توسعه‌دهندگان اجازه می‌دهد که مجموعه‌های داده را به عنوان ورودی به پروسیجر ارسال کنند.

ویژگی‌های Table Variable Parameter

  • تعریف پارامتر: برای تعریف یک Table Variable Parameter در داخل یک Stored Procedure، از نوع READONLY و Table استفاده می‌شود. مثال زیر یک پروسیجر با یک Table Variable Parameter نشان می‌دهد:
    CREATE PROCEDURE dbo.MyProcedure
    @MyTableParameter TABLE
    (
        ID INT,
        Name NVARCHAR(50)
    )
AS
BEGIN
    -- اجرای دستورات با استفاده از @MyTableParameter
END;
  • استفاده در دستورات پروسیجر: Table Variable Parameter می‌تواند به عنوان یک جدول موقت داخل دستورات پروسیجر مورد استفاده قرار گیرد. برای مثال، می‌توانید از آن به عنوان یک جدول موقت در دستورات SELECT، INSERT، UPDATE یا DELETE استفاده کنید.
  • تحمل داده‌های متغیر: Table Variable Parameter می‌تواند به عنوان یک جایگزین برای ارسال مجموعه‌های داده به پروسیجر استفاده شود. این امکان به توسعه‌دهندگان اجازه می‌دهد تا بدون نیاز به تعریف نوع‌های داده سفارشی یا Temporary Table، مجموعه‌های داده را به پروسیجر ارسال کنند.
  • استفاده در JOIN و عملیات دستورات: Table Variable Parameter می‌تواند به عنوان یک جدول در JOIN ها و دستورات SELECT، INSERT، UPDATE و DELETE استفاده شود. این امکان به توسعه‌دهندگان امکان انجام عملیات‌های پیچیده بر روی مجموعه‌های داده را در داخل پروسیجر فراهم می‌کند.
  • تعامل با داده‌های ارسالی: پروسیجر می‌تواند به داده‌های ارسالی از طریق Table Variable Parameter دسترسی داشته باشد و عملیات‌های مختلف را بر روی آنها انجام دهد.

مثال زیر نحوه تعریف و استفاده از یک Table Variable Parameter در یک پروسیجر ذخیره‌ شده را نشان می‌دهد:

CREATE PROCEDURE dbo.InsertData
    @EmployeeData TABLE
    (
        EmployeeID INT,
        FirstName NVARCHAR(50),
        LastName NVARCHAR(50)
    )
AS
BEGIN
    INSERT INTO Employees (EmployeeID, FirstName, LastName)
    SELECT * FROM @EmployeeData;
END;

در این مثال، یک پروسیجر به نام InsertData با یک Table Variable Parameter به نام @EmployeeData تعریف شده است. این پروسیجر می‌تواند داده‌های ارسالی را به جدول Employees درج کند.

استفاده از Table Variable Parameter یک راه مناسب برای انتقال مجموعه‌های داده به پروسیجرهاست و از ایجاد Temporary Table یا دستورات INSERT مستقیم استفاده کرد. با این حال، برای مواقعی که می‌خواهید تغییرات در داده‌ها پس از اجرای پروسیجر نیز حفظ شود، ممکن است Temporary Table مزیت داشته باشد.

تأثیر استفاده از Table Variable بر کارایی بانک اطلاعاتی در SQL Server

استفاده از Table Variable در SQL Server تأثیرات مختلفی بر کارایی بانک اطلاعاتی می‌تواند داشته باشد. این تأثیرات به وابستگی از عوامل مختلفی نظیر حجم داده‌ها، میزان تعداد رکوردها، نحوه استفاده از Table Variable، و بهینه‌سازی کوئری‌ها متغیر است. در زیر، برخی از تأثیرات ممکن آورده شده‌اند:

  1. منابع حافظه: Table Variable در حافظه RAM ایجاد می‌شود و در بسیاری از مواقع بهینه‌تر از Temporary Table که به دیسک نوشته می‌شود، می‌باشد. این ممکن است باعث افزایش کارایی شود، به خصوص در مواردی که مصرف حافظه مهم است.
  2. کش‌های کوئری: در بعضی مواقع، استفاده از Table Variable می‌تواند باعث بهبود استفاده از کش‌های کوئری شود. از آنجایی که Table Variable در حافظه ایجاد می‌شود، این کش‌ها می‌توانند به صورت بهینه‌تری کار کنند.
  3. توزیع کوئری: استفاده از Table Variable می‌تواند باعث بهبود توزیع کوئری شود. Table Variable معمولاً به صورت موازی ایجاد می‌شود که می‌تواند منجر به بهترین استفاده از توانایی‌های موازی سازی سرور شود.
  4. بلوک‌های تراکنش: استفاده از Table Variable در بعضی مواقع می‌تواند منجر به کاهش نیاز به بلوک‌های تراکنش (Transaction Blocks) شود، که ممکن است در کارایی کوئری‌ها تأثیر داشته باشد. به این دلیل که Table Variable برای مدت زمان اجرای یک کوئری یا پروسیجر ذخیره‌شده وجود دارد و پس از اجرای بلوک تراکنش به صورت خودکار حذف می‌شود.
  5. حجم داده: – برای حجم داده‌های بزرگ، استفاده از Table Variable ممکن است منجر به مصرف حافظه بیشتر شود. در چنین مواردی، بهینه‌تر است از Temporary Table با امکان استفاده از حافظه و دیسک به صورت هوشمندانه استفاده کنید.
  6. استفاده در JOIN و GROUP BY: در برخی مواقع، استفاده از Table Variable در JOIN و GROUP BY می‌تواند بهترین عملکرد را داشته باشد و باعث بهبود کارایی این نوع عملیات‌ها شود.

با توجه به موارد بالا، باید تصمیم‌گیری در مورد استفاده از Table Variable یا دیگر روش‌های موقت (مانند Temporary Table) با توجه به نیازها و ویژگی‌های خاص هر پروژه انجام شود. همچنین، بهینه‌سازی کوئری‌ها و مدیریت منابع به صورت صحیح نیز تأثیر زیادی در بهبود کارایی دارد.

TVP در SQL Server چیست؟

TVP (Table-Valued Parameter) یک ویژگی در SQL Server است که به شما اجازه می‌دهد یک جدول موقت را به عنوان ورودی به یک پروسیجر ذخیره‌شده ارسال کنید. این ویژگی به صورت خاص برای ارسال و استفاده از مجموعه‌های داده به عنوان ورودی به پروسیجرها طراحی شده است و در بسیاری از مواقع می‌تواند جایگزینی بهینه‌تر نسبت به روش‌های دیگر (مانند استفاده از رشته‌ها یا Temporary Tables) باشد.

نحوه تعریف یک User-Defined Table Type:

CREATE TYPE dbo.EmployeeType AS TABLE
(
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
);

استفاده از User-Defined Table Type در یک پروسیجر:

CREATE PROCEDURE dbo.InsertEmployees
    @Employees dbo.EmployeeType READONLY
AS
BEGIN
    -- اجرای عملیات با استفاده از @Employees
    INSERT INTO Employees (EmployeeID, FirstName, LastName)
    SELECT EmployeeID, FirstName, LastName FROM @Employees;
END;

در این مثال، @Employees یک پارامتر TVP است که به عنوان ورودی به پروسیجر InsertEmployees ارسال می‌شود. این پروسیجر عملیات INSERT را بر روی جدول Employees با استفاده از داده‌های موجود در TVP انجام می‌دهد.

مزایای استفاده از TVP عبارتند از:

  • عدم نیاز به Temporary Tables: با استفاده از TVP، نیاز به ایجاد Temporary Table برای ارسال داده‌ها به پروسیجرها کاهش می‌یابد.
  • عملکرد بهتر: TVP ممکن است در مواقعی که نیاز به عملیات بر روی مجموعه داده دارید، عملکرد بهتری داشته باشد.
  • کد بهینه‌تر: کد برنامه نویسی با استفاده از TVP ممکن است کوتاه‌تر و خواناتر باشد.
  • توزیع کوئری بهبود یافته: TVP می‌تواند توزیع بهتری از کوئری‌ها را فراهم کند و به موازی‌سازی عملیات‌ها کمک کند.

تأثیر استفاده از TVP بر کارایی بانک اطلاعاتی SQL Server

استفاده از TVP (Table-Valued Parameter) در SQL Server می‌تواند تأثیرات مختلفی بر کارایی بانک اطلاعاتی داشته باشد. این تأثیرات به وابستگی از عوامل مختلفی نظیر حجم داده‌ها، تعداد رکوردها، نحوه استفاده از TVP و بهینه‌سازی کوئری‌ها متغیر است. در زیر، برخی از تأثیرات ممکن آورده شده‌اند:

  1. منابع حافظه: استفاده از TVP معمولاً بهینه‌تر از Temporary Tables است زیرا داده‌ها در حافظه ایجاد می‌شوند. این ممکن است منجر به کاهش مصرف حافظه و افزایش کارایی باشد.
  2. عملیات تراکنش: استفاده از TVP می‌تواند منجر به کاهش نیاز به بلوک‌های تراکنش شود. با ارسال یک جدول موقت به پروسیجر، می‌توان از تعداد بلوک‌های تراکنش کاست و در نتیجه کارایی را بهبود بخشید.
  3. کاهش ترافیک شبکه: با ارسال مجموعه داده به عنوان TVP به جای ارسال داده‌ها به صورت جداگانه، می‌توان از ترافیک شبکه کاست که می‌تواند در مواقعی با حجم داده‌های زیاد مفید باشد.
  4. کوئری‌های موازی: TVP می‌تواند توزیع بهتری از کوئری‌ها را فراهم کند و به موازی‌سازی عملیات‌ها کمک کند که می‌تواند کارایی را افزایش دهد.
  5. تحلیل نقاط ضعف: استفاده از TVP می‌تواند به تحلیل نقاط ضعف در کوئری‌ها کمک کند و امکان بهینه‌سازی درخواست‌ها را فراهم کند.
  6. کد کوتاه‌تر و خواناتر: استفاده از TVP ممکن است باعث کاهش تعداد خطوط کد و افزایش خوانایی کد شود، که می‌تواند در نگهداری و توسعه سیستم تأثیر مثبتی داشته باشد.

هر چند که استفاده از TVP مزایای زیادی دارد، اما باید توجه داشت که در برخی مواقع ممکن است بهتر باشد از روش‌های دیگر (مانند Temporary Tables یا استفاده از رشته‌ها) استفاده کرد. بهینه‌سازی کوئری‌ها، تست و اندازه‌گیری کارایی باید در هر مورد خاص در نظر گرفته شوند.

FAQ — سوالات متداول

تفاوت اصلی بین Temporary Table و Table Variable چیست؟

Temporary Table روی دیسک ذخیره می‌شود و قابلیت‌های بیشتری مانند ایجاد ایندکس دارد؛ در حالی که Table Variable بیشتر در حافظه اجرا می‌شود و برای داده‌های سبک‌تر مناسب است.

آیا Table Variable سریع‌تر از Temporary Table است؟

در بیشتر موارد با داده‌های کم، Table Variable سریع‌تر است؛ ولی با حجم بالای داده، Temporary Table بهتر عمل می‌کند.

آیا می‌توان از TVP در کوئری‌های JOIN استفاده کرد؟

بله، TVP مانند یک جدول عادی می‌تواند در عملیات JOIN، GROUP BY و سایر دستورات SQL استفاده شود.

آیا TVP از ایندکس پشتیبانی می‌کند؟

خیر، TVP نمی‌تواند ایندکس داشته باشد ولی همچنان برای پردازش سریع‌تر داده‌ها در پروسیجرها بسیار مناسب است.

چه زمانی استفاده از TVP توصیه نمی‌شود؟

وقتی نیاز دارید تغییراتی در جدول انجام دهید که در خارج از پروسیجر ذخیره بماند، بهتر است از Temporary Table استفاده کنید.

 

ارتباط و مشاوره

آیا نیاز به مشاوره در طراحی و بهینه‌سازی بانک اطلاعاتی خود دارید؟

با تیم متخصص لاندا تماس✆ بگیرید
جهت مشاوره، اجرا، بهینه‌سازی و آموزش تخصصی SQL Server آماده خدمت رسانی به شما هستیم.

روزبه امیرعصامی

SQL Server

SQL Serverاسکیوال سرورایندکسباز طراحی دیتابیسبهینه سازی کوئریپایگاه دادهطراحی دیتابیس

Xلینکدینواتساپتلگرامایمیل

نوشته های مرتبط

نظری داده نشده

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *