Question
You have an Azure SQL database that contains a table named dbo.Orders. dbo.Orders contains a column named CreateDate that stores order creation dates.
You need to create a stored procedure that filters Orders by CreateDate for a single calendar day. The solution must be SARGable.
How should you complete the Transact-SQL code? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
```sql
CREATE PROCEDURE dbo.usp_SearchOrders
@StartDate date
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EndDate date;
SET @EndDate = [Target 1]
SELECT o.CreateDate,
o.OrderId,
o.ShipDate
FROM dbo.Orders AS o
WHERE o.CreateDate >= [Target 2]
AND o.CreateDate < [Target 3];
END;
GO
```
Explanation
Target 1 is DATEADD(day, 1, @StartDate), which sets @EndDate to the start of the following day. Target 2 is @StartDate and Target 3 is @EndDate, which together form a half-open range that covers the whole calendar day, including any time portion stored in CreateDate. The predicate is SARGable because the column stands alone on one side of each comparison, so the optimizer can seek on an index. CONVERT(char(10), CreateDate, 121) is wrong because wrapping the column in a function prevents an index seek, and GETDATE() ignores the requested day.
