SQL Server جستجو و فیلتر تاریخ با WHERE DATE بهینه سازی کوئری

جستجوی تاریخ در SQL Server: راهنمای جامع WHERE DATE و بهینه‌سازی کوئری‌ها

در SQL Server، عبارت WHERE برای فیلتر کردن داده‌ها بر اساس یک شرط مشخص استفاده می‌شود. وقتی صحبت از فیلتر کردن بر اساس تاریخ به میان می‌آید، درک نحوه صحیح استفاده از این عبارت برای اطمینان از دقت نتایج و عملکرد بهینه کوئری‌ها بسیار مهم است.

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

فیلتر کردن تاریخ‌ها با عبارت WHERE

برای فیلتر کردن تاریخ‌ها با عبارت WHERE، می‌توانیم از عملگرهای مقایسه‌ای مانند `=`، `>`، `=`, `<=` و “ (یا `!=`) استفاده کنیم. بیایید با یک مثال شروع کنیم. فرض کنید جدولی به نام `Orders` داریم که یک ستون `OrderDate` از نوع `DATETIME` یا `DATE` دارد. برای یافتن تمام سفارشات مربوط به یک تاریخ خاص، مثلاً اول ژانویه ۲۰۲۳، می‌توانیم از کوئری زیر استفاده کنیم:

SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE OrderDate = '2023-01-01';

این کوئری تمام ردیف‌هایی را برمی‌گرداند که ستون `OrderDate` دقیقاً برابر با ‘2023-01-01’ باشد.

فیلتر کردن تاریخ‌ها از یک محدوده

اغلب نیاز داریم داده‌ها را در یک محدوده تاریخ فیلتر کنیم. برای این کار، می‌توانیم از عملگر `BETWEEN` یا ترکیبی از عملگرهای مقایسه‌ای استفاده کنیم. برای مثال، اگر می‌خواهید سفارشات مربوط به بازه اول ژانویه ۲۰۲۳ تا ۳۱ ژانویه ۲۰۲۳ را فیلتر کنید، می‌توانید کوئری زیر را بنویسید:

SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-01-31';

یا با استفاده از عملگرهای مقایسه‌ای:

SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE OrderDate >= '2023-01-01' AND OrderDate <= '2023-01-31';

توجه داشته باشید که `BETWEEN` شامل هر دو تاریخ شروع و پایان می‌شود. اگر ستون `OrderDate` شامل مؤلفه زمان باشد (مثلاً از نوع `DATETIME` باشد)، `’2023-01-31’` به عنوان `’2023-01-31 00:00:00.000’` تفسیر می‌شود و ممکن است رکوردهای ۳۱ ژانویه با زمان‌های بعد از نیمه‌شب را نادیده بگیرد. برای پوشش کامل روز پایانی، بهتر است از رویکرد زیر استفاده کنید:

SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE OrderDate >= '2023-01-01' AND OrderDate < '2023-02-01';

این روش تضمین می‌کند که تمام رکوردهای مربوط به ژانویه ۲۰۲۳، شامل زمان‌های مختلف روز پایانی، بازیابی شوند.

فیلتر کردن تاریخ‌ها قبل و بعد از یک تاریخ خاص

برای یافتن تاریخ‌هایی قبل یا بعد از یک تاریخ مشخص، می‌توانیم از عملگرهای `>`، `=`, `<=` استفاده کنیم. برای یافتن سفارشات قبل از اول ژانویه ۲۰۲۳:

SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE OrderDate < '2023-01-01';

برای یافتن سفارشات از اول ژانویه ۲۰۲۳ به بعد:

SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE OrderDate >= '2023-01-01';

فیلتر کردن تاریخ‌ها با توابع تاریخ

SQL Server توابع متعددی برای کار با تاریخ و زمان ارائه می‌دهد که می‌توانند برای فیلتر کردن پیشرفته‌تر استفاده شوند.

تابع CONVERT

تابع `CONVERT` برای تبدیل یک عبارت از یک نوع داده به نوع دیگر استفاده می‌شود. این تابع می‌تواند برای تبدیل `DATETIME` به `DATE` یا به فرمت‌های رشته‌ای خاص مفید باشد. برای تبدیل ستون `DATETIME` به `DATE` و مقایسه آن:

SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE CONVERT(DATE, OrderDate) = '2023-01-01';

توجه داشته باشید که استفاده از توابع مانند `CONVERT` روی ستون‌های ایندکس شده در عبارت WHERE می‌تواند مانع از استفاده بهینه از ایندکس شود و منجر به “اسکن جدول” (table scan) به جای “جستجوی ایندکس” (index seek) گردد که عملکرد را تحت تأثیر قرار می‌دهد.

تابع CAST

تابع `CAST` نیز مشابه `CONVERT` عمل می‌کند و یک عبارت را به نوع داده مشخصی تبدیل می‌کند. برای تبدیل `DATETIME` به `DATE` با `CAST`:

SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE CAST(OrderDate AS DATE) = '2023-01-01';

این نیز می‌تواند همان مشکل عملکردی را که برای `CONVERT` ذکر شد، ایجاد کند.

تابع FORMAT

تابع `FORMAT` یک عبارت را با فرمت مشخص شده و فرهنگ (locale) اختیاری فرمت می‌کند. این تابع برای نمایش داده‌ها به فرمت خوانا کاربرد دارد، اما استفاده از آن در عبارت WHERE برای فیلتر کردن تاریخ توصیه نمی‌شود زیرا به شدت بر عملکرد تأثیر می‌گذارد.

SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE FORMAT(OrderDate, 'yyyy-MM-dd') = '2023-01-01';

این روش به دلیل سربار زیاد تابع `FORMAT` در هر ردیف، بسیار کندتر از روش‌های دیگر است.

توابع DATEFROMPARTS، DATETIMEFROMPARTS و TIMEFROMPARTS

این توابع به شما امکان می‌دهند تا یک تاریخ یا زمان را از اجزای مختلف (سال، ماه، روز، ساعت، دقیقه و…) بسازید. این توابع برای ایجاد مقادیر تاریخ-زمان دقیق برای مقایسه مفید هستند. تابع `GETDATE()` تاریخ و زمان جاری را برمی‌گرداند:

YYYY-MM-DD HH:MI:SS.MMM

می‌توانید از توابع `DATEFROMPARTS` یا `DATETIMEFROMPARTS` برای ساخت یک تاریخ-زمان مشخص استفاده کنید. به عنوان مثال، برای فیلتر کردن بر اساس سال:

SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE OrderDate >= DATEFROMPARTS(2023, 1, 1) AND OrderDate < DATEFROMPARTS(2024, 1, 1);

این رویکرد به SQL Server اجازه می‌دهد تا از ایندکس‌ها به طور موثر استفاده کند زیرا هیچ تابعی روی ستون `OrderDate` اعمال نمی‌شود.

ملاحظات عملکردی در فیلتر کردن تاریخ

برای بهینه‌سازی عملکرد کوئری‌های فیلتر کننده تاریخ، نکات زیر را در نظر بگیرید: * **از توابع روی ستون‌های ایندکس شده خودداری کنید:** همانطور که قبلاً اشاره شد، اعمال توابع (مانند `CONVERT`, `CAST`, `FORMAT`, `YEAR`, `MONTH`, `DAY`) روی ستون‌های تاریخ در عبارت WHERE می‌تواند مانع از استفاده SQL Server از ایندکس‌های موجود شود. * **با محدوده تاریخ به صورت باز کار کنید:** به جای استفاده از `BETWEEN` با تاریخ نهایی، از `<` برای روز بعد از محدوده استفاده کنید تا تمام زمان‌های روز آخر را پوشش دهید و از مشکلات مربوط به مؤلفه زمان جلوگیری کنید.

    WHERE OrderDate >= '2023-01-01' AND OrderDate < '2023-02-01'
    

* **از پارامترها استفاده کنید:** همیشه برای مقادیر تاریخ در کوئری‌های خود از پارامترها استفاده کنید. این کار به جلوگیری از حملات SQL Injection کمک می‌کند و به SQL Server اجازه می‌دهد تا طرح‌های اجرایی کوئری (query execution plans) را کش کند، که منجر به بهبود عملکرد می‌شود.

    DECLARE @StartDate DATE = '2023-01-01';
    DECLARE @EndDate DATE = '2023-02-01';

    SELECT OrderID, OrderDate, CustomerID
    FROM Orders
    WHERE OrderDate >= @StartDate AND OrderDate < @EndDate;
    

با رعایت این اصول، می‌توانید اطمینان حاصل کنید که کوئری‌های فیلتر کننده تاریخ شما در SQL Server هم دقیق و هم از نظر عملکرد بهینه هستند.