نوشته های Ali Varzeshi

هنر مهار تله‌های NULL و Duplication در SQL Server

Ali Varzeshi — Thu, 09 Jul 2026 14:11:31 +0330

تصور کنید ساعت ۲ بامداد است؛ تلفن شما زنگ می‌خورد و مدیر مالی شرکت با لحنی مضطرب پشت خط است: «گزارش سود و زیان فصلی که نیم ساعت پیش برای هیئت‌مدیره فرستادیم با تراز مغایرت شدید دارد! اعداد بعضی از مشتریان بزرگ دقیقاً دو برابر ثبت شده و در برخی ردیف‌ها کلاً هیچ دیتایی نمایش داده نمی‌شود. سریعاً سیستم را بررسی کنید.»این یک داستان خیالی نیست؛ این سناریویی واقعی است که شاید هر کارشناس ارشد دیتابیسی حداقل یک‌بار در پروژه‌های مالی یا انبارداری بزرگ تجربه کرده باشد. ریشه چنین فاجعه‌ای معمولاً در Syntax Error یا کندی سرور نیست، بلکه در یک اشتباه منطقی بسیار ظریف هنگام استفاده از اپراتورهای ساده‌ای مثل NOT IN یا INNER JOIN نهفته است.در این مقاله می‌خواهیم پشت صحنه موتور SQL Server را کالبدشکافی کنیم و ببینیم چطور نوشتن چند خط کد به ظاهر ساده و تمیز، می‌تواند منطق کل سیستم را به بازی بگیرد.سناریوی اول: وقتی یک مقدار NULL ساده کل خروجی را غیب می‌کند!بیایید با یک درخواست ساده تجاری شروع کنیم. از ما خواسته‌اند لیستی از مشتریانی را آماده کنیم که محصول اصلی ما (مثلاً Widget) را خریده‌اند، اما هرگز پکیج سفر (Vacation) را تهیه نکرده‌اند.سریع دست به کیبورد می‌شویم و یک Query کلاسیک با استفاده از NOT IN می‌نویسیم:SELECT customerID, SUM(itemAmount) AS totalSpent FROM #widgetSales WHERE customerID NOT IN (SELECT customerID FROM #vacationPurchases) GROUP BY customerID; کد بدون هیچ خطایی کامپایل و اجرا می‌شود، اما نتیجه نهایی یک صفحه کاملاً خالی است! کل دیتا غیب شده است.با شک و تردید نگاهی به داده‌های Table سفرها می‌اندازیم و متوجه می‌شویم که به هر دلیلی (مثلاً خطای ثبت دستی یا سیستم‌های قدیمی)، یک Record در ستون customerID مقدار NULL گرفته است. چطور این یک دانه NULL کل خروجی ما را نابود کرد؟تحلیل فنی: دام مرگبار Three-Valued Logic (3VL)در SQL Server، مقدار NULL به معنی صفر یا خالی نیست؛ بلکه به معنای “Unknown” (نامشخص) است. موتور SQL وقتی به اپراتور NOT IN می‌رسد، آن را به یک زنجیره از شرط‌های منطقی با عملگر AND تبدیل می‌کندبر اساس قوانین ریاضی دیتابیس، هرگونه مقایسه مستقیم با یک چیز نامشخص، باز هم خروجی UNKNOWN می‌دهد. در نتیجه:customerID != NULL تبدیل می‌شود به UNKNOWN.در منطق بولین، وقتی شروط با AND به هم وصل می‌شوند، اگر حتی یک شرط به UNKNOWN ختم شود، کل عبارت ارزیابی نهایی TRUE نخواهد گرفت. بنابراین، شرط WHERE برای تک‌تک Rowهای جدول ریجکت شده و خروجی کاملاً سفید تحویل شما داده می‌شود.یک نصیحت تجربی: به عنوان قانون طلایی در SQL Server، هر زمان که خواستید از NOT IN استفاده کنید، ابتدا از خود بپرسید: «آیا این ستون در جدول مقابل می‌تواند NULL باشد؟» اگر پاسخ مثبت است یا از آن مطمئن نیستید، هرگز به سراغ NOT IN نروید.سناریوی دوم: گزارش‌های متورم و کابوس Row Duplicationحالا فرض کنید سناریو برعکس است؛ می‌خواهیم مجموع خرید مشتریانی را به دست آوریم که هم محصول را خریده‌اند و هم پکیج سفر را.این بار تصمیم می‌گیریم برای سرعت بیشتر یا خوانایی کد، از INNER JOIN استفاده کنیم:SELECT w.customerID, SUM(w.itemAmount) AS totalSpent FROM #widgetSales AS w INNER JOIN #vacationPurchases AS v ON v.customerID = w.customerID GROUP BY w.customerID; گزارش آماده می‌شود، اما در زمان بازبینی (Auditing) متوجه می‌شویم مجموع خرید مشتری شماره ۳ به جای ۷۵ دلار، به اشتباه ۱۵۰ دلار ثبت شده است!کجای کار ایراد دارد؟در طراحی دیتابیس، همیشه این فرض را نداریم که ستون‌ها دارای کلید اصلی یا Unique Constraint هستند. مشتری شماره ۳ در جدول سفرها دو Record دارد (یک بار به نیویورک رفته و یک بار به سیدنی). عملگر INNER JOIN کورکورانه به ازای هر سفر این مشتری، خریدهای او را در جدول مبدا متناظر می‌کند.این رفتار باعث ایجاد یک ضرب دکارتی محلی (Local Cartesian Product) در سطح Rowها شده و پیش از اینکه عملیات GROUP BY اتفاق بیفتد، رکوردهای مشتری ۳ را دو برابر می‌کند. در نهایت، تابع SUM روی دیتای تکراری اعمال شده و خروجی را به شدت متورم نشان می‌دهد.راهکار نجات‌بخش: ترکیب طلایی EXISTS و NOT EXISTSبرای اینکه به عنوان یک متخصص کاربلد، کدی بنویسید که در مقابل ساختار متغیر داده‌ها کاملاً مقاوم (Robust) باشد، بهترین انتخاب استفاده از EXISTS و NOT EXISTS در قالب Correlated Subquery است.بیایید هر دو سناریو را بازنویسی کنیم:۱. حل مطمئن فیلترینگ منفی با NOT EXISTS:SELECT w.customerID, SUM(w.itemAmount) AS totalSpent FROM #widgetSales AS w WHERE NOT EXISTS ( SELECT 1 FROM #vacationPurchases AS v WHERE v.customerID = w.customerID ) GROUP BY w.customerID; در این حالت، وجود مقادیر NULL در جدول سفرها هیچ اختلالی ایجاد نمی‌کند؛ زیرا شرط برابری داخل Subquery فقط روی مقادیر واقعی و غیر-Null ارزیابی می‌شود.۲. حل مشکل دیتای تکراری با EXISTS:SELECT w.customerID, SUM(w.itemAmount) AS totalSpent FROM #widgetSales AS w WHERE EXISTS ( SELECT 1 FROM #vacationPurchases AS v WHERE v.customerID = w.customerID ) GROUP BY w.customerID; در این ساختار، موتور SQL به محض اینکه اولین نشانه از خرید سفر را برای مشتری شماره ۳ پیدا کند، ارزیابی را برای آن مشتری متوقف کرده (اصطلاحاً Early Exit می‌کند) و مقدار منطقی TRUE را برمی‌گرداند. فرقی نمی‌کند مشتری ۱ سفر رفته باشد یا ۱۰۰ سفر؛ رکوردهای جدول مبدا به هیچ وجه تکثیر نمی‌شوند و محاسبات SUM کاملاً دقیق انجام می‌شود. (ضمناً بخش SELECT 1 صرفاً یک نگارش استاندارد است؛ موتور SQL بخش Projection زیر کوئری را نادیده می‌گیرد، به طوری که حتی اگر بنویسید SELECT 1/0 باز هم هیچ خطایی رخ نخواهد داد).نگاه عمیق‌تر از دیدگاه DBA و Performanceشاید بپرسید آیا این تغییر ساختار روی Performance و سرعت اجرای Query در حجم‌های بالا تأثیر منفی نمی‌گذارد؟پاسخ کوتاه: خیر، در اکثر مواقع وضعیت بهتر هم می‌شود.وقتی Query Optimizer با EXISTS مواجه می‌شود، در پشت صحنه و در Execution Plan کدهای شما را به اپراتورهای فیزیکی بسیار بهینه‌ای به نام Left Semi Join (برای EXISTS) و Left Anti Semi Join (برای NOT EXISTS) تبدیل می‌کند.برخلاف یک JOIN معمولی که تمام داده‌ها را برای ادغام فیزیکی لود می‌کند و Memory Grant زیادی می‌طلبد، این اپراتورها به محض پیدا کردن اولین تطابق، کار را رها می‌کنند. فقط به عنوان یک متخصص دیتابیس همواره به یاد داشته باشید که برای کارکرد ایده‌آل این اپراتورها در جدول‌های بزرگ، وجود یک Non-Clustered Index روی ستون شرط (در اینجا ستون customerID در جدول سفرها) الزامی است تا موتور به جای انجام عملیات سنگین Scan، با یک Index Seek سریع کار را در کسری از ثانیه انجام دهد.

مفهوم Logical Query Processing در SQL SERVER

Ali Varzeshi — Mon, 23 Mar 2020 20:19:24 +0430

شاید جالب باشه که با این مقدمه شروع کنیم که تعریف فرایند چیست و متشکل از چه اجزایی هست؟ یک فرایند در چه زمانی می تواند بهترین خروجی رو به شما بدهد و این که باید از چه بخش‌هایی تشکیل شده باشد و حتی چه قوانینی باید برای آن تعریف شود که کلیه این اجزا در این فرایند خروجی مطلوب رو ارایه بدهند و در نهایت حاصل این کار نتیجه خوبی داشته باشد. می خواهیم با چند تا مثال که شاید برای همه ما خیلی ملموس و واقعی باشه این موضوع رو مقداری موشکافی کنیم.یک باغبان رو در نظر بگیرید که با گونه‌های مختفی از گیاهان و درختان سروکار دارد، هر کدام از این درختان و گیاهان، ممکن است، در شرایط مختلفی نگهداری شوند. همچنین درختی ممکن است تا یک متر رشد کند و سپس ادامه این فرایند رشد، به شکل دیگری انجام شود یا درختی هم تا چندین سال، به رشد خود ادامه دهد و یا در فصل‌های مختلفی از سال به شیوه‌های مختفلی این فرایند متوقف شود و یا شکل ان عوض شود. همین فرایند را می‌توانیم در مورد کسوف و خسوف هم مشاهده کنیم که طبق یک سری قوانین و شرایطی که از پیش تعریف شده است ، در فازهای مختلف قبل و بعد و زمان خسوف می‌توانیم خروجی‌های مختلف این فرایند را مشاهده کنیم. پس اگر دقت کنید، در هر مرحله اجزایی در این فرایند شرکت می‌کنند که بر روی نتیجه فعلی تاثیراتی اعمال کرده و سپس از صحنه ممکن است خارج شوند یا به شکل دیگری، تاثیر خود را در مراحل دیگر بگذارند. در سناریو اول باغبان این فرایند ها را به درستی می شناسد و با انواع و اقسام گونه های گیاهی و درختان مختلف به خوبی آشنایی داشته و طی یک فرایند این مراحل رشد را برای درخت یا گیاه مورد نظرش زیر نظر دارد . در فرایند دوم هم سلسله عواملی محیطی با اولویت های مشخص و از پیش تعریف شده منجر به این پدیده زیبا خواهند شد .مراحل تکامل یک گیاه در فاز بندی های متفاوت رشد آناین موضوع به عنوان پیش زمینه ایی بود که با یک اصلاح تخصصی آشنا بشویم و آن مفهوم Logical Query Processing Phases یا اجرای منطقی فازهای اجرایی هست. در واقع فازهای اجرایی برای این عنوان می‌شود که اسکریپت اجرایی شما عملیاتی شده و خروجی نهایی رو نمایش دهد. در رابطه با این موضوع که که این منطق به چه شکل هست در ادامه مقاله به معرفی جزییات و بخش‌های مختلف آن خواهیم پرداخت.اگر اسکریپت زیر را بررسی و تحلیل کنیم متوجه می‌شویم که چندین عبارت کلیدی در این کوئری استفاده شده که بتوانیم مطابق با درخواستی که به ما اعلام شده است خروجی کار را استخراج کنیم. به صورت خلاصه اسکریپت زیر این خروجی را به ما ارایه می‌کند که ۱۰ نفر از پر سودترین بازاریاب‌های فروش شرکت را بتوانیم از دیتایی که داریم استخراج کنیم. ذکر این نکته لازم هست که بدانیم در یک فرایند فروش، اجزا و المان‌های مختلفی برای تحلیل می‌توانیم داشته باشیم. نظیر موارد زیر:کالا ها گروه کالا بازاریاب های فروش وسایل حمل و نقل شهر ها و مناطق طبقه بندی شده فروشو ...که تمرکز این کوئری بر روی تحلیل این موارد هست که بتوانیم با تحلیل ساده ایی بر روی عملکرد فروشنده ها یا بازاریاب های فروش به این هدف دست پیدا کنیم که به عنوان مثال در بحث پورسانت یا ارتقا شغلی به سمت های بالاتر برای این فروشنده ها به چه شکل این اطلاعات را تحلیل کنیم .یه نگاهی به این کوئری داشته باشید :SELECT * FROM ( SELECT DISTINCT TOP 10 s.[Salesperson Key] AS Salesperson, SUM(s.profit) AS profit FROM fact.Sale s INNER JOIN fact.Sale s1 ON s1.[sale key] = s.[Sale Key] INNER JOIN Dimension.Employee e ON s.[Salesperson Key] = e.[Employee Key] WHERE e.[Is Salesperson] = 1 GROUP BY s. [Salesperson Key] ORDER BY Salesperson ) A ORDER BY A.[Salesperson OFFSET 7 ROWS;معمولا نوع خواندن این مدل اسکریپت‌ها از SELECT شروع می‌شود و به سمت پایین اسکریپت، مرحله به مرحله، شرط‌ها یا گروه‌بندی‌های مختلف بر روی نتیجه نهایی اعمال می‌شود. ولی با این دید و نوع نگرشی که ما داریم Engine با این منطق این کوئری را پردازش نمی‌کند. در اینجاست که باید بررسی کنیم اولویت اجرای این کوئری با کدام کلمه کلیدی شروع شده و به چه شکل قرار هست این کوئری تحلیل شود. اگر با یک دید بالا به پایین به این قضیه نگاه کنیم، سبک نوشتن و توسعه کوئری‌ها به این صورت هست که ممکن یک سری از عبارت حتما نیاز هست ذکر شود و تعدادی از ان‌ها بسته به سناریو و تحلیلی که داریم نیاز می‌شود که استفاده شود. این ساختار به شکل زیر است:SELECT DISTINCT TOP FROM join ON WHERE GROUP BY WITH {CUBE | ROLLUP} HAVING ORDER BY خب حالا اگه قرار باشه اون منطقی که SQL SERVER داره برای اجرای Query پیاده سازی میکنه رو بدونیم به این شکل میشه : (8) SELECT (9) DISTINCT (11) (1) FROM (3) JOIN (2) ON (4) WHERE (5) GROUP BY (6) WITH {CUBE | ROLLUP} (7) HAVING (10) ORDER BY شماره‌هایی که در ابتدای هر خط نوشته شده است، مطابق با سبکی رایج کوئری‌نویسی در نظر گرفته شده. در واقع اولویت اجرای هر فاز را برای ما مشخص می‌کند. کوئری شما هر چه قدر که تو در تو نوشته شده باشد و Select‌های مختلفی در ابتدای آن باشد، تا زمانی که دیتا سورس یا مخزن کلی اطلاعات را پیدا نکند به این جستجو ادامه می‌دهد. یعنی FROM کوئری شما در اولین قدم باید پردازش شود و اطلاعات لازم برداشته شود . در مرحله بعد اگر در کوئری از یک نوع خاصی از Join ها استفاده شده باشد، نوبت به اجرای این فاز می‌رسد که اطلاعات این جداول ترکیب شود.و برای پیدا کردن ستون‌ها یا اطلاعات دیگر این ارتباط، حاصل شود. در این جا نمی‌خواهیم که انواع Join ‌ها را بررسی کنیم. فقط به این نکته دقت داشته باشید که منطق این موضوع را به خوبی متوجه شوید. به همین ترتیب هر چه قدر Join‌های مختلفی در این قسمت نوشته شده باشد، این ارتباطات توسط شرط‌هایی که در قسمت On اعمال می‌شود، می‌توانند با سایر جداول ارتباط گیرند. خب تا این مرحله ما با کلی جدول ارتباط گرفتیم که قرار هست که فیلتر‌هایی بر روی خروجی این مرحله اعمال کنیم. در مرحله بعد در صورتی که قرار هست کوئری‌های تحلیلی بنویسیم از عبارت‌های کلیدی Group By و اعمال شرط‌های آن استفاده می‌کنیم. سپس Engine به اولین سطر از کوئری شما رفته و تعداد رکورد‌های مد نظر را جهت نمایش ارایه خواهدکرد .. سپس این خروجی به اخرین سطر کوئری ارجاع داده شده تا مرتب ‌سازی ان انجام شده و در مرحله اخر هم مجدد به اولین خط رجوع می‌شود که مقادیر تکراری این اطلاعات به کاربر نمایش داده نشود. پس ذکر این نکته بسیار حائز اهمیت هست که حتما شماره هایی که در ساختار بالا مشاهده می کنید را به خوبی یاد گرفته که در چرخه اجرای این فرایند دقیقا بتوانید تحلیل های خود را از کوئری های نوشته شده به خوبی ارایه دهید .*** به طور کلی قاعده Logical Query Processing یا LQP به این موضوع اشاره می‌کند که رعایت حق تقدم و تاخر در اجرا تمامی این فرایند‌ها مطابق با یک قانون مشخص و با اولیوت های خاص در نظر گرفته شده است .تمامی این فرایند ها در واقع در Sql server تحت عنوان مرحله Parsing شناخته می شود که اولویت استفاده از این کلمات و درست استفاده شدن از آنها رعایت شده باشد . این مثال رو مشاهده کنید : SELECT ID, (Salary * 12) AS YearlySalary FROM Employee WHERE YearlySalary > 10000;اگه مطابق با منطقی که توضیح داده شد ، این عبارت رو اجرا کنید شاهد خطای زیر خواهید بود : nvalid column name ‘YearlySalary’.این خطا دقیقا نشان می‌دهد که اولا، کوئری شما از جدول Employee شروع به جمع آوری داده می‌کند و در مرحله بعد باید مطابق با منطق Lgoical query processing باید به قسمت where رفته و این خط را اجرا کند. ولی همان طور که مشاهده کردید، در این قسمت از الیاسی استفاده شده که هنوز در قسمت مورد نظر، پردازش نشده وSql server در جریان نیست که همچین الیاسی در کد به کار رفته که منظور همان YearlySalary هست. لذا تسلط بر این منطق، باعث می‌شود، زمانی که یک کوئری بسیار بزرگ را مشاهده کردید، بتوانید به راحتی تحلیل کنید که اولا چه اطلاعاتی از چه جداولی استخراج می‌شود و ثانیا این که در مرحله بعد قرار است چه فیلتر‌هایی بر روی این کوئری اعمال شود و به چه سبک گروه‌بندی‌های آن انجام و نتیجه نهایی مشخص شود. همچنین خطایابی و متوجه شدن منطق کوئری های بزرگ با این تکنیک که خدمت شما ارایه شد به راحتی قابل استخراج است . برای اشنایی با جزییات این موضوع می‌توانید به کتاب اقای Itzik Ben-Gan که با عنوان T-SQL Querying منتشر شده است مراجعه کنید و با چارت اجرایی این فرایند بیشتر اشنا شوید.Logical query processing phases لطفا اگه در این خصوص نظری داشتید حتما با بنده به اشتراک بزارید که بتونیم مقالات رو تخصصی تر ارایه بدیم . موفق و موید باشید