ORDER and filter by add date while CLUSTERED IDENTITY PRIMARY KEY on row Id

Question

I have a table (Orders) with CLUSTERED IDENTITY PRIMARY KEY (OrderId) and I am filtering and sorting the data by add date column (AddDate). Is there a way to tell the query optimizer that the AddDate is ordered the same way the OrderId is (so the data is ordered by AddDate already)?

SQL Server does not really need to Scan whole table and then sort it. All the operation requires is to Scan the table until end date is found, then filter out data before start date and return it as is (without sorting it).

Example:

SELECT
      *
    FROM Orders
    WHERE AddDate BETWEEN @FromDate AND @ToDate
    ORDER BY AddDate

Answer 1

Is there a way to tell the query optimizer that the AddDate is ordered the same way the OrderId is (so the data is ordered by AddDate already)?

No, there isn't any way to do that.

However you can order by OrderId instead of by AddDate, if AddDate is ordered the same way as OrderId, it would return the same results. But unfortunatly, SQL Server would scan the entire table anyway.

Let't take Northwind Orders tables and OrderDate column.

The query:

SELECT *
FROM dbo.Orders
WHERE OrderDate BETWEEN '1997-12-10' AND '1998-03-05'
ORDER BY OrderDate

Produces this plan. It scans the clustered index entirely while applying a filter, then order the result.

The query:

SELECT *
FROM dbo.Orders
WHERE OrderDate BETWEEN '1997-12-10' AND '1997-12-17'
ORDER BY OrderId -- It's equivalent to ordering by OrderDate

Produces this plan. It also scans the clustered index entirely and apply a filter, but it doesn't order.

Having OrderDate as the clustered index key would drastically improve the performance of the query, but you might not want to have such a clustered index key. However you cloud create a covering index that would also drastically improve the performance:

CREATE INDEX IX_Orders_OrderDate ON dbo.Orders(OrderDate)
INCLUDE ([OrderID], [CustomerID], [EmployeeID], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry])

The query:

SELECT *
FROM dbo.Orders
WHERE OrderDate BETWEEN '1997-12-10' AND '1998-03-05'
ORDER BY OrderDate

Produces this plan. It just seeks the index. It cannot be faster.

But that index is fat, it would penalize data modifications.

However, you could take advantage of a thinner index like the following:

CREATE INDEX IX_Orders_OrderDate ON dbo.Orders(OrderDate, OrderId)

Using a query like this:

DECLARE @FromOrderId int, @ToOrderId int;
SELECT TOP (1) @FromOrderId = OrderId FROM dbo.Orders WHERE OrderDate <= '1997-12-10' ORDER BY OrderDate DESC, OrderId DESC;
SELECT TOP (1) @ToOrderId = OrderId FROM dbo.Orders WHERE OrderDate >= '1998-03-05' ORDER BY OrderDate ASC, OrderId ASC;

SELECT *
FROM dbo.Orders
WHERE 
    (OrderId  >= @FromOrderId OR @FromOrderId IS NULL)
    AND (OrderId <= @ToOrderId OR @ToOrderId IS NULL)
ORDER BY OrderID
OPTION (RECOMPILE)

It produces this plan. It just need 3 seeks to solve the query.

Answer 2

There is no more you can do. First of all if you know that this is sorted the same way then there is no reason why would you order your result set by AddDate instead of OrderId. Secondly if you know about this relation you can just get the OrderId assigment for the date frame you are after as @Jesus showed you. Best method will be to create additional index on OrderDate (only). For sake of such a simple query this will work best, no need to include extra columns, PK will be covered in the background anyway and based of in the whole filtering will be applied. Last option you have which still will bring some improvements (not as good as index, but still can be helpful) is to add extra statistics on the OrderDate column, based on them the SQL Server kardionality estimator will be able to produce better plan wchich should drive to better performance.

Answer 3

You could add a non clustered index on AddDate, OrderId:

CREATE INDEX IX_Orders_AddDate_OrderID
ON dbo.Orders(AddDate, OrderID)

Then rewrite your query:

SELECT       *
FROM Orders
WHERE OrderId >=
           (SELECT MIN(OrderId)
            FROM dbo.Orders
            WHERE AddDate >= @FromDate) AND
      OrderId <=
           (SELECT MAX(OrderId)
            FROM dbo.Orders
            WHERE AddDate <= @ToDate)
ORDER BY AddDate