SQL Server大表中基于更新时间戳查询最新版本的最快方法

Hey George, let's tackle this performance issue you're facing with your weather forecast table. The inner join + max(UpdateTimestamp) approach works for small datasets, but it doesn't scale well as your table grows—here are the fastest, production-proven alternatives I've used for exactly this scenario:

This method lets you rank rows within each Date/City/Hour group by their UpdateTimestamp, then pick only the top-ranked (latest) row. It’s efficient because SQL Server can leverage targeted indexes to avoid full table scans.

CREATE OR ALTER VIEW vw_LatestWeatherForecast
AS
SELECT 
    Date,
    City,
    Hour,
    Temperature,
    UpdateTimestamp
FROM (
    SELECT 
        *,
        -- Assign rank: 1 = latest entry per Date/City/Hour
        ROW_NUMBER() OVER (
            PARTITION BY Date, City, Hour 
            ORDER BY UpdateTimestamp DESC
        ) AS rn
    FROM WeatherForecast
) AS subquery
WHERE rn = 1;

Critical Index Optimization #

To make this fly, create a covering index that lets SQL Server retrieve all needed data directly from the index (no "bookmark lookups" back to the main table):

CREATE NONCLUSTERED INDEX IX_WeatherForecast_PartitionSort 
ON WeatherForecast (Date, City, Hour, UpdateTimestamp DESC)
INCLUDE (Temperature); -- Include non-key columns needed for the view

If you prefer shorter code, this achieves the same result as ROW_NUMBER() but with more concise syntax. It works by returning all rows tied for the top rank defined in the ORDER BY clause.

CREATE OR ALTER VIEW vw_LatestWeatherForecast
AS
SELECT TOP 1 WITH TIES
    Date,
    City,
    Hour,
    Temperature,
    UpdateTimestamp
FROM WeatherForecast
ORDER BY ROW_NUMBER() OVER (
    PARTITION BY Date, City, Hour 
    ORDER BY UpdateTimestamp DESC
);

This relies on the same covering index as the ROW_NUMBER() method for optimal speed.

If your system is read-dominated (you query the latest data far more often than you write new forecasts), skip the view entirely and maintain a dedicated snapshot table that only stores the latest entry per Date/City/Hour.

Example Trigger to Keep Snapshot Updated #

Use a trigger to automatically sync the snapshot table whenever new data is inserted or updated:

-- First create the snapshot table
CREATE TABLE LatestWeatherForecast (
    Date DATE,
    City VARCHAR(100),
    Hour INT,
    Temperature DECIMAL(5,2),
    UpdateTimestamp DATETIME2,
    PRIMARY KEY (Date, City, Hour)
);

-- Trigger to update snapshot on insert/update
CREATE TRIGGER trg_WeatherForecast_UpdateLatest
ON WeatherForecast
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Remove old entries for the same Date/City/Hour
    DELETE FROM LatestWeatherForecast
    WHERE EXISTS (
        SELECT 1 FROM inserted i 
        WHERE i.Date = LatestWeatherForecast.Date 
          AND i.City = LatestWeatherForecast.City 
          AND i.Hour = LatestWeatherForecast.Hour
    );

    -- Insert the latest records
    INSERT INTO LatestWeatherForecast (Date, City, Hour, Temperature, UpdateTimestamp)
    SELECT Date, City, Hour, Temperature, UpdateTimestamp
    FROM inserted;
END;

Querying this snapshot table will be instant, as it only contains one row per group—no sorting or filtering needed at query time.

Your initial inner join with max(UpdateTimestamp) requires two full table scans:

1. First, to calculate the maximum timestamp for each Date/City/Hour group
2. Second, to join back to the original table to fetch the corresponding temperature

As your table grows, these scans become exponentially slower. The window function or snapshot methods eliminate this redundant work, especially when paired with proper indexing.

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