https://virgool.io/feed/@a_ajoodanheydari SQL Server Database Administration, Programmer fa 2026-06-18 12:37:05 https://files.virgool.io/upload/users/51924/avatar/xqy2yz.png?height=120&width=120 https://virgool.io/@a_ajoodanheydari https://virgool.io/@a_ajoodanheydari/%D8%AA%D8%A7%D8%A8%D8%B9-eomonth-he6imiixhmni مقدمهبعضی اوقات وقتی ما میخواهیم کوئری هایی در ارتباط با تاریخ در دیتابیس ها بنویسیم ممکن است با یکسری مشکلات مرسوم روبرو شویم در واقع اگر به این موضوع کمی دقت کنیم متوجه میشویم که این ها مشکلات نیستند بلکه نکاتی را در این خصوص رعایت نکرده ایم. تابع EOMONTH هم یکی دیگر از این مواردی است که باید در استفاده از آن دقت کنیم. در این مقاله سعی بر آن شده است که به بررسی تابع EOMONTH و نحوه استفاده از آن در SQL Server بپردازیم و در آخر یک سناریو کاربردی را مطرح کنیم. تابع EOMONTHاگر بخواهیم این تابع را بصورت ساده تعریف کنیم میتوان گفت: به ازای یک تاریخ خاص , آخرین روز ماه را بر میگرداند. به syntax زیر توجه فرمایید.EOMONTH(start_date [, offset] );تابع EOMONTH دو ورودی میگیرد:ورودی اول (start_date) شامل یک تاریخ میباشد و تابع EOMONTH موظف است آخرین روز ماه ذکر شده در این تابع را برگرداندورودی دوم شامل یک offset میباشد که اصطلاحا با یک square brackets نشان داده شده است (این [ ] علامت حکایت از اختیاری بودن آن دارد). offset یک عدد صحیح میباشد که به ماه تاریخ مورد نظر در ورودی اول تابع (start_date) اضافه یا کم میکند.مثال هایی از تابع EOMONTH:فرض کنید میخواهیم آخرین روز از ماه را در تاریخ 10-02-2020 را بدست آوریمSELECT EOMONTH('2020-02-10') AS End_Of_Month با توجه به دستور بالا خروجی بصورت زیر میباشد.حال فرض کنید این بار شما تصمیم گرفتید آخرین روز ماه را در همان تاریخ ولی در سال 2021 یعنی 10-02-2021 را بدست آورید. SELECT EOMONTH('2021-02-10') AS End_Of_Monthخروجی دستور بالا بصورت زیر میباشد.اگر به خروجی دو دستور بالا نگاه کنید متوجه میشوید که دو جواب متفاوت است و این تفاوت فقط با تغییر سال بوجود آمده است. اما چرا؟ اگر به جدول زیر با دقت نگاه کنید متوجه میشوید که تعداد روز ها در ماه های مختلف و یا بعضا در سال ها مختلف متفاوت است به عنوان مثال ژانویه (January) دارای 31 روز می باشد اما فوریه (February) در سال 2020 , 29 روز است ولی در سال 2021 , 28 روز است که به اضای هر 4 سال متفاوت استجدول 1-1بررسی سناریو کاربردیفرض کنید یک جدولی به نام Orders وجود دارد این جدول شامل سفارشات هر مشتری - کارمند و تاریخ درج یک سفارش در این جدول میباشد. به ما گفته شده است که آخرین سفارش موجود که در آخرین روز از هر ماه است را بدست آوریمروش اول استفاده از تابع EOMONTHهمانطور که ذکر شد این تابع آخرین روز از ماه را به ازای تاریخ مربوطه برمیگرداندSELECT * FROM Orders WHERE orderdate = EOMONTH(orderdate)همانطور که میبینید به ازای هر تاریخ آخرین روز را محاسبه میکند. توجه داشته باشد که اگر یک مشتری در یک تاریخ خاص دو عدد سفارش داشته باشد تابع EOMONTH هر دوی آن را بر میگرداندروش دوم استفاده از Ranking Function است. ما به این دلیل از() DENSE_RANK استفاده کرده ایم که اگر یک مشتری در یک تاریخ خاص دو عدد سفارش داشته باشد هر دوی آن را در خروجی نشان دهد;WITH CTE_Order AS( SELECT *, DENSE_RANK()OVER(PARTITION BY YEAR(orderdate), MONTH(orderdate) ORDER BY DAY(orderdate) DESC) AS rw FROM Orders )SELECT orderid, custid, empid, orderdate, shipcountryFROM CTE_OrderWHERE rw = 1خروجی دستور بالا بصورت زیر استهر دو این دستور آخرین روز از هر ماه را بر میگرداند اما تعداد خروجی ها متفاوت است همانطور که میبینید EOMONTH خروجی 26 رکورد و روش دوم خروجی 42 رکورد دارد .نکته بسیار مهمی که شما باید به آن توجه ویژه کنید این است که EOMONTH آخرین روز از جدول 1-1 که در بالا توضیح داده شده است را بر میگرداند اگر این روز در Table شما (Orders) باشد در خروجی نشان میدهد اگر نباشد صرف نظر میکند ولی روش دوم آخرین روز از تاریخ موجود که در Table میباشد را بر میگرداند به عبارت دیگر اگر شما در تاریخ 25-01-2021 سفارشی در جدول خود درج کرده باشید تابع EOMONTH آن را بر نمیگرداند چون تابع EOMONTH آخرین روز یعنی 31 را میشناسد و کاری ندارد که شما آخرین روز درج سفارشتان در آن تاریخ 25 است اما روش دوم میگوید من آخرین تاریخی که در جدول سفارشات وجود دارد بر میگردانم به شرطی که بزرگترین باشد. همانطور که میبینید در روش اول خبری از ماه 8 نیست چون آخرین درج سفارشش 30 ام بوده ولی جدول 1-1 برای ماه 8, 31 روز را نشان میدهد پس شامل خروجی نمیباشد ولی روش دوم میگوید بزرگترین روز در ماه 8 , 30 است پس در خروجی می آید.به عنوان مثال تکمیلی فرض کنید ما یک رکورد در تاریخ 26-09-2013 در جدول Orders درج میکنیمINSERT INTO Orders(custid,empid,orderdate,shipcountry) VALUES(10,3,'2013-09-26','Iran')خروجی روش اول باز هم 26 رکورد می باشد چون آخرین روز ماه 9, 30 روز است ولی آخرین روزی که ما در ماه 9 درج کردیم 26 است پس در خروجی ظاهر نمیشود . خروجی روش دوم این دفعه 43 رکورد میشود چون تاریخ جدید درج شده است .روش اول EOMONTH روش دوم علیرضا حیدری علیرضا حیدری Sun, 24 Oct 2021 17:52:52 +0330 https://virgool.io/@a_ajoodanheydari/%D8%A8%D8%B1%D8%B1%D8%B3%DB%8C-%D9%85%D8%B9%D9%85%D8%A7%D8%B1%DB%8C-page-%D9%88-%D8%B3%D8%A7%D8%AE%D8%AA%D8%A7%D8%B1-%D8%A2%D9%86%D9%87%D8%A7-page-architecture-uyte2sytyt8t انواع مختلفی از Page ها در SQL Server وجود دارد که هر کدام وظیفه ای را به عهده دارند که با شناخت عملکرد و معماری آنها میتوانیم نقاط مشکل ساز سیستم را رفع کنیم به عنوان مثال فرض کنید سیستم ما از لحاظ Performance دچار مشکل شده است ما با شناخت معماری آنها میتوانیم متوجه شویم مشکل به وجود آمده چیست و چگونه باید در رفع آن بکوشیم .ما در این مقاله به معرفی Data Page ها عملکرد و بررسی آنها میپردازیم و در آخر برای درک بیشتر مثالی را ارائه خواهیم داد.در واقع تمام رکورد ها , دیتاها و هر آنچه که قابل ذخیره سازی در دیتابیس ما است در قالب Page ها ذخیره میشوند که این Pageها داخل Data File ها وجود دارند و باید به این نکته اشاره کرد که Log File مفهومی به نام Page ندارد. همچنین لازم به ذکر است که عملیات IO به ازای این Page ها انجام میشود در واقع رکورد های ما وقتی بخواهند از روی دیسک واکشی شوند در قالب این بلوک 8 کیلو بایتی روی حافظه میآید و مورد پردازش قرار میگیرد همانطور که اشاره کردیم ظرفیت این Page ها 8 کیلوبایت است.که تنها 8060 بایت در دسترس است. Page ها فرمت آدرس دهی مربوط به خود میباشد که این آدرس دهی به شکل زیر میباشدFile ID : Page Number -----> 1:501به این معنی میباشد که دیتافایل شماره 1 و پیج شماره 501وقتی ما از Page صحبت میکنیم , یک ساختار و قاعده ای مشخصی وجود دارد که شامل 3 قسمت اصلی میباشد که شامل موارد زیر استPage HeaderData RowRow OffsetPage Architectureبررسی Page Header : در این قسمت پر از اطلاعات سیستمی وجود دارد که ظرفیت آن 96 بایت است . از این اطلاعات میتوان به مشخص کردن Page های مربوط به Object خاص , مشخص کردن آدرس های مربوط به Page های قبل و بعد از خود (در صورت وجود داشتن Index) , تعداد رکوردها , شماره Page و Checksum مربوط به Page ها و موارد دیگر اشاره کرد.بررسی Data Row :محلی برای ذخیره , ویرایش و حذف رکوردهای ما میباشد . حداکثر طول رکوردها 8060 بایت است . داده های ما در اینجا پشت سر هم ذخیره میشوند و قسمت Free Space آن مشخص کننده این است که هنوز داده هایی برای ذخیره وجود نداردبررسی Row Offset :Page Architecture - Row Offsetظرفیت این قسمت 36 بایت میباشد . مرتب سازی Data Rowها در این قسمت میباشد توجه داشته باشید که وقتی رکوردی درج میشود همانطور که قبلا ذکر شده بود این رکوردها پشت سرهم درج میشود و وظیفه ی مرتب سازی آنها در Row Offset است همونطور که در شکل بالا ملاحظه میفرمایید با جا به جا شدن این Offset ها داده های ما مرتب میشود. بررسی سناریو کاربردی :فرض کنید دیتابیسی وجود دارد به اسم PageStructure ما برای اینکه متوجه بشویم که دیتابیس ما چه فایل هایی دارد از دستور sp_helpfile استفاده میکنیم که خروجی این store procedure به شکل زیر میباشدsp_helpfile همانطور که میبیند ما در اینجا دوتا فایل داریم که یکی دیتا فایل ما است و تمامی Page ها در آنجا ذخیره میشود و آن یکی لاگ فایل است که مفهومی به نام Page ندارد. توجه داشته باشید که میتوانیم بسته به نیازمان تعداد دیتافایل های بیشتری داشته باشیم.با استفاده از دستور زیر ما جدولی به نام PageTable میسازیم و مقدار 10000 رکورد در آن درج میکنیمما برای بدست آوردن Page های مربوط به یک جدول خاص از دستور زیر استفاده میکنیمو خروجی دستور بالا بصورت زیر میباشد.همانطور که میبینید Page های مربوط به جدول مورد نظر با این دستور استخراج شد که در PagePID قابل مشاهده میباشد. توجه داشته باشید که هر Page شامل یک IAM Page میباشد که مخفف آن Index Allocation Map میباشد که SQL Server با استفاده از آن متوجه میشود که هر Page متعلق به کدام شئ میباشد ظرفیت آن 4GB میباشد و Page Type آن همانطور که در خروجی بالا میبینید 10 میباشد.اما داده های ما که همان Data Page ها میاشد در Page Type شماره 1 قابل مشاهده است که رکوردها در داخل آن ذخیره شده است که آدرس آن 330, 331,... میباشدهمانطور که در در دستور زیر مشاهده میکنید یک تابعی وجود دارد به اسم sys.fn_PhysLocFormatter وقتی ما این تابع را در جدول مورد نظر صدا میزنیم تمامی رکوردها با FileID و Page مربوط به آن و همچنین شماره رکورد مورد نظر استخراج میشود.خروجی مورد نظر به صورت زیر میباشد.اگر بخاطر داشته باشید ما در ابتدای مقاله یک شکلی نمادین از معماری Page و ساختار آن ارائه دادیم حال میخواهیم محتویات داخل یک Page را Dump کنیم همانطور که در دستور بالا مشاهده میکنید ما قبل از اجرا کردن دستور DBBC PAGE باید فلگی به شماره 3604 را ON کنیم تا بتوانیم محتویات Page را ببنیم توجه داشته باشد که باید دسترسی SA داشته باشید دستور DBCC PAGE شامل پارامترهایی میباشد که این پارامترها به صورت زیر میباشد.DBCC PAGE ( \{dbid | dbname\}, filenum, pagenum \[, printopt\] \[, cache\] )خروجی دستور مربوطه به شکل زیر میباشد. توجه داشته باشید که به دلیل حجم اطلاعات خروجی در چند مرحله نمایش داده میشودPage Header :Data Row : Row Offset :موفق باشید. علیرضا حیدری علیرضا حیدری Sat, 28 Mar 2020 01:57:51 +0430 https://virgool.io/@a_ajoodanheydari/%D8%A2%D8%B4%D9%86%D8%A7%DB%8C%DB%8C-%D8%A8%D8%A7-ctes-%D9%87%D8%A7-%D8%A8%D8%AE%D8%B4-%D8%AF%D9%88%D9%85-cte-%D8%A8%D8%A7%D8%B2%DA%AF%D8%B4%D8%AA%DB%8C-ydh2xy9h4ykh همانطور که در مقاله قبل , با یکی از انواع CTEs ها به نام CTE غیر بازگشتی آشنا شدیم و راجع به خصوصیات , مزایا و معایب آنها صحبت کردیم حال در این مقاله قصد داریم با نوع دیگر از آن به نام CTE بازگشتی یا Recursive CTE آشنا شویم و در آخر به بررسی یک سناریو کاربردی بپردازیمیکی از مزایای استفاده از Recursive CTE پیاده سازی ساختار درختی است . اگر بخواهیم مثالی از ساختار درختی را بیان کنیم میتوان به چارت سازمانی اشاره کرد به عنوان مثال هر سازمانی یک مدیر دارد هر مدیری میتواند با یک سری معاون در ارتباط باشد و هر معاونی با یک یا چند مدیر میانی و هر مدیر میانی با یک سری کارمند و ... در ارتباط باشند. همانطور که مشاهده میکنید با یک ساختار درختی رو به رو هستیم حال اگر بخواهیم همچین ساختارهایی را در SQL Server پیاده سازی کنیم میتوان از CTE های بازگشتی استفاده کنیم زمانی که ما از Recursive CTE استفاده میکنیم , در بخش Sub Query آن , دیگر یک کوئری نمی نویسیم در اینجا بیش از یک کوئری مینویسیم که این دو کوئری از طریق یک Set Operators که در بیشتر مواقع UNION ALL است با یکدیگر در ارتباط هستند و اینکه این ارتباط به چه شکلی است در ادامه توضیح خواهم دادRecursive CTEهمانطور که در تصویر بالا میبینید در CTE بازگشتی یک بخشی به نام Anchor Member و بخش دیگری به نامRecursive Member وجود دارد که این دو بخش از طریق یک Set Operators با هم در ارتباط هستند.حال برای درک بیشتر این موضوع میخواهیم به بررسی مثالی بپردازیم . فرض کنید قرار است اعداد 1 تا 100 را تولید کنیم برای این کار ما از 2 روش استفاده میکنیم و با یکدیگر مقایسه خواهیم کردروش اول با استفاده از CROSS JOIN :در ابتدا جدولی به اسم Numbers ایجاد میکنیممقادیر 0 تا 9 را در جدول Numbers درج میکنیم طبق فرمول موجود اعداد 1 تا 100 را تولید میکنیمنتیجه کوئری بالا به صورت زیر میباشدروش دوم با استفاده از Recursive CTE : در ابتدا ما Anchor Member را مشخص و عدد 1 را برای ما تولید میکنددر قسمت Recursive Member ما میتوانیم اسم CTE را صدا بزنیم . به عبارت دیگر قسمت Anchor با نام CTE مشخص میشود یعنی هرگاه بگوییم CTE انگار همان SELECT 1 را میخواهیمو نتیجه دستور بالا به صورت زیر میباشد.حال اگر بخواهیم این دو کوئری را از لحاظ Cost با هم بررسی کنیم نتیجه این قیاس به صورت زیر می باشدهمانطور که مشاهده میکنید کوئری که با استفاده از روش CROSS JOIN نوشته شده است به نسب کوئری که با استفاده از روش Recursive CTE نوشته شده است یک نسبت 100 به 0 میباشدنکته ای که خیلی باید دقت داشته باشید این است که ماکزیموم فرآیند تکرار به صورت پیشفرض 100 تا استاما برای رفع این مشکل یک option وجود دارد که ما میتوانیم در انتهای کوئری هایی که از نوع CTE بازگشتی است استفاده کنیم که این option ها عبارت است از : MAXRECURSION: 0 (Infinite) MAXRECURSION: 100 (Default) MAXRECURSION: 32767 (Max value)حالا سوالی که در اینجا مطرح میشود این است که چه فرقی بین مقدار 0 (نامحدود) با مقدار 32767 که همان (Max value) است وجود دارد؟ اگر ما بخواهیم به صورت عددی وارد کنیم نهایت تا 32767 میتوانیم مشخص کنیم اما اگر ما مقدار نامحدود را انتخاب کردیم باید در قسمت WHERE در Recursive Member کنترل نماییمسناریو کاربردی :همانطور که قبلا صحبت کردیم میپردازیم به یک سناریو کاملا کاربردی در خصوص CTE بازگشتیفرض کنید ما یک جدولی داریم به اسم Employees که مشخصات هر کارمند را در آن ثبت میکنیمدر این جدول فیلدی وجود دارد به نام mgrid که این فیلد رئیس هر کارمند را مشخص میکند Table Employees این جدول مطابق چارت زیر طراحی شده است.در این مثال ما میخواهیم بدانیم که بالا دستی های کارمند شماره 9 چه کسانی هستند.اول بخش Anchor Member را مشخص میکنیم (هدف ما بالا دستی های کارمند شماره 9 است )دوم بخش Recursive Member را مشخص میکنیم که فرآیند تکرار را برای ما انجام دهد .خروجی کوئری بالا بصورت زیر میباشد.همانطور که مشاهده میکنید ما با یک ساختار بازگشتی روبرو هستیم که فرآیند تکرار را برای ما انجام میدهد . و در آخر فرض کنید که ما با استفاده از روش های دیگر میخواستیم چنین سلسله مراتبی را تولید کنیم چه حجمی از کوئری باید نوشته میشد؟ و چه هزینه ای برای ما داشت ؟و چقدر خوانا بودن کوئری را برای ما مشکل ساز میکرد...موفق باشید . علیرضا حیدری علیرضا حیدری Tue, 18 Feb 2020 01:34:55 +0330 https://virgool.io/@a_ajoodanheydari/%D8%A2%D8%B4%D9%86%D8%A7%DB%8C%DB%8C-%D8%A8%D8%A7-ctes-%D9%87%D8%A7-%D8%A8%D8%AE%D8%B4-%D8%A7%D9%88%D9%84-cte-%D8%BA%DB%8C%D8%B1-%D8%A8%D8%A7%D8%B2%DA%AF%D8%B4%D8%AA%DB%8C-dqsopqlopi20 امروز میخواهیم با یکی از انواع Table Expressions ها که به شدت کار برد دارد آشنا شویم.تکنولوژی CTEs یا همان (Common Table Expressions) از نسخه ی SQL Server 2005 به رسمیت شناخته شد و شاملیک Result Set موقتی است از این رو میگوییم موقتی که در قالب یک Object در دیتا بیس ما ذخیره نمیشودو با اتمام دستور از بین میرود. از CTEs میتوان در دستورات INSERT , UPDATE, DELETE , SELECT و همچنین در دستور CREATE VIEW و SELECT مربوط به آن استفاده کرد . در نسخه SQL SERVER 2008از CTE میتوان در دستور MERGE هم بهره برد.( و اینکه دستور MERGE چیست در پست های بعدی به آن اشاره خواهیم کرد.) در SQL Server ما از دو نوع CTE به نام های CTE بازگشتی و غیر بازگشتی استفاده میکنیم که در این مقاله ما صرفا به بحث در رابطه با CTE غیر بازگشتی , ساختار , نحوه استفاده , و در آخر به مثالی از آن میپردازیم و در پست بعدی به طور کامل از نوع بازگشتی آن صحبت خواهیم کرد.حال میپردازیم به بحث CTE غیر بازگشتی :CTE Scriptدر واقع یکی از کاربرد های CTE ایجاد ماژول های کوچکی است که امکان استفاده مجدد را به شما داده و سبب خواناتر شدن کوئری های پیچیده می شود و دیگر کار برد آن میتوان زمانی که شما به ساخت یک View احتیاجی ندارید ولی قصد استفاده از مزایای یک View را دارید , اشاره کرد .ساده ترین ساختار یک CTE غیر بازگشتی به صورت زیر می باشدNon-Recursive CTEنکات خیلی مهمی که باید در رابطه با CTE ها بدانیم عبارت است از :داخل CTE به هیچ عنوان نمیتوانیم از ORDER BY استفاده کنیم مگر آنکه با TOP یا OFFSET FETCH بیاید.تمام فیلدهای داخل CTE باید دارای نام باشند به عبارتی no column name نباشند.تمام اسامی فیلدهای داخل CTE باید نام های منحصر به فرد داشته باشند .حال با استفاده از دیتابیس Northwind میخواهیم به مثال هایی از CTE بپردازیممثال اول : فراخوانی فیلد هایی از جدول Customers Scriptخروجی کوئری بالا بصورت زیر میباشد .Resultمثال دوم : تمام مشتریانی که بیش از 15 سفارش داشته اند. (تمام مشتریان در جدول Customers و سفارشات آن در جدول Orders می باشد.)Scriptنتیجه کوئری بالا به صورت زیر میباشد.Resultتوجه داشته باشید هرگاه خواستید از CTE های تو در تو استفاده کنید با یک " , " از هم جدا میکنیم .در CTE های تو در تو هر بخش به بخش های بالایی خود دسترسی دارد و در نهایت Outer Query بیرون از CTE به همه بخش ها دسترسی دارد. ساختار یک CTE تو در تو به شکل زیر میباشد .Nested CTE علیرضا حیدری علیرضا حیدری Mon, 02 Dec 2019 18:44:52 +0330