آموزش جامع متغیرهای جدولی (Table Variables) در SQL Server
مقدمه
متغیرهای جدولی (Table Variables) یکی از ابزارهای مهم طراحی پردازش میانی در Microsoft SQL Server است. مسئله اصلی صرفاً نوشتن یک دستور نیست؛ باید بدانیم داده کجا نگهداری میشود، چه کسی آن را میبیند، Optimizer چه اطلاعاتی برای برآورد دارد و پاکسازی در چه زمانی رخ میدهد.
متغیر جدولی با DECLARE و نوع table در دامنه Batch، تابع یا Stored Procedure تعریف میشود. این ساختار برای مجموعههای کوچک و قراردادهای جدولی محدود، کد فشرده و دامنه روشن فراهم میکند. با این حال نبود آمار توزیعی و محدودیت تغییر ساختار میتواند در حجمهای بزرگ، Joinهای پیچیده و تصمیمهای موازیسازی به برآورد نامناسب منجر شود.
در این راهنما از مثالهای کوچک آغاز میکنیم و سپس به دامنه، NULL، تراکنش، ایندکس، خطاهای رایج و سناریوهای Performance میرسیم. تمام Queryها برای آزمایش در محیط کنترلشده نوشته شدهاند و مثالهای دارای پیشنیاز، آن پیشنیاز را صریح اعلام میکنند.
برای مقایسه این ساختار با گزینههای دیگر، راهنمای جامع اشیای موقت در SQL Server را نیز مطالعه کنید.
تعریف و معماری متغیر جدولی
متغیر جدولی با DECLARE و نوع table در دامنه Batch، تابع یا Stored Procedure تعریف میشود. این ساختار برای مجموعههای کوچک و قراردادهای جدولی محدود، کد فشرده و دامنه روشن فراهم میکند. با این حال نبود آمار توزیعی و محدودیت تغییر ساختار میتواند در حجمهای بزرگ، Joinهای پیچیده و تصمیمهای موازیسازی به برآورد نامناسب منجر شود.
انتخاب صحیح زمانی رخ میدهد که سه محور را همزمان ببینیم: تعداد و توزیع ردیفها، مرز دسترسی نشست یا Batch، و تعداد دفعات خواندن و نوشتن. یک ساختار ساده در حجم کم ممکن است بهترین باشد، اما همان ساختار در گزارش چندمیلیونی میتواند به برآورد Cardinality نامناسب یا فشار منابع منجر شود.
همچنین باید تفاوت میان عمر منطقی داده و عمر فیزیکی شیء را درک کرد. SQL Server بخشی از عملیات را Cache میکند و این موضوع به معنای دائمی شدن داده نیست. قرارداد برنامه باید بر رفتار مستند دامنه و تراکنش تکیه کند، نه بر مشاهده اتفاقی یک اجرای آزمایشی.
Syntax استاندارد
DECLARE @Items TABLE
(
ItemID INT NOT NULL PRIMARY KEY,
Title NVARCHAR(100) NOT NULL,
Amount DECIMAL(12,2) NULL
);
INSERT INTO @Items(ItemID,Title,Amount) VALUES(1,N'نمونه',125.00);
SELECT ItemID,Title,Amount FROM @Items;
پارامترها و اجزای تعریف
- نام متغیر با @ شروع میشود و فقط در دامنه تعریفشده معتبر است.
- ستونها، نوع داده و قیدهای کلیدی هنگام DECLARE تعیین میشوند و ALTER TABLE بعدی در دسترس نیست.
- ایندکسهای مورد نیاز را با PRIMARY KEY، UNIQUE یا تعریف inline سازگار با نسخه ایجاد کنید.
- برای حجم و Join مهم، سطح سازگاری 150 و Table Variable Deferred Compilation را بررسی کنید.
نوع خروجی و نحوه مصرف
متغیر جدولی یک ظرف رابطهای با Schema ثابت است، نه مقدار برگشتی اسکالر. میتوان آن را در SELECT، JOIN، INSERT، UPDATE و DELETE همان دامنه به کار برد و نوع جدول تعریفشده توسط کاربر را نیز بهعنوان TVP به رویه ارسال کرد.
چه زمانی متغیر جدولی را انتخاب کنیم؟
کاربردهای مناسب شامل نگهداری چند ردیف تنظیم یا کلید در یک Batch کوتاه، ثبت خروجی محدود دستور با INSERT ... EXEC یا پردازش مرحلهای کوچک، پیادهسازی توابع جدولی چنددستوری در موارد ضروری، ارسال مجموعهداده ساختیافته به Stored Procedure از طریق Table-Valued Parameter است. با این حال، مناسب بودن از نام قابلیت نتیجه نمیشود؛ باید Query مصرفکننده و تعداد اجرای همزمان را نیز تحلیل کرد.
- نگهداری چند ردیف تنظیم یا کلید در یک Batch کوتاه
- ثبت خروجی محدود دستور با INSERT ... EXEC یا پردازش مرحلهای کوچک
- پیادهسازی توابع جدولی چنددستوری در موارد ضروری
- ارسال مجموعهداده ساختیافته به Stored Procedure از طریق Table-Valued Parameter
برای تصمیمگیری، یک نمونه با داده واقعی بسازید، Baseline ثبت کنید و گزینه رقیب را با همان ورودی اجرا کنید. تعداد Logical Read، CPU، Duration و اختلاف Estimated Rows با Actual Rows را مقایسه کنید. اگر SLA همزمانی مهم است، تست بار چندنشستی نیز اجباری است.
مثالهای عملی مستقل
ده سناریوی زیر جنبههای متفاوت متغیر جدولی را پوشش میدهند. هر مثال هدف، Query، خروجی مورد انتظار و نکته اجرایی مستقل دارد.
مثال 1: اعلان و خواندن پایه
دو محصول را در متغیر جدولی قرار میدهیم و مرتب نمایش میدهیم.
DECLARE @Products TABLE(ProductID INT PRIMARY KEY,Title NVARCHAR(40));
INSERT INTO @Products VALUES(2,N'نمایشگر'),(1,N'صفحهکلید');
SELECT ProductID,Title FROM @Products ORDER BY ProductID;
| ProductID | Title |
|---|
| 1 | صفحهکلید |
| 2 | نمایشگر |
نکته کاربردی: دامنه متغیر با پایان Batch تمام میشود و DROP TABLE لازم نیست.
مثال 2: درج از داده نمونه
فقط رخدادهای فعال را با INSERT SELECT وارد متغیر میکنیم.
DECLARE @Active TABLE(ID INT,StateName NVARCHAR(20));
WITH S AS(SELECT * FROM (VALUES(1,N'فعال'),(2,N'غیرفعال'),(3,N'فعال'))v(ID,StateName))
INSERT INTO @Active SELECT ID,StateName FROM S WHERE StateName=N'فعال';
SELECT COUNT(*) AS ActiveCount FROM @Active;
نکته کاربردی: برخلاف #Table، متغیر جدولی هدف SELECT INTO نیست و باید پیش از INSERT تعریف شود.
مثال 3: تجمیع در SELECT
هزینههای محدود یک پروژه را جمع و میانگینگیری میکنیم.
DECLARE @Costs TABLE(ProjectID INT,Amount DECIMAL(12,2));
INSERT INTO @Costs VALUES(10,100.00),(10,300.00),(20,500.00);
SELECT ProjectID,SUM(Amount) AS TotalAmount,AVG(Amount) AS AverageAmount FROM @Costs GROUP BY ProjectID ORDER BY ProjectID;
| ProjectID | TotalAmount | AverageAmount |
|---|
| 10 | 400.00 | 200.00 |
| 20 | 500.00 | 500.00 |
نکته کاربردی: برای چند ردیف محلی، متغیر جدولی خوانایی خوبی دارد؛ در داده بزرگ طرح را با #Table مقایسه کنید.
مثال 4: فیلتر شرطی
کارهای با اولویت بالا را بدون تابع روی ستون فیلتر میکنیم.
DECLARE @Tasks TABLE(TaskID INT,PriorityNo TINYINT,Title NVARCHAR(30));
INSERT INTO @Tasks VALUES(1,1,N'پشتیبانگیری'),(2,3,N'گزارش'),(3,2,N'بازبینی');
SELECT TaskID,Title FROM @Tasks WHERE PriorityNo<=2 ORDER BY PriorityNo;
| TaskID | Title |
|---|
| 1 | پشتیبانگیری |
| 3 | بازبینی |
نکته کاربردی: شرط ساده و مستقیم فهم Query را بالا میبرد و در ساختارهای دارای کلید با مسیر دسترسی سازگار است.
مثال 5: کلید ترکیبی inline
برای جلوگیری از تکرار کالا در هر سفارش، کلید اصلی ترکیبی تعریف میکنیم.
DECLARE @Lines TABLE(OrderID INT,ProductID INT,Qty INT,PRIMARY KEY(OrderID,ProductID));
INSERT INTO @Lines VALUES(100,1,2),(100,2,1),(101,1,4);
SELECT OrderID,SUM(Qty) AS TotalQty FROM @Lines GROUP BY OrderID ORDER BY OrderID;
نکته کاربردی: قید inline هم صحت داده را تضمین میکند و هم ساختار ایندکس لازم را میسازد.
مثال 6: رفتار NULL
مبلغ اختیاری را با حفظ معنای نامشخص و نمایش جایگزین گزارش میکنیم.
DECLARE @Payments TABLE(ID INT,Amount DECIMAL(10,2) NULL);
INSERT INTO @Payments VALUES(1,NULL),(2,80.00);
SELECT ID,COALESCE(CONVERT(NVARCHAR(30),Amount),N'ثبت نشده') AS AmountText FROM @Payments ORDER BY ID;
| ID | AmountText |
|---|
| 1 | ثبت نشده |
| 2 | 80.00 |
نکته کاربردی: نوع خروجی COALESCE تابع تقدم نوعهاست؛ تبدیل صریح، خروجی متنی قابل پیشبینی ایجاد میکند.
مثال 7: بهروزرسانی هدفمند
وضعیت اقلام پردازششده را در متغیر جدولی تغییر میدهیم.
DECLARE @Queue TABLE(ID INT PRIMARY KEY,IsDone BIT);
INSERT INTO @Queue VALUES(1,0),(2,0),(3,0);
UPDATE @Queue SET IsDone=1 WHERE ID IN(1,3);
SELECT COUNT(*) AS DoneCount FROM @Queue WHERE IsDone=1;
نکته کاربردی: DML روی @Table معتبر است، اما برای تغییرات حجیم محدودیت طرح موازی را در نظر بگیرید.
مثال 8: کامپایل متناسب با تعداد
با OPTION(RECOMPILE) به Query اجازه میدهیم تعداد فعلی ردیفها را هنگام کامپایل ببیند.
DECLARE @IDs TABLE(ID INT PRIMARY KEY);
INSERT INTO @IDs VALUES(1),(2),(3),(4);
SELECT i.ID FROM @IDs AS i WHERE i.ID>=3 OPTION(RECOMPILE);
نکته کاربردی: RECOMPILE نسخه جادویی نیست؛ هزینه کامپایل و دفعات اجرا را همراه کیفیت طرح اندازهگیری کنید.
مثال 9: دامنه Dynamic SQL
روش صحیح این است که اعلان متغیر و مصرف آن هر دو داخل Batch پویا باشند.
DECLARE @Sql NVARCHAR(MAX)=N'DECLARE @Inside TABLE(ID INT); INSERT INTO @Inside VALUES(5),(6); SELECT COUNT(*) AS RowCount FROM @Inside;';
EXEC sys.sp_executesql @Sql;
نکته کاربردی: ارجاع در Dynamic SQL به @Table تعریفشده بیرون از آن با خطای Must declare the table variable روبهرو میشود.
مثال 10: مقایسه برای حجم بالاتر
یک مجموعه هزارردیفی میسازیم و تعداد انتخابی را با کامپایل مجدد محاسبه میکنیم.
DECLARE @Numbers TABLE(ID INT PRIMARY KEY);
WITH n AS(SELECT TOP(1000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS ID FROM sys.all_objects)
INSERT INTO @Numbers SELECT ID FROM n;
SELECT COUNT(*) AS SelectedCount FROM @Numbers WHERE ID BETWEEN 401 AND 600 OPTION(RECOMPILE);
نکته کاربردی: در پروژه واقعی همین Query را با #Table و STATISTICS IO/TIME مقایسه کنید؛ هزار ردیف قانون قطعی انتخاب نیست.
خطاهای رایج و روش اصلاح
خطای 1
فرض یکسان بودن رفتار @Table و #Table در برآورد Cardinality، انتخاب طرح را در حجم بالا خراب میکند. راه اصلاح این است که فرض را به یک آزمون قابل تکرار تبدیل کنید و رفتار را در نسخه و Compatibility Level مقصد بسنجید.
خطای 2
متغیر جدولی بیرون از sp_executesql در Batch پویا قابل مشاهده نیست؛ دامنه آن lexical است. راه اصلاح این است که فرض را به یک آزمون قابل تکرار تبدیل کنید و رفتار را در نسخه و Compatibility Level مقصد بسنجید.
خطای 3
SELECT INTO نمیتواند @Table را هدف قرار دهد و باید ابتدا Schema را DECLARE کرد. راه اصلاح این است که فرض را به یک آزمون قابل تکرار تبدیل کنید و رفتار را در نسخه و Compatibility Level مقصد بسنجید.
خطای 4
پس از DECLARE امکان ALTER TABLE و CREATE INDEX معمولی وجود ندارد؛ کلیدها را از ابتدا طراحی کنید. راه اصلاح این است که فرض را به یک آزمون قابل تکرار تبدیل کنید و رفتار را در نسخه و Compatibility Level مقصد بسنجید.
ملاحظات کارایی و بهینهسازی
متغیر جدولی برای تعداد کم ردیف و منطق ساده مناسب است؛ آستانه ثابت جهانی وجود ندارد و اندازهگیری لازم است.
سطح سازگاری 150 قابلیت Deferred Compilation را فراهم میکند تا کامپایل نخست با تعداد واقعی ردیفها انجام شود، ولی آمار توزیعی کامل ایجاد نمیکند.
OPTION(RECOMPILE) میتواند برای Query حساس به تعداد ردیف مفید باشد، اما هزینه کامپایل مجدد را باید سنجید.
اصلاح داده در @Table معمولاً طرح موازی تولید نمیکند؛ برای ETL بزرگ یا DML سنگین #Table را مقایسه کنید.
Actual Plan و اختلاف Estimated Rows با Actual Rows مهمترین علامت برای تشخیص انتخاب نامناسب است.
هیچ Hint یا نوع شیئی درمان همگانی نیست. Baseline را نگه دارید تا پس از تغییر بتوانید رگرسیون را تشخیص دهید. برای Queryهای حساس، Query Store و Actual Plan امکان مقایسه پایدارتر را فراهم میکنند و Wait Statistics نشان میدهد مشکل واقعاً CPU، I/O، قفل یا تخصیص است.
بهترین روشها
- Schema متغیر جدولی را باریک و صریح تعریف کنید و از ستونهای بلااستفاده دوری کنید.
- طول عمر، مالک ایجاد و مسئول پاکسازی را در طراحی و مستندات مشخص کنید.
- ایندکس را از روی Predicate، Join و Order By واقعی طراحی کنید، نه از روی حدس.
- داده حساس را متناسب با دامنه دید و مجوزها محافظت کنید.
- مسیر موفق، خطا، Rollback و اجرای همزمان را در تست خودکار پوشش دهید.
- پس از Upgrade یا تغییر Compatibility Level آزمون Performance را تکرار کنید.
- کد DDL و Query را در Source Control نگه دارید و تغییر را با برنامه بازگشت منتشر کنید.
سؤالات متداول
1. متغیر جدولی دقیقاً چیست و چه مسئلهای را حل میکند؟
متغیر جدولی با DECLARE و نوع table در دامنه Batch، تابع یا Stored Procedure تعریف میشود. این ساختار برای مجموعههای کوچک و قراردادهای جدولی محدود، کد فشرده و دامنه روشن فراهم میکند. با این حال نبود آمار توزیعی و محدودیت تغییر ساختار میتواند در حجمهای بزرگ، Joinهای پیچیده و تصمیمهای موازیسازی به برآورد نامناسب منجر شود. انتخاب آن باید از نیاز واقعی دامنه، حجم داده و نحوه مصرف شروع شود.
2. برای شروع کار با متغیر جدولی چه مراحلی لازم است؟
ابتدا Schema و طول عمر داده را مشخص کنید، سپس نمونه Syntax مقاله را در دیتابیس آزمایشی اجرا کنید و با داده شبیه تولید، صحت و طرح اجرا را بسنجید.
3. آیا استفاده از متغیر جدولی هزینه توسعه گزارش را کاهش میدهد؟
اگر ساختار برای چند مرحله پردازش مناسب باشد، کد سادهتر و عیبیابی سریعتر میشود. در پروژه تجاری بهتر است هزینه tempdb، نگهداری و همزمانی نیز در برآورد مشاوره لحاظ شود.
4. چه زمانی سرمایهگذاری روی بهینهسازی متغیر جدولی توجیه تجاری دارد؟
وقتی زمان پاسخ، مصرف CPU یا انتظار کاربران روی درآمد و SLA اثر دارد، ثبت Baseline و آزمایش کنترلشده میتواند ارزش تغییر را روشن کند؛ بهینهسازی بدون عدد قابل دفاع نیست.
5. متغیر جدولی چه تفاوتی با سایر اشیای موقت دارد؟
تفاوت اصلی در دامنه دید، طول عمر، آمار، ایندکس، مدل ذخیرهسازی و هزینه همزمانی است. جدول مقایسه مقاله مادر انتخاب میان #Table، ##Table، @Table و In-Memory را خلاصه میکند.
6. آیا میتوان طراحی متغیر جدولی را برای پروژه سازمانی سفارش داد؟
بله؛ در یک خدمت حرفهای، الگوی Query، حجم، Execution Plan، Wait Statistics و محدودیت نسخه بررسی میشود و اسکریپت مهاجرت و آزمون بازگشت نیز تحویل میگردد.
7. رایجترین خطای متغیر جدولی چیست؟
فرض یکسان بودن رفتار @Table و #Table در برآورد Cardinality، انتخاب طرح را در حجم بالا خراب میکند. برای جلوگیری، مسیر خطا را مانند مسیر موفقیت در تست خودکار پوشش دهید.
8. چگونه Performance متغیر جدولی را اندازه بگیریم؟
STATISTICS IO، STATISTICS TIME، Actual Execution Plan، مصرف tempdb یا XTP و زمان صدکی را قبل و بعد ثبت کنید. چند اجرای همزمان از تست تککاربره معتبرتر است.
9. بهترین روش استفاده از متغیر جدولی چیست؟
Schema باریک، نام روشن، مالکیت طول عمر، پاکسازی مشخص و ایندکس مبتنی بر Query واقعی اصول پایهاند. تصمیم نهایی باید با داده تولیدی و طرح اجرای واقعی تأیید شود.
10. متغیر جدولی با کدام نسخههای SQL Server سازگار است؟
اصل قابلیت در نسخههای پشتیبانیشده SQL Server موجود است، اما جزئیات بهبودهای Optimizer و In-Memory با نسخه و Compatibility Level فرق دارد. مستندات نسخه مقصد و آزمون رگرسیون را معیار قرار دهید.
سؤالات مصاحبه تخصصی
پرسش 1: دامنه و طول عمر متغیر جدولی را توضیح دهید و یک مورد استفاده مناسب نام ببرید.
پاسخ حرفهای باید علاوه بر تعریف، یک Trade-off و روش اندازهگیری ارائه کند. نام بردن از Actual Plan، IO، CPU، دامنه و آزمون همزمانی نشان میدهد داوطلب قابلیت را در پروژه واقعی فهمیده است.
پرسش 2: Optimizer برای متغیر جدولی چه اطلاعاتی در اختیار دارد و این موضوع چگونه بر Join اثر میگذارد؟
پاسخ حرفهای باید علاوه بر تعریف، یک Trade-off و روش اندازهگیری ارائه کند. نام بردن از Actual Plan، IO، CPU، دامنه و آزمون همزمانی نشان میدهد داوطلب قابلیت را در پروژه واقعی فهمیده است.
پرسش 3: اگر تعداد ردیفها صد برابر شود، چه شاخصهایی را پیش از تغییر طراحی بررسی میکنید؟
پاسخ حرفهای باید علاوه بر تعریف، یک Trade-off و روش اندازهگیری ارائه کند. نام بردن از Actual Plan، IO، CPU، دامنه و آزمون همزمانی نشان میدهد داوطلب قابلیت را در پروژه واقعی فهمیده است.
پرسش 4: نقش Index و هزینه نگهداری آن در این ساختار چیست؟
پاسخ حرفهای باید علاوه بر تعریف، یک Trade-off و روش اندازهگیری ارائه کند. نام بردن از Actual Plan، IO، CPU، دامنه و آزمون همزمانی نشان میدهد داوطلب قابلیت را در پروژه واقعی فهمیده است.
پرسش 5: چگونه Race Condition، پاکسازی و مسیر Rollback را آزمایش میکنید؟
پاسخ حرفهای باید علاوه بر تعریف، یک Trade-off و روش اندازهگیری ارائه کند. نام بردن از Actual Plan، IO، CPU، دامنه و آزمون همزمانی نشان میدهد داوطلب قابلیت را در پروژه واقعی فهمیده است.
پرسش 6: برای مهاجرت به گزینه دیگر چه Baseline و معیار پذیرشی تعریف میکنید؟
پاسخ حرفهای باید علاوه بر تعریف، یک Trade-off و روش اندازهگیری ارائه کند. نام بردن از Actual Plan، IO، CPU، دامنه و آزمون همزمانی نشان میدهد داوطلب قابلیت را در پروژه واقعی فهمیده است.
چکلیست نهایی
- هدف داده میانی و طول عمر آن مشخص است.
- حجم معمول، اوج و تعداد نشست همزمان اندازهگیری شده است.
- Schema و نوع داده بیش از نیاز بزرگ نیست.
- Predicate و Joinهای اصلی ایندکس مناسب دارند.
- Estimated Rows و Actual Rows مقایسه شدهاند.
- مسیر NULL، خطا و Rollback تست شده است.
- پاکسازی و مالکیت شیء روشن است.
- Baseline و برنامه بازگشت ثبت شده است.
جمعبندی
متغیرهای جدولی (Table Variables) زمانی ارزشمند است که ویژگیهای واقعی آن با مسئله هماهنگ باشد. دامنه، حجم، آمار، ایندکس و همزمانی را یک تصمیم واحد ببینید و به یک آزمایش سریع تکنشستی اکتفا نکنید.
از مثالهای این مقاله بهعنوان نقطه شروع استفاده کنید، سپس داده و Query پروژه خود را جایگزین کنید. نتیجه درست باید هم از نظر صحت کسبوکار و هم از نظر معیارهای Performance قابل دفاع باشد.
برای مرور همه گزینهها و انتخاب ساختار مناسب، به مقاله مادر اشیای موقت در SQL Server بازگردید.