آشنایی با انواع جداول (استاتیک - متغیر - موقتی ..)
سلام.
این دفعه اومدم تا کمی در رابطه با جداول صحبت کنم.
در SQL جدول (که می دونیم چیه!) در حالت نرمال خودش یک شی هست در دیتابیس ما و طبعا کارهایی رو هم میشه روش انجام داد (ایندکس - کلید - constraint ها - drop - ..) و کارهایی هم در اون (update - delete - truncate - ..)
یک نوع جدول (مجازی) دیگه هم وجود داره که View گفته میشه (از این لحاظ جدول در نظر گرفته شده که در Information_Schema.Tables جزو جداول ذکر شده)
در View نمی شه داده ها رو دستکاری کرد. در حقیقت یک select ترکیبی از جداول واقعی ماست که ممکنه فیلتر یا سورت هم شده باشه. میشه از اون select انجام داد و ما مواقعی از این جداول استفاده می کنیم که در select خودمون چندین join داشته باشیم. این join ها رو که معمولا همیشه اتفاق می افته (مثلا مستر- دیتیل ها) میشه در قالب یک View ساخت و از اون View داده ها رو select کرد.
در این حالت سرعت select هم بهبود پیدا می کنه. به دلیل اینکه در حافظه کش میشه و برای مدتی حضور داره.
محدودیت ها:
نمیشه داده های اون رو با دستورات DML تغییر داد. باید داده های جداولش رو تغییر داد.
نمیشه در یک دیتابیس یک View برای دیتابیس بغلی ساخت. اما میشه جدول ساخت.
نمیشه در اون از جدولی استفاده کرد که به Select اون دسترسی نداریم.
نمیشه روی View یک index یا trigger گذاشت.
داده های یک View نمی تونن از اشیای موقتی گرفته شده باشند.
کی ازش استفاده کنیم؟
روشی هست برای فاکتور گرفتن از join های متعدد در SP.
گاهی ممکنه کمکی به امنیت کنه. مثلا وقتی که SQL Inject انجام میشه برای پیدا کردن نام جداول مربوطه معطلی بزرگی به وجود میاره. امکان تغییر یا حذف یا ثبت داده جدید رو نمی ده. با حذف خودش هم داده ها همچنان وجود دارند.
وقتایی که می خواهیم رفتاری رو عوض کنیم بی اینکه کد برنامه رو عوض کنیم. مثلا جای همه داده ها 10 ردیف اول بیاد، یا مثلا توان دوم یک داده رو جای خودش استفاده کنیم، یا اینکه نام نمایشی فیلدی رو عوض کنیم.
گاهی برای یک select که به کرات استفاده میشه میخواهیم کامنتی بگذاربم. برای این مستند سازی کوچولو میشه Select رو یک view کرد و در view کامنت گذاشت و از اون استفاده کرد.
وقتایی که فیلدی محاسباتی داریم. مثلا دو ستون گل زده و گل خورده در جدول داریم. می خواهیم تفاضل بازی رو هم داشته باشیم. یا حتی وقتهایی که می خواهیم برای جدولمون شماره ردیف درست کنیم.
روش ساختش رو هم میگذاریم به عهده خودتون.
و اما جداول موقتی:
همون طوری که از اسمش بر میاد جدول هایی هستند که به طور فیزیکی در دیتابیس ما وجود ندارند.
این جدولها در دیتابیس TempDB زندگی می کنند و با قطع اتصال با سرور از بین می روند.
خود این جداول هم دو نوع دارند: محلی و سراسری.
تفاوت اونها هم در اینه که محلی ها (اسمشون با # شروع میشه) فقط در session جاری معتبرند ولی سراسری ها (اسمشون با ## شروع میشه) در تمام session ها هستند و این یعنی تمام کاربران متصل اون رو می بینند.
ویژگی ها:
جداول موقتی تمام دستورات DDL رو می پذیرند.(drop, index, alter, ...)
می توانند در تراکنش استفاده شوند و rollback یا commit شوند.
در توابع نمی توان از اونها استفاده کرد.
تست وجود یک جدول موقتی هم مثل جدول ساده هست با این فرق که در دیتابیس TempDB باید دنبالش بگردیم:
if exists(select 1 from TempDB..SysObjects where name = '#temptbl') blabla
کی از اون استفاده کنیم؟
گاهی لازمه داده ها رو در محلی موقتی نگه داریم. مثلا برای جمع آوری داده ها با شرایط گوناگون میشه یک جدول مجازی ساخت و هی تو اون insert کرد یا delete کرد.
گاهی لازمه برای گزارشاتی سرجمع نوشت که from بزرگی دارند.
برای این کار میشه به جای union کردن با from مشابه داده ها رو از select اول در جدول موقتی ریخت و از اون سرجمع رو ساخت.
گاهی union هایی داریم با from های مشابه. اگر قرار باشه شرایط where همه اونها عوض شه ممکنه یادمون بره و یکی از union ها جا بمونه. اگر از جدول مجازی استفاده کنیم فقط یک جا where داریم.
گاهی لازمه یک اسکریپت از خروجی میانی یک اسکریپت دیگه (منظورم SP هست) مطلع باشه یا از اون استفاده کنه.
در اینجا هم جدول موقتی می تونه راهگشا باشه.
ممکنه یک خروجی موقت قابل اصلاح بخواهید داشته باشید. مثلا پیش نمایش سند حسابداری. در اونجا هم وجود این جدولها مفیده.
و مثالهایی که الان خاطرم نیست.
برای ساخت اونها هم دقیقا مثل ساخت جدول معمولی عمل می کنیم:
Create Table #Temp1 (F1 int, F2 int) -- normal
یا
select * into ##temp2 from TBL
و اما متغیر از نوع جدول:
مثل متغیر معمولی ساخته میشه و نوع اون table هست.
این نوع از 2005 به بعد وارد SQL شده.
مثال:
declare @t1 as table(f int, x int) -- declaring
نکته مهم: این نوع جداول رو در پایان کارمون Drop نمی کنیم. خودشون بعد از اتمام SP از بین می رن.
فرقشون با جداول موقتی # دار:
نمیشه از دستورات DDL در اونها استفاده کرد.(create, alter, index, drop)
نمیشه از فرم select into برای ساختشون استفاده کرد.
# دار ها از SP های مجاور هم قابل رویتند اما @ ها نیستند.
# دار ها رو با اتمام کارمون Drop می کنیم.
@ دار ها قابلیت شرکت در تراکنش ها رو ندارند.
@ دار ها رو میشه در توابع استفاده کرد. # دار ها رو نمیشه.
کاربردشون هم مثل # ها هست با این فرق که پس از اتمام SP دیگه وجود ندارند که کسی اونها رو ببینه، حتی خود ما.
بیشتر در مواقعی کاربرد داره که فقط در SP جاری از اون استفاده می کنیم. مثل سرجمع زدن، پیمایش اول عمق درخت ها، و چون نمی شه در توابع از انواع دیگه استفاده کرد این نوع کمک بزرگی هست.
دیگه مطلبی به ذهنم نمی رسه.
امیدوارم از این مطلب خوشتون اومده باشه.
اگر مایل بودید می تونیم به همین روش در مورد مطالب دیگه هم ادامه بدیم.
موفق باشید.
پاسخ با نقل قول
نوشته شده توسط یوسف زالی
