راهنمای متغیرها در T-SQL SQL Server بهینه سازی کوئری

بهینه‌سازی کوئری‌های SQL Server: راهنمای جامع متغیرها در T-SQL

متغیرهای SQL ابزارهایی حیاتی در کدنویسی T-SQL هستند که امکان ذخیره موقت مقادیر داده‌ای را فراهم می‌کنند. این مقادیر را می‌توان در عبارات، کوئری‌ها یا سایر قسمت‌های اسکریپت استفاده کرد. استفاده از متغیرها به بهبود خوانایی کد، افزایش کارایی و قابلیت نگهداری کمک می‌کند.

**تعریف متغیرها در SQL Server**

برای استفاده از یک متغیر در T-SQL، ابتدا باید آن را با استفاده از دستور `DECLARE` تعریف کنید. هر متغیر باید با علامت `@` شروع شود، یک نام منحصر به فرد داشته باشد و نوع داده‌ای مشخصی به آن اختصاص یابد.

مثال ساده تعریف یک متغیر:

DECLARE @myvariable INT;

شما می‌توانید چندین متغیر را به طور همزمان در یک دستور `DECLARE` تعریف کنید و حتی در زمان تعریف، مقداری اولیه به آن‌ها اختصاص دهید:

DECLARE @firstName VARCHAR(50), @lastName VARCHAR(50), @age INT = 30;

اگر متغیری را مقداردهی اولیه نکنید، مقدار پیش‌فرض آن `NULL` خواهد بود. همچنین می‌توانید چندین متغیر را در یک دستور `DECLARE` با مقادیر اولیه یا بدون آن تعریف کنید.

**اختصاص مقادیر به متغیرها**

پس از تعریف یک متغیر، می‌توانید با استفاده از دستورات `SET` یا `SELECT` به آن مقدار اختصاص دهید.

**استفاده از `SET`:**
دستور `SET` روشی متداول برای اختصاص یک مقدار به یک متغیر است. این روش برای اختصاص یک مقدار واحد به یک متغیر در هر بار استفاده می‌شود.

SET @myvariable = 100;

مثال کامل‌تر:

DECLARE @firstName VARCHAR(50);
DECLARE @lastName VARCHAR(50);
SET @firstName = 'Jane';
SET @lastName = 'Doe';
SELECT @firstName + ' ' + @lastName AS FullName;

**استفاده از `SELECT`:**
دستور `SELECT` نیز می‌تواند برای اختصاص مقادیر به متغیرها به کار رود. این روش زمانی مفید است که می‌خواهید مقداری را از نتیجه یک کوئری به متغیر اختصاص دهید. در صورت بازگرداندن چندین ردیف توسط کوئری، فقط مقدار آخرین ردیف به متغیر اختصاص می‌یابد. اگر کوئری هیچ ردیفی باز نگرداند، متغیر `NULL` باقی می‌ماند.

SELECT @myvariable = ColumnValue FROM MyTable WHERE ID = 1;

مثال با استفاده از `SELECT` برای اختصاص مقادیر:

DECLARE @MaxCustomerID INT;
SELECT @MaxCustomerID = MAX(CustomerID) FROM Customers;
SELECT 'Maximum Customer ID is: ' + CAST(@MaxCustomerID AS VARCHAR(10));

**محدوده (Scope) متغیرها**

متغیرهای SQL به “دسته” (batch) که در آن تعریف شده‌اند، محدود می‌شوند. این بدان معناست که یک متغیر فقط در همان دسته کدی که `DECLARE` شده، قابل دسترسی است. اگر دستور `GO` را در کد خود استفاده کنید، آن را به دسته‌های جداگانه تقسیم می‌کنید و متغیرهای تعریف شده در یک دسته پس از `GO` در دسته بعدی قابل دسترسی نخواهند بود.

مثال برای نشان دادن محدوده متغیر:

DECLARE @myvariable INT = 10;
SELECT @myvariable;
GO
-- این دستور خطا می دهد زیرا @myvariable در این دسته جدید تعریف نشده است.
SELECT @myvariable;

**متغیرهای جدولی (Table Variables) در مقابل جداول موقت (Temporary Tables)**

SQL Server دو روش برای ذخیره موقت مجموعه‌ای از داده‌ها ارائه می‌دهد: متغیرهای جدولی و جداول موقت. هر دو کاربردهای خاص خود را دارند و انتخاب بین آن‌ها به سناریوی شما بستگی دارد.

**متغیرهای جدولی (Table Variables):**
یک متغیر جدولی با استفاده از دستور `DECLARE` و نوع داده‌ای `TABLE` تعریف می‌شود. این متغیرها در حافظه نگهداری می‌شوند (البته ممکن است به tempdb منتقل شوند اگر حجم داده‌ها زیاد باشد) و مانند یک متغیر اسکالر، دارای محدوده دسته هستند.

مزایای متغیرهای جدولی:
* محدوده دسته: به محض اتمام دسته، به طور خودکار حذف می‌شوند.
* تراکنش‌پذیری کمتر: Rollbackهای تراکنش بر روی آن‌ها اعمال نمی‌شود که می‌تواند در برخی موارد مفید باشد.
* کاهش قفل‌گذاری: سربار قفل‌گذاری کمتری دارند.

نحوه تعریف و استفاده از یک متغیر جدولی:

DECLARE @MyTableVariable TABLE
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50),
    Value INT
);

INSERT INTO @MyTableVariable (Name, Value)
VALUES ('Item A', 10), ('Item B', 20);

SELECT * FROM @MyTableVariable;

**جداول موقت (Temporary Tables):**
جداول موقت با استفاده از دستور `CREATE TABLE` و پیشوند `#` برای جدول موقت محلی یا `##` برای جدول موقت جهانی ایجاد می‌شوند. جداول موقت در پایگاه داده `tempdb` ذخیره می‌شوند و محدوده آن‌ها می‌تواند از یک دسته فراتر رود (برای جداول موقت محلی تا پایان نشست و برای جداول موقت جهانی تا زمان بسته شدن آخرین اتصال استفاده‌کننده).

مزایای جداول موقت:
* پشتیبانی کامل از ایندکس‌ها، آماره‌ها و محدودیت‌ها.
* قابل استفاده در چندین دسته (Local Temp Table در یک session، Global Temp Table در تمام sessions).
* مناسب برای حجم داده‌های بزرگ و کوئری‌های پیچیده‌تر.

نحوه تعریف و استفاده از یک جدول موقت:

CREATE TABLE #MyTempTable
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50),
    Value INT
);

INSERT INTO #MyTempTable (Name, Value)
VALUES ('Product X', 100), ('Product Y', 200);

SELECT * FROM #MyTempTable;

DROP TABLE #MyTempTable; -- توصیه می شود برای پاکسازی صریح

**تفاوت‌های کلیدی:**
* **محدوده:** متغیرهای جدولی به دسته، جداول موقت محلی به نشست، جداول موقت جهانی به سطح سرور.
* **فضای ذخیره‌سازی:** متغیرهای جدولی عمدتاً در حافظه (در صورت لزوم `tempdb` استفاده می‌شود)، جداول موقت همیشه در `tempdb`.
* **ایندکس‌ها/آماره‌ها:** متغیرهای جدولی فقط می‌توانند `PRIMARY KEY` یا `UNIQUE` داشته باشند، جداول موقت از تمام انواع ایندکس‌ها و آماره‌ها پشتیبانی می‌کنند.
* **Recompilation:** متغیرهای جدولی باعث recompilation کمتری می‌شوند، جداول موقت می‌توانند باعث recompilation کوئری‌ها شوند.
* **تراکنش:** جداول موقت تحت تأثیر `ROLLBACK` تراکنش‌ها قرار می‌گیرند، متغیرهای جدولی خیر.

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