اشتباهات رایج در SQL SERVER و راه های اجتناب از آنها

در این فصل به پاره ایی از اشتباهات رایج در SQL Server می پردازیم

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

1. فراموش کردن کلید اصلی (Primary Key)

هر جدولی احتیاج به کلید اصلی دارد تا بتوان رکوردی را بطور منحصر به فردی در آن جدول مشخص کرد.
همچنین تاثیر مهم یک کلید اصلی پرفورمنس (همون Performance) بالای آن است که بدلیل ایجاد Clustered index بروی آن می باشد. حال اگر در جداولمان کلید اصلی نداشته باشیم علاوه بر مشکلاتی که در یافتن رکورد ها داریم کوئری های ما پرفورمنس پایینی خواهند داشت. تعریف کلید های اصلی مناسب و صحیح نخستین گام در طراحی کارامد یک پایگاه های داده رابطه ایی (Relational Database) هستند. فرض کنید جدولی از مشتریان داریم و می خواهیم یک مشتری خاصی را از آن بیرون بکشیم. می توانیم مثلا به هر مشتری یک کد منحصر به فرد اختصاص دهیم (CustomerID) . این کد می تواند حتی بصورت اتوماتیک در زمان درج یک مشتری در دیتابیس توسط موتور پایگاه داده ایجاد شود (Identity)
. بنابراین وقتی اطلاعات مشتری خاصی را نیاز داریم با حداکثر سرعت و کمترین منابع می توانیم آن را از جدول مربوطه بیرون بکشیم.
جهت توضیحات مفصل تر به فضل ششم دوره آموزش SQL Server مراجه کنید. ساخت جدول (Table) در SQL Server

2. افزونگی اطلاعات (Data Redundancy)

هر جدولی احتیاج به کلید اصلی دارد تا بتوان رکوردی را بطور منحصر به فردی در آن جدول مشخص کرد.
همچنین تاثیر مهم یک کلید اصلی پرفورمنس (همون Performance) بالای آن است که بدلیل ایجاد Clustered index بروی آن می باشد. حال اگر در جداولمان کلید اصلی نداشته باشیم علاوه بر مشکلاتی که در یافتن رکورد ها داریم کوئری های ما پرفورمنس پایینی خواهند داشت.
تعریف کلید های اصلی مناسب و صحیح نخستین گام در طراحی کارامد یک پایگاه های داده رابطه ایی (Relational Database) هستند.
فرض کنید جدولی از مشتریان داریم و می خواهیم یک مشتری خاصی را از آن بیرون بکشیم. می توانیم مثلا به هر مشتری یک کد منحصر به فرد اختصاص دهیم (CustomerID) . این کد می تواند حتی بصورت اتوماتیک در زمان درج یک مشتری در دیتابیس توسط موتور پایگاه داده ایجاد شود (Identity).
بنابراین وقتی اطلاعات مشتری خاصی را نیاز داریم با حداکثر سرعت و کمترین منابع می توانیم آن را از جدول مربوطه بیرون بکشیم.
جدول ، رابطه و نرمالسازی (Relation & Relationship) در SQL Server

3. تا جایی که می توانید از NOT IN و IN استفاده نکنید و از JOIN استفاده کنید.

عبارات NOT IN و IN خیلی بهینه نمی باشند و بسیار هم رایج هم هستند ولیکن خیلی از مواردی که ما آنها را با IN و NOT IN پیاده سازی میکنیم با اندکی تفکر و حوصله می توانند با JOIN جایگزین شوند. کوئری زیر را ببینید :

SELECT * FROM Customer
WHERE CustomerId NOT IN (SELECT CustomerId FROM Order)

کوئری بالا تمام مشتریانی را برای ما برمیگرداند که سفارشی نداشته اند در این کوئری موتور پایگاه داده اول تمامی سفارشات را از جدول Order بیرون میکشد (تعداد سفارشات معمولا خیلی زیاد هستند به نسبت تعداد مشتریان فرض کنید یک فروشگاه بزرگ آنلاین داریم و ده ها میلیون رکورد سفارش !)
بعد موتور پایگاه داده کوئری بیرونی را اجرا میکند و رکورد هایی را که فیلد CustomerID انها درکوئری داخلی نیستند را بیرون میکشد. که روش بسیار کند و ناکارامدی است. چه کنیم ؟ راه بهتری وجود دارد کوئری زیر را ببینید

SSELECT * FROM Customer c
LEFT JOIN Order o on c.CustomerId = o.CustomerId
WHERE o.CustomerId IS NULL

جواب این کوئری با کوئری قبلی یکیست. ولی بسیار بهینه تر و سرعتر و ارزانتر است (ارزانتر یعنی منابع کمتری شامل رم ، سی پی یو و هارد را مصرف میکند). بررسی انواع JOIN در SQL Server

4. Null در مقابل یک رشته خالی

برای چند دهه در مورد استفاده از NULL و یا یک رشته خالی بین طراحان دیتابیس بحث و جدل در جریان است نکته مهم این است که اگر برای یک مقدار نامعلوم در جدولی از NULL استفاده میکنید در همه جا اینکار را انجام دهید. و یا اگر از یک رشته خالی استفاده میکنید و یا در یک فیلد عددی می خواهید 0 بگذارید. همه جا اینکار را بکنید تا در هنگام کوئری نویسی مطمئن باشید که باید از IS NULL باید استفاده کنید و نه <>’’ .
این موضوع یکی از مشکلات رایج در کوئری ها می باشد .
فرض کنید در جدول مشتریان فیلد نام خانوادگی را Allow null در نظر گرفته اییم . حال می خواهیم تمام مشتریانی را بدست آوریم که نام خانوادگی آنها وارد نشده است براحتی با استفاده از IS NULL این کوئری را می نویسیم.در جدول سفارشات نیز فرض کنید فیلدی رشته ایی بنام توضیحات سفارش داریم که آن هم Allow null هست.
چون بعنوان یک استاندارد در سطح جداول دیتابیسمان در نظر گرفته اییم که اگر مقدار رشته ایی وارد نشده بود آن فیلد NULL باشد و نه یک رشته خالی’’ بنابراین در تمامی کوئری ها از IS NULL استفاده میکینم. و لازم نیست هر بار چک کنیم که در آن جدول چه طراحی داشته ایم.
را عایت نکردن این موضوع باعث میشود که کوئری هایی داشته باشیم که خطا ندارند واجرا می شوند ولیکن دیتای صحیح را باز نمی گردانند. (دارند دروغ میگند !)

مفهوم null
یکی از مفاهیم پایه ایی پایگاه داده مفهوم null است Null : عبارتست از مقداری که نمی دانیم چیست . مقدار نامعلوم . نامشخص (UNKNOWN). مقدار null با عدد صفر و یا یک رشته خالی متفاوت است . چون null مقداری نامشخص می باشد پس هیچ دو مقدار null ایی با هم برابر نیستند . فیلد های یک جدول بطور پیشفرض می توانند مقدار null را داشته باشند.

5. استفاده از * در کوئری ها

همیشه ستونهایی که می خواهید در نتیجه کوئری مشاهده کنید را در سلکت بیاورید و نه همه را خیلی وقت ها برای راحتی کار (تنبلی) بجای ذکر ستون های مورد نیاز ، خیلی راحت میایم کار رو با یک * از سر خودمون باز میکنیم ، فیلد هایی که اصلا لازم نداریم را بدون دلیل از دیتابیس بیرون میکشیم . حالا فرض کنید که این فیلدها شامل دیتای حجیمی مثل nvarchar(MAX) باشند.
یه تست ساده انجام بدید. یک جدول بسازید با مثلا 1000 رکورد (با کد در جدول دیتا بزیند) در طراحی جدول چند تا فیلد nvarchar(max) هم بگذارید و اونا رو با یک دیتای متنی مثلا محتویات HTML یک صفحه وب پر کنید. حالا روی خیلی ساده Task Manager ویندوز رو باز کنید ببینید الان سرویس SQL Server چقدر رم مصرف میکنه (راه های دیگری هم هست) حالا یک select * روی جدول بزنید . اجرای کوئری که تمام شد دوباره مقدار رم مصرفی سرویس رو برید چک کنید. از منابع سرور بی دلیل استفاده کردیم و یک کوئری غیر بهینه نوشتیم.

در لیست فیلد هایی که در SELECT می نویسید خسیسسسسسسس باشید. فقط ستون هایی رو ذکرکنید که لازم دارید و ضروری هستند.

6. استفاده بیش از حد از کرسر Cursor

کرسر ها جهت کار بروی رکورد های جداول بصورت یکی یکی بکار میروند . بجای اینکه دستوری بنویسیم که اطلاعات رو بصورت یک Batch (بَچ) ازجداول استخراجی کنیم گاهی از کرسر استفاده میکینم که یک شرط خاص را بروی رکورد ها بصورت یکی یکی چک میکند. و یا یک عمل خاص را یدونه یدونه بروی رکورد ها انجام میدهد. در برخی از موارد مجبور به استفاده از کرسر ها هستیم ولیکن اگر بتوان عملی را با کرسر و بدون کرسر انجام داد اولویت با روش بدون کرسر است. در برخی موارد دیده شده اجرای یک کوئری با کرسر بیش از 20 بار کند تر از اجرای معادل اون بدون کرسر هست.
در برخی موارد مثلا ساخت گزارشات پیچیده ناچار هستیم از کرسر ها استفاده کنیم . بنابراین این اعمال را می توان مثلا در زمانی که بار روی سرور کم است انجام داد و نتیجه را جایی ذخیره کرد.ولی اینکه مثلا در یک سایت پر بازدید در لود صفحات اطلاعات قابل نمایش را با کرسر از دیتابیس بیرون بکشیم و بازای هر رکوئست کاربر این کرسر اجرا شود. وای به حالمون! Cursor در SQL Server

7. تعیین نوع داده (Data Type) مناسب

وقتی جداول دیتابیس را طراحی میکنید درهنگام تعریف ستونها وقتی میخایید نوع داده یا همون دیتاتایپ را برای ستونها تعریف کنید، دقیق باشید. می تونید در سایت ماکروسافت و سایتهای راهنمای سایر دیتابیس ها انواع دیتا تایپ های مختلف و محدوده مقادیری که در خود جا میدهند را ببینید.
وقتی میخواهیم لیست استانهای ایران را در جدولی نگهداریم چرا باید برایی فیلد کلید اصلی ID از Bigint استفاده کنیم که بازای هر فیلد از هر رکورد 8 بایت فضا اشغال کنیم. ما میدانیم تعداد استان های ما 31 عدد است در Bigint می توان عددی به بزرگی 2 به توان 64 را نگهداشت (9223372036854775807). مث این می مونه برای خوردن یک لیوان شیر شما بری یک کارخونه شیر بخری!
این مسئله خیلی رایج است. اگر می خواهید به طراحی دیتابیس یک نرم افزار نمره بدهید یکی از فاکتور های مهم شما باید بررسی دیتاتایپ های فیلد ها باشد. این موضوع در پرفورمنس دیتابیس و در نهایت کل نرم افزار خیلی خیلی خیلی خیلی بازم خیلی مهم است !

در اختصاص دیتاتایپ ها ول خرجی نکنید. و وسواس داشته باشید (قول میدم ضرر نکنید)

وقتی می خواهیم کد ملی را نگهداری کنیم چرا باید یک فیلد nvarchar(50) در نظر بگیریم؟ خیلی ها معتقدند بزار دیتاتایپ ها رو بزرگ بگیریم تا یه موقع کم نیاریم. خوب این نتیجش دیتابیس های حجیم ناکارآمد و کند است. البته آینده نگری جای خود را دارد !
موضوع مهم بعدی انتخاب دیتاتایپ مناسب با توجه به دیتای ماست .فرض کنید می خواهیم مقادیر اعشاری را نگهداری کنیم. اگر یک دیتا تایپ از گروه (bigint,int,smallint,tinyint) در نظر بگیریم. در هنگام درج یک مقدار اعشاری یا به خطا بر میخوریم یا مقدار اعشار ما در فیلد ذخیره نمی شود.همچنین در هنگام تعریف پارامتر ها در stored procedure نیز باید دقت لازم را داشته باشیم.

8. اپراتور های منطقیOR و AND

ترتیب ارزیابی اپراتور های منطقیor و and به اینصورت است که اول AND بررسی می شود و بعد OR این یکی از رایج ترین خطا ها در نوشتن کوئری هاست که باعث میشود کوئری ما اطلاعات صحیح را باز نگرداند. (کار میکنه ولی دروغ میگه!)
همچنین وقتی در یک عبارت اپراتور AND داریم وقتی نتیجه صحیح است که دوطرف درست باشند
در اپراتور OR حتی اگر یکطرف گزاره هم درست باشد نتیجه درست است.
به کوئری زیر توجه کنید:

SELECT CustomerId
FROM Customer
WHERE FirstName = N'آرش' AND FirstName = N'کوروش’ OR CustomerId > 100

می خواهیم لیست مشتریانی را ببینیم که نام آنها ‘آرش’ و یا ‘کوروش’ است کد مشتری آنها بزرگتر از 100 است.
حالا فرض کنید در جدول مشتریان ما 10 مشتری داریم که نام آنها ‘آرش’ و یا ‘کوروش’ است که شماره مشتری 8 تا از آنها زیر 20 و 2 تای دیگر بالای 100 است .
آیا کوئری بالا درست است ؟
موتور پایگاه داده اول اپراتور AND را بررسی میکند. پس حاصل عبارت قبل از OR یعنی :

FirstName = N'آرش' AND FirstName = N'کوروش’

مشتری که نام آن هم کوروش است و هم آرش! پس این عبارت همیشه غلط است . حالا بریم سراغ عبارت بعد از OR یعنی Customerid>100. خب این عبارت برای تمام مشتریانی که customerid آنها بزرگتر از 100 است درست می باشد. پس نتیجه کوئری ما تمام رکوردهای هست که ستون customerid آنها بزرگتر از 100 است و فرقی نمیکند که نام آنها چه باشد!
دوباره سعی کنیم

SELECT CustomerId
FROM Customer
WHERE FirstName = N'آرش' OR FirstName = N'کوروش’ AND CustomerId > 100

بجای اپراتور AND اول OR میگذاریم. (نام آنها یا آرش باشد یا کوروش) نکته بعد اینکه می خواهیم حتما آن مشتری کد مشتری بزرگتر از 100 داشته باشد.
جواب ؟!
موتور پایگاه داده اول اپراتور AND را بررسی میکند یعنی :

FirstName = N'کوروش’ AND CustomerId > 100

یعنی در مورد مشتریانی این شرط صحیح است که هم نام انها کوروش باشد و هم شماره مشتری آنها بزرگتر از 100. و حاصل با عبارت اول که نام آرش را چک میکند OR می شود .(یکی اگر درست بود نتیجه کل صحیح است). پس در نتیجه رکورد هایی را می آورد که نام آنها کوروش است و شماره مشتری آنها بزرگتر از 100 است بعلاوه همه آرش ها با هر شماره مشتری که باشند!
ای بابا! بازم نشد. کوئری از لحاظ سینتکس (Syntax) صحیح است ولی از نظر منطقی (Logical) خیر (کار میکنه ولی داره دروغ میگه !)
با استفاده از پرانتز می توانیم ترتیب ارزیابی اپراتور ها را تغییر دهیم.توجه کنید که اول داخل پرانتز ارزیابی میشود.
کوئری زیر را ببینید.

SELECT CustomerId
FROM Customer
WHERE (FirstName = N'آرش' OR FirstName = N'کوروش’) AND CustomerId > 100

اول داخل پرانتز را بررسی میکند. تمام مشتریانی که نام انها یا کوروش است یا آرش. حالا این مشتریان باید کد مشتریشان بزرگتر از 100 باشد.
فارسیش میشه همه آرش ها و یا کوروش ها رو بیار به شرطی که حتما شماره مشتری آنها بزرگتر از 100 باشد.
بالاخره شد !

9. ساب کوئری ها (Subqueries) باید یک رکورد بازگردانند.

ساب کوئری ها (یک select در دل یک select) راه بهینه ای برای نوشتن عبارات sql نیستند ولیکن در برخی موارد ناچار به استفاده از آنها هستیم.
وقتی از ساب کوئری استفاده میکینم باید حواسمان باشد که تنها یک رکورد را بازگرداند. مثال زیر رو ببینید:

SELECT CustomerId,
(SELECT OrderId FROM Order o WHERE c.CustomerId = o.CustomerId)
FROM Customer c

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

SELECT CustomerId,
(SELECT Top 1 OrderId FROM Order o WHERE c.CustomerId = o.CustomerId ORDER BY OrderDate )
FROM Customer c

در ساب کوئری بالا فقط یک رکورد را باز میگردانیم پس خطا نداریم.

10. عدم ایندکس گذاری در فیلد هایی که بروی انها JOIN میزنیم

برای اینکه کوئری های سریعی داشته باشیم جداول ما باید بطور صحیحی ایندکس گذاری شده باشند. فرض کنید 2 جدول داریم یکی جدول مشتریان Customer و دیگری جدول Order برای سفارشات ما کلید اصلی جدول مشتریان یعنی فیلد (CustomerID) را در جدول Order بعنوان یک کلید خارجی Foreign Key (فارِن کیی) نگهداری میکنیم تا بتوانیم با یک join سفارشات یک مشتری را بدست آوریم.
در هنگام join فیلد CustomerID از جدول مشتریان کلید اصلی می باشد پس یک clustered index بروی آن وجود دارد که می دانیم سریعترین نوع ایندکس است. حالا در جدول سفارشات چه ؟
وقتی join را می نویسیم باید فیلد CustomerID در دو جدول با هم مقایسه شوند. اگر در جدول سفارشات بروی فیلد CustomerID که یک Foreign Key است یک Nonclustered index بگذاریم این عمل باعث بهبود چشمگیر پرفورمنس join های ما می شود.
پس سعی میکنیم بروی فیلد های که در join قرار است با هم مقایسه شوند index بگذاریم. کلید های اصلی که ایندکس دارند ولی باید حواسمان به سایر فیلد ها ی شرکت کننده در join باشد!