SQL Server 2014窗口函数查询如何智能利用索引避免全表扫描？

Great question—this is a common pain point when trying to pull top-N-per-group data with window functions in older SQL Server versions. Let’s break down why your index is being ignored and how to fix it without resorting to that clunky multi-step query.

Why Your Index Isn’t Being Used #

The index you created (idx_VehicPosition on VehicleID, Timestamp) is a solid starting point, but SQL Server 2014’s query optimizer might skip it for two key reasons:

• It’s not a covering index: Your query uses SELECT *, which means it needs every column from the table. The existing index only includes VehicleID and Timestamp—the optimizer would have to do a key lookup to fetch the rest of the columns, which can be slower than a full table scan for large datasets.
• Index order mismatch: Your window function orders by Timestamp DESC, but your index doesn’t explicitly define this sort order. While SQL Server can reverse the index order on the fly, matching the index sort to your query helps the optimizer recognize it as a more efficient option.

Fixes to Make the Window Function Query Use the Index #

1. Create a Covering Index with Matching Sort Order #

The simplest and most sustainable fix is to update your index to be a covering index that includes all columns your query needs, plus explicitly sorts Timestamp in descending order. Here’s the syntax:

CREATE INDEX idx_VehicPosition_Covering 
ON VehiclePositions(VehicleID, Timestamp DESC)
INCLUDE (Id, PositionX, PositionY, PositionZ, Speed, Heading);

• VehicleID first in the index enables efficient partitioning by each vehicle.
• Timestamp DESC matches the ORDER BY in your window function, so the index is already sorted perfectly for ranking records.
• The INCLUDE clause adds all other columns required for your SELECT *, turning this into a covering index—no extra key lookups needed.

2. Refresh Table Statistics #

Outdated statistics can lead the query optimizer to make poor execution plan choices. Run this to refresh stats for your table:

UPDATE STATISTICS VehiclePositions;

This helps SQL Server understand your data’s distribution and recognize that using the covering index is more efficient than a full table scan.

3. Use an Index Hint (Last Resort) #

If the optimizer still refuses to use the covering index (unlikely, but possible in edge cases), you can force it with an index hint:

SELECT * 
FROM (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY VehicleID ORDER BY Timestamp DESC) AS ranking 
    FROM VehiclePositions WITH(INDEX(idx_VehicPosition_Covering))
) AS x 
WHERE ranking = 1;

Note: Index hints should be a last resort. They can become problematic if your data distribution or schema changes later, as the optimizer can’t adapt to new conditions automatically.

Why This Works Better Than the Clunky Query #

Your original alternative query uses correlated subqueries and a temp table, which can be slower for large datasets and harder to maintain. The window function approach is cleaner and more readable, and with the right covering index, it will perform just as well (if not better) by leveraging efficient index seeks/scans instead of repeated subqueries.

内容的提问来源于stack exchange，提问作者Barry Smith