SQL Server باعث می شود: خوب و ترسناک

SQL Server Triggers ابزاری دیگر در جعبه ابزار DBA یا Dev شما است. ادوارد پولاک توضیح می دهد که چه چیزی می تواند با محرک ها اشتباه باشد و چگونه این مسائل را اصلاح کند.

SQL Server Triggers موضوعی از عملکرد و بحث و جدال در هر بستر پایگاه داده است. آنها می توانند با هزینه کم ، ابزار عظیمی را فراهم کنند ، اما اغلب مورد سوء استفاده از توسعه دهندگان ، DBA ها و معماران به طور یکسان قرار می گیرند و در نتیجه تنگناهای عملکردی یا چالش های حفظ آن وجود دارد.

این مقاله به طور خلاصه بررسی می کند DML (زبان دستکاری داده ها) به بحث و گفتگو در مورد چگونگی استفاده از محرک ها به طور مؤثر و مؤثر و همچنین هنگامی که محرک ها می توانند توسعه دهندگان را به سوراخ هایی منتقل کنند که فرار از آن دشوار است ، می پردازد.

در حالی که تمام نسخه های نمایشی در این مقاله در SQL Server قرار دارند ، توصیه های ارائه شده در اینجا جهانی در سراسر سیستم عامل های پایگاه داده است. چالش های ناشی از محرک ها نیز در MySQL ، PostgreSQL ، MongoDB و بسیاری دیگر مشاهده می شود.

محرک ها چیست؟

محرک های سرور SQL را می توان در سرور ، پایگاه داده یا جدول تعریف کرد و به کد اجازه می دهد در هنگام وقوع اقدامات خاص به طور خودکار اجرا شود. این مقاله بر روی محرک های DML روی میزها متمرکز شده است ، زیرا آنها تمایل به استفاده از بیش از حد استفاده بیش از حد دارند. در مقابل ، DDL (زبان تعریف داده) در پایگاه داده یا سرور به طور معمول متمرکز تر و برای عملکرد کمتر مضر است.

ماشه مجموعه ای از کد است که هنگام تغییر داده در یک جدول ارزیابی می شود. ممکن است محرک ها برای اجرای در Insert ، Update ، Delete یا هر ترکیبی از این عملیات تعریف شوند. ادغام عملیات آتش سوزی بر اساس عملیات انجام شده توسط هر عملیات در بیانیه ادغام.

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

در نهایت، تریگرها به TSQL اجازه می دهند که بدون در نظر گرفتن منبع این نوشته ها، هنگام انجام عملیات نوشتن، اجرا شود. آنها معمولاً برای اجرای عملیات حیاتی مانند ورود به سیستم، اعتبارسنجی یا DML اضافی در سناریوهایی که میل به اطمینان از اجرای همیشه کد اضافه شده وجود دارد استفاده می شود. این راحت است زیرا منبع کد مهم نیست. این می تواند از یک API، کد برنامه، اسکریپت انتشار یا یک فرآیند داخلی باشد، و تریگرها بدون توجه به آن فعال می شوند.

محرک ها چگونه به نظر می رسند؟

جدول Sales. Orders را در پایگاه داده نمونه WideWorldImporters در نظر بگیرید. تصور کنید که نیاز به ثبت تمام اقدامات به روز رسانی یا حذف در این جدول، همراه با جزئیاتی در مورد اینکه چه کسی و چه زمانی تغییر رخ داده است، وجود دارد. این عمل می تواند از طریق یک رویه یا کد ذخیره شده انجام شود، اما انجام این کار مستلزم تغییر در هر مکان در کد نوشته شده در جدول است.

برای حل این مشکل از طریق تریگرها می توان مراحل زیر را انجام داد:

1. یک جدول گزارش ایجاد کنید تا داده ها را همانطور که نوشته شده است بپذیرید. TSQL زیر یک جدول گزارش ساده با تمام ستون های اصلی و همچنین چند نقطه داده اضافه شده برای کمک به درک منبع و علت تغییر ایجاد می کند:

این جدول مقادیر قدیمی و جدید را برای تمام ستون ها ثبت می کند. این بسیار کامل است و به طور واقع بینانه می توانیم به سادگی نسخه قدیمی ردیف ها را وارد کنیم و بتوانیم با پیوستن نسخه های جدید و قدیمی به هم تغییرات را استنتاج کنیم. انتخاب به عهده توسعه دهنده است تا تعیین کند که آیا ستون های اضافی قابل قبول هستند یا بیهوده و این تصمیم بر اساس هر موقعیتی متفاوت خواهد بود. 3 ستون آخر جدید هستند و زمینه را در مورد نوع عمل انجام شده (INSERT، UPDATE، یا DELETE)، زمان، و اینکه کدام کاربر آن را انجام داده است، ارائه می دهد.

2. یک ماشه برای ثبت تغییرات در جدول جدید ایجاد کنید:

تنها عملکرد تریگر این است که ردیف هایی را در جدول گزارش وارد کند، یک ردیف برای هر ردیف تحت تأثیر یک عملیات نوشتن معین. با گذشت زمان و تغییر جدول، مستندسازی و نگهداری آن ساده، مبتنی بر مجموعه و آسان است. در صورت نیاز می توان ستون های اضافی را برای ردیابی جزئیات دیگر، مانند نام پایگاه داده، نام سرور، فهرست/تعداد ستون های تحت تأثیر یا برنامه تماس اضافه کرد.

3. آخرین مرحله آزمایش و تأیید صحت پر شدن جدول گزارش است. در زیر آزمایشی از UPDATE روی جدول پس از اضافه شدن ماشه است:

انتخاب * از Sales. Orders_log نتایج اجرای تریگر را نشان می دهد:

برخی از ستون ها در بالا حذف شده اند ، اما ما می توانیم به سرعت تأیید کنیم که تغییرات ضبط شده است ، از جمله اقدامات اضافه شده ، کاربر و ستون های زمان در انتهای جدول ورود.

جداول درج و حذف شده

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

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

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

این جداول فوق العاده راحت هستند زیرا راهی برای دسترسی به داده های تحت تأثیر یک عملیات نوشتن ارائه می دهند بدون اینکه نیاز به بازگشت به جدول زیرین و پرس و جو برای آن داشته باشند. توجه داشته باشید که هر دو در TEMPDB درج شده و حذف شده اند و در معرض محدودیت های فضا یا عملکردی هستند که TEMPDB در یک سرور SQL معین دارد.

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

چه زمانی محرک های مفیدترین هستند؟

استفاده بهینه از محرک های DML برای عملیات نوشتن کوتاه ، ساده و آسان است که تا حد زیادی مستقل از منطق تجاری برنامه ها عمل می کند.

برخی از کاربردهای عالی از محرک ها عبارتند از:

• ورود به سیستم در یک جدول تاریخ
• حسابرسی کاربران و اقدامات آنها در برابر جداول حساس.
• اضافه کردن مقادیر اضافی به جدول که ممکن است در دسترس برنامه نباشد (به دلیل محدودیت های امنیتی یا محدودیت های دیگر) ، مانند:
  • ورود به سیستم/نام کاربری
  • زمان یک عمل رخ می دهد
  • نام سرور/پایگاه داده

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

  برای عملکرد محرک ها ، باید آنها را تنظیم کنند تا مبتنی بر مجموعه باشند. اگر یک ماشه حاوی حلقه ها (در حالی که یا مکان نما) یا روشهای ذخیره شده با پارامتر است ، عملیات روی چند ردیف مجبور به کار بر روی داده های یک ردیف همزمان می شود.

  اگر باید از روشهای ذخیره شده در یک ماشه استفاده شود ، اطمینان حاصل کنید که در صورت لزوم از پارامترهای با ارزش جدول استفاده می کنند تا داده ها به صورت مبتنی بر تنظیم شده منتقل شوند. در زیر نمونه ای از ماشه ای است که از طریق شناسه ها به منظور اجرای یک روش ذخیره شده با استفاده از شناسه های سفارش حاصل تکرار می شود:

  در حالی که نسبتاً ساده است ، عملکرد در انجام عملیات در برابر فروش. وقتی چندین ردیف به طور همزمان وارد می شوند ، زیرا SQL Server مجبور خواهد شد یک به یک را تکرار کند ، زیرا این روش را انجام می دهد. رفع آسان برای بازنویسی روش ذخیره شده و این کد برای انتقال مجموعه ای از شناسه های سفارش به روش ذخیره شده ، به جای انجام این کار یک بار:

  نتیجه این تغییرات این است که مجموعه کامل شناسه ها از ماشه به روش ذخیره شده و پردازش منتقل می شوند. تا زمانی که روش ذخیره شده این داده ها را به روشی مبتنی بر مدیریت انجام دهد ، از تکرار جلوگیری می شود ،

  گفته می شود ، در جلوگیری از رویه های ذخیره شده در محرک ها ، ارزش زیادی وجود دارد زیرا آنها یک لایه اضافی از محصور سازی اضافه می کنند که بیشتر TSQL را پنهان می کند که هنگام نوشتن داده ها به یک جدول انجام می شود. آنها باید آخرین راه حل در نظر گرفته شوند و فقط در شرایطی که جایگزین بازنویسی TSQL بارها در بسیاری از مکان ها در یک برنامه استفاده شود ، مورد استفاده قرار می گیرد.

  چه زمانی محرک های خطرناک هستند؟

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

  محرک ها می توانند خطرناک شوند وقتی:

  • خیلی زیاد استبرای به حداقل رساندن پیچیدگی ، تا حد امکان محرک کمی را حفظ کنید.
  • کد ماشه پیچیده می شود. اگر به روزرسانی یک ردیف در یک جدول منجر به هزاران خط کد ماشه اضافه شده شود ، برای توسعه دهندگان سخت می شود که بطور کامل درک کنند که وقتی داده ها روی یک جدول نوشته می شوند چه اتفاقی می افتد. حتی بدتر ، عیب یابی می تواند بسیار چالش برانگیز باشد.
  • محرک ها به سرور متقابل می روند. این شبکه را به عملیات ماشه معرفی می کند و می تواند منجر به کند شدن یا عدم موفقیت در هنگام بروز مشکلات اتصال شود. حتی اگر پایگاه داده هدف موضوع نگهداری باشد ، محرک های داده های متقابل نیز می توانند مشکل ساز باشند.
  • محرک ها محرک ها را فراخوانی می کنند. بیشترین درد محرک ها زمانی است که یک ردیف واحد درج می شود ، و این کار نوشتن منجر به 100 محرک در 75 جدول برای اجرای آن می شود. هنگام نوشتن کد ماشه ، اطمینان حاصل کنید که محرک ها می توانند تمام منطق لازم را بدون ایجاد محرک های بیشتر انجام دهند. تماس های ماشه اضافی اغلب غیر ضروری است ، اما ممکن است از این کار در اعماق سوراخ خرگوش جلوگیری شود.
  • محرک های بازگشتی روی آن تنظیم شده اند. این یک تنظیم در سطح پایگاه داده است که به طور پیش فرض تنظیم شده است. هنگام روشن شدن ، به محتوای یک ماشه اجازه می دهد تا همان ماشه را صدا کنند. در صورت نیاز به بازگشت ، آن را با استفاده از روشی کمتر مبهم اجرا کنید. محرک های بازگشتی می توانند به عملکرد بسیار آسیب برساند و برای اشکال زدایی فوق العاده گیج کننده هستند. غالباً از محرک های بازگشتی استفاده می شود که DML در یک محرک محرک های دیگر را به عنوان بخشی از یک عمل شلیک می کند. کاهش تعداد عملیات نوشتن در برابر یک جدول می تواند نیاز به این امر را از بین ببرد. به جای محرک ها به عنوان راهی برای اجازه تغییر داده ها یک بار در نظر بگیرید ، بدون اینکه عملیات اضافی پس از واقعیت مورد نیاز باشد.
  • توابع ، روشهای ذخیره شده یا نماهای در محرک ها هستند. محاصره منطق کسب و کار بیشتر در محرک ها باعث پیچیده تر شدن آنها می شود و این تصور غلط را ایجاد می کند که کد ماشه کوتاه و ساده است ، وقتی که در واقع اینگونه نیست. از استفاده از روشها و توابع ذخیره شده در صورت امکان در محرک ها خودداری کنید و منظره را به سناریوهایی که در آن ساده و ساده هستند محدود کنید.
  • تکرار رخ می دهددر حالی که حلقه ها و نشانگرهای طبیعت به صورت ردیف به ردیف عمل می کنند و ممکن است منجر به عملیاتی در برابر 1000 ردیف که یک ردیف در یک زمان ، 1000 بار رخ می دهد ، به عملکرد پرس و جو بسیار آسیب برساند.

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

  توجه به این نکته حائز اهمیت است که برای حفظ معاملات اتمی و بادوام ، هر اشیاء تحت تأثیر یک ماشه ، معامله را باز نگه می دارند تا زمانی که آن ماشه (و هر محرک بعدی) کامل شود. این بدان معنی است که محرک های طولانی نه تنها باعث می شوند معاملات دوام بیشتری داشته باشند بلکه قفل ها را نیز نگه می دارند و باعث می شوند که اختلافات طولانی تر نیز ادامه یابد. بنابراین ، هنگام آزمایش محرک ها ، تأثیر آنها بر قفل ، مسدود کردن و انتظار باید هنگام ایجاد یا اضافه کردن منطق اضافی به محرک های موجود درک شود.

  نحوه بهبود محرک ها

  روش های زیادی برای ایجاد محرک ها قابل حفظ ، آسان تر و عملکرد بیشتر وجود دارد. در زیر برخی از افکار اضافی در مورد نحوه مدیریت محرک ها به طور مؤثر و جلوگیری از افتادن در تله ها وجود دارد.

  سند!

  خود ماشه باید به خوبی مستند شود:

  • چرا این محرک وجود دارد؟
  • چه کار میکند؟
  • چگونه کار می کند (اگر آشکارا آشکار نباشد)؟
  • آیا استثنائات یا احتیاطی در مورد نحوه عملکرد ماشه وجود دارد؟

  علاوه بر این ، اگر TSQL در یک ماشه برای هر کسی که قبلاً با آن آشنا نبود ، گیج کننده یا دشوار است ، سپس نظرات Inline را اضافه کنید تا به توسعه دهندگان کمک کند که برای اولین بار آن را مشاهده می کنند. در زیر نمونه ای از چگونگی نگاه مستندات در یک ماشه است:

  توجه داشته باشید که مستندات گسترده نیست ، اما شامل یک هدر مختصر است و چند قطعه کلیدی TSQL را در ماشه توضیح می دهد:

  • محرومیت از CustomerID = -1. این برای هر کسی که اتفاقاً آن را نمی داند آشکار نخواهد بود ، بنابراین این یک اظهار نظر عالی است.
  • عبارت موردی برای ActionType برای چیست.
  • چرا IsNull در ستون OrderID بین درج و حذف شده استفاده می شود.

  در صورت بروزرسانی استفاده کنید

  در Triggers ، عملکرد به روزرسانی امکان تعیین اینکه آیا یک به روزرسانی یا درج عملیات در تلاش برای نوشتن داده ها به یک ستون معین است ، فراهم می کند. این می تواند به یک ماشه اجازه دهد تا بررسی کند که آیا ستون قبل از پیوستن به جداول و انجام عملیات تغییر کرده است یا خیر. موارد زیر نمونه ای از این نحو است:

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

  Columns_Updated یک الگوی بیت واربی است که نشان می دهد کدام ستون در یک جدول به عنوان بخشی از یک عملیات نوشتن به روز شده است و می تواند در یک ماشه مورد استفاده قرار گیرد تا به سرعت تعیین کند که آیا مجموعه خاصی از ستون ها تحت تأثیر یک درج یا به روزرسانی قرار گرفته اند. در حالی که مستند و یک روش قانونی برای تعیین ستون های تحت تأثیر یک عملیات نوشتن است ، استفاده از آن پیچیده است و مستند سازی آن دشوار است. من به طور کلی توصیه می کنم از استفاده از آن استفاده کنم زیرا تقریباً تضمین شده است که توسعه دهندگان ناآشنا با آن اشتباه بگیرند.

  اگر بروزرسانی ممکن است لفظ تر باشد ، اما حتی اگر بسیاری از ستون ها درگیر باشند ، کارآمد و آسان است.

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

  یک محرک در هر عمل

  کد ماشه را تا حد امکان ساده نگه دارید. حداکثر یک درج واحد ، به روزرسانی و حذف ماشه را حفظ کنید. اجازه رشد تعداد محرک های روی یک جدول ، پیچیدگی جدول را به شدت افزایش می دهد و درک عملکرد آن را سخت تر می کند. یک محرک هر عمل تضمین می کند که نظم محرک هرگز مورد توجه مهم نیست ، همچنین باعث بهبود قابلیت حفظ کد و کاهش پیچیدگی می شود.

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

  چه اتفاقی می افتد که یک ردیف درج شود؟در مورد یک عملیات ادغام که درج و به روز می کند چیست؟به چه ترتیب باعث آتش سوزی می شود؟پاسخ این سؤالات نیاز به برخی از خراش دادن ها و تحقیق دارد. این پیچیدگی ارزش سردرد را ندارد. حفظ محرک های کمتر یک راه حل آسان است و حدس و گمان را از درک چگونگی نوشتن در یک جدول معین حذف می کند.

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

  آن را ساده نگه دارید

  محرک های بهینه عملیات ساده ای را انجام می دهند ، به سرعت انجام می دهند و به دلیل اجرای آنها باعث ایجاد محرک یا ماژول های بیشتری نمی شوند.

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

  با استفاده از مثال قبلی ما ، ماشه ای را در نظر بگیرید که در جدول حسابرسی تغییر می کند. این به راحتی می تواند از ماشه به یک روش ذخیره شده یا کد منتقل شود و تلاش برای انجام این کار قابل توجه نخواهد بود. راحتی آن فرآیند ورود به سیستم در یک ماشه ، آن را ارزشمند می کند ، اما در عین حال ، ما می توانیم تعیین کنیم که چند ساعت طول می کشد تا توسعه دهندگان مهاجرت کنند که TSQL از ماشه به مکان دیگری.

  این تعداد ساعت ها را می توان به عنوان بخشی از هزینه نگهداری برای هر محرک معین دانست. یعنی قیمتی که برای خلاص شدن از ماشه در صورت لزوم باید پرداخت شود. این ممکن است انتزاعی به نظر برسد ، اما مهاجرت پایگاه داده بین سیستم عامل ها رایج است. مجموعه ای از محرک هایی که در SQL Server عملکرد خوبی دارند ممکن است در Oracle یا PostgreSQL مؤثر نباشد. به طور مشابه ، مهاجرت به یک نوع پایگاه داده که از همان سطح عملکرد ماشه پشتیبانی نمی کند ، ممکن است نیاز به حذف یا ساده سازی محرک ها داشته باشد.

  متغیرهای جدول بهینه سازی شده حافظه

  بعضی اوقات ، جداول موقت در یک ماشه مورد نیاز است تا به روزرسانی های متعدد در برابر داده ها یا تسهیل درج های تمیز از یک به جای ماشه درج شود. جداول موقت در TEMPDB ذخیره می شوند و در معرض هر اندازه ، سرعت و محدودیت عملکرد در پایگاه داده TEMPDB قرار دارند.

  برای جداول موقت که اغلب به آنها دسترسی پیدا می کنند ، یک متغیر جدول بهینه شده حافظه یک روش عالی برای حفظ داده های موقت در حافظه است نه در TEMPDB.

  TSQL زیر یک پایگاه داده برای داده های بهینه شده حافظه (در صورت لزوم) پیکربندی می کند:

  پس از پیکربندی ، می توان از نوع جدول بهینه سازی شده حافظه ایجاد کرد:

  این TSQL یک جدول مورد نیاز ماشه نشان داده شده در زیر ایجاد می کند:

  در زیر نمایشی از یک ماشه است که از یک جدول بهینه شده حافظه استفاده می کند:

  هرچه عملیات بیشتری در تریگر مورد نیاز باشد، صرفه جویی بیشتری مشاهده خواهد شد زیرا متغیر جدول بهینه شده برای حافظه برای خواندن/نوشتن نیازی به IO ندارد. هنگامی که داده های اولیه از جدول INSERTED خوانده می شود، باقیمانده ماشه می تواند tempdb را به حال خود رها کند و اختلاف احتمالی را که در صورت استفاده از متغیرهای جدول استاندارد یا جداول موقت ایجاد می شود، کاهش می دهد.

  کد زیر برخی از داده های تست را تنظیم می کند و یک به روز رسانی را برای نشان دادن نتایج کد بالا اجرا می کند:

  پس از اجرا، محتویات جدول OrderAdjustmentLog را می توان تأیید کرد:

  

  نتایج مطابق انتظار است. جداول بهینه سازی شده برای حافظه، با کاهش اتکا به ذخیره سازی استاندارد و جابجایی جداول واسطه در حافظه، راهی برای بهبود بسیار سرعت ماشه فراهم می کنند. این به سناریوهایی محدود می شود که در آن اختلافات یا IO زیادی علیه اشیاء موقت وجود دارد، اما می تواند در رویه های ذخیره شده یا سایر TSQL رویه ای نیز مفید باشد.

  جایگزین هایی برای محرک ها

  مانند همه ابزارها، تریگرها می توانند به طور موثری با مشکلاتی که برای غلبه بر آنها بهینه شده اند مقابله کنند. به طور مشابه، آنها می توانند مورد سوء استفاده قرار گیرند و به منبع سردرگمی، گلوگاه های عملکردی و کابوس های نگهداری تبدیل شوند. بسیاری از جایگزین ها وجود دارند که نسبت به محرک ها ارجح هستند و باید قبل از پیاده سازی (یا اضافه کردن به محرک های موجود) در نظر گرفته شوند.

  جداول زمانی

  جداول زمانی در SQL Server 2016 معرفی شدند و راهی آسان برای افزودن نسخه به جدول بدون ساختن ساختارهای داده و ETL خود را ارائه می دهند. این ثبت نام برای برنامه ها نامرئی است و پشتیبانی کامل از نسخه سازی را ارائه می کند که مطابق با ANSI است و به آن اجازه می دهد تا یک راه آسان برای حل مشکل ذخیره نسخه های قدیمی داده ها باشد.

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

  این یک ویژگی غنی SQL Server است که مجموعه ای از عبارات را برای بازگرداندن سریع داده ها برای یک نقطه از زمان یا هر دوره زمانی ارائه می دهد. نسخه ی نمایشی کامل آن در اینجا کاملاً فضا بر خواهد بود، اما اطلاعات بیشتر را می توانید در اینجا پیدا کنید.

  محدودیت ها را بررسی کنید

  برای اعتبارسنجی ساده داده ها، محدودیت های بررسی می توانند دقیقاً آنچه را که بدون نیاز به توابع، رویه های ذخیره شده یا محرک ها مورد نیاز است، فراهم کنند. یک محدودیت چک بر روی یک ستون تعریف می شود و زمانی که داده ها ایجاد می شوند به طور خودکار اعتبارسنجی می کند. در زیر نمونه ای از محدودیت چک آورده شده است:

  این کد بررسی می کند که آیا یک ستون معتبر JSON است یا خیر. اگر اینگونه باشد ، اجرای آن به طور عادی پیش می رود. اگر اینگونه نباشد ، پس از آن خطایی توسط SQL Server پرتاب می شود و عملیات نوشتن شکست می خورد. محدودیت ها را بررسی کنید می تواند هر ترکیبی از ستون و مقادیر را بررسی کند ، بنابراین می تواند هر دو کار اعتبار سنجی ساده یا پیچیده را مدیریت کند.

  محدودیت های بررسی برای ایجاد ارزان و نگهداری آسان است. آنها همچنین مستند سازی و درک آنها ساده تر هستند زیرا دامنه محدودیت چک محدود به اعتبار داده های ورودی و اطمینان از یکپارچگی داده ها است ، در حالی که محرک ها می توانند عملاً هر کاری را تصور کنند!

  محدودیت های منحصر به فرد

  اگر یک ستون باید منحصر به فرد باشد و کلید اصلی جدول نیست ، یک محدودیت منحصر به فرد روشی آسان و کارآمد برای انجام کار است. یک محدودیت منحصر به فرد ترکیبی از شاخص و اجرای منحصر به فرد بودن است. این شاخص لازم است تا بتوانید یکپارچه بودن را به طور مؤثر تأیید کنید (بدون آن ، هر زمان که ستون های منحصر به فرد نوشته شده باشد ، اسکن جدول لازم است).

  موارد زیر نمونه ای از یک محدودیت منحصر به فرد است:

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

  محدودیت های منحصر به فرد برای این منظور ساخته شده است و ساده ترین راه برای اجرای منحصر به فرد بودن در یک ستون است. این عملکرد جایی در محرک ها ندارد و راه حل داخلی کارآمدتر ، آسانتر برای نگهداری و مستند سازی آسان تر خواهد بود. هر توسعه دهنده ای که یک محدودیت منحصر به فرد را ببیند ، بلافاصله بدون نیاز به حفر عمیق تر در TSQL می فهمد تا بفهمد چگونه کارها کار می کنند و این سادگی این را به یک راه حل ایده آل تبدیل می کند.

  محدودیت های کلیدی خارجی

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

  این یک مثال ساده کلید خارجی است:

  هنگامی که داده ها به Sales. Orders نوشته شده است ، ستون CustomerID در برابر ستون CustomerID در Sales. customers بررسی می شود.

  مشابه محدودیت های منحصر به فرد ، کلیدهای خارجی فقط یک هدف دارند: تأیید کنید که داده ها در یک جدول در جدول دیگری وجود دارد. مستند سازی آسان است ، درک آن ساده است و در اجرای آن کارآمد است.

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

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

  رویه های ذخیره شده

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

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

  در حالی که SQL Server (و بیشتر RDBMS) ضمانت اسید را ارائه می دهد که معاملات دارای اتمی ، سازگار ، منزوی و با دوام هستند ، مجموعه معاملات در کد خود ما ممکن است یا ممکن است نیاز به پیروی از همان قوانین نداشته باشد. برنامه های دنیای واقعی در نیازهای یکپارچگی داده های خود متفاوت است ، در هر نقطه از زمان واقعی و اتمی تا در نهایت سازگار.

  یک روش ذخیره شده اجازه می دهد تا کد برای دستیابی به یکپارچگی داده های مورد نیاز یک برنامه سفارشی شود و اطمینان حاصل شود که عملکرد و منابع محاسباتی بر روی یکپارچگی داده های غیر ضروری تلف نمی شوند. به عنوان مثال ، یک برنامه رسانه های اجتماعی که به کاربران امکان ارسال عکس CAT را می دهد ، به احتمال زیاد به معاملات آن کاملاً اتمی و سازگار نیست. اگر عکس گربه من یک ثانیه قبل یا بعد از شما وارد شود ، هیچ کس اهمیتی نخواهد داد. به همین ترتیب ، اگر در حالی که من آن را ویرایش می کنم در مورد عکس من اظهار نظر کنید ، احتمالاً زمان برای کسی که از این داده ها استفاده می کند مهم نیست.

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

  کارکرد

  توابع یک راه آسان برای کپسوله کردن منطق مهم در یک مکان واحد فراهم می کنند که در آن می توان آن را روی داده ها اعمال کرد. آنها اغلب گزینه ای برتر نسبت به محرک ها هستند، زمانی که می توانند به طور مکرر مورد استفاده مجدد قرار گیرند و باعث ایجاد گلوگاه در عملکرد نمی شوند. یک تابع منفرد که در 50 درج جدول مورد استفاده مجدد قرار می گیرد، کدگذاری و نگهداری آن بسیار آسان تر از 50 محرک است، یکی در هر جدول، که همگی منطق یکسانی را انجام می دهند.