SQL Server 2000添加存储MAX值的计算列解决查询慢问题咨询

可行实现方案 #

方案1：新增非聚集索引优化原生MAX查询（成本最低，无需改动表结构逻辑） #

你当前查询慢的核心原因是dateandtime字段没有索引，SQL Server每次执行MAX都要扫全表。SQL Server 2000支持非聚集索引，直接给对应表的dateandtime字段建非聚集索引即可：

CREATE NONCLUSTERED INDEX IX_Mytable_dateandtime ON Mytable(dateandtime DESC)

建完后再执行SELECT MAX(dateandtime) FROM Mytable会直接走索引取顶部值，耗时从全表扫的秒/分钟级降到毫秒级，完全满足需求，不需要额外加冗余列。 #

方案2：触发器维护冗余最大值列（适配对实时性要求高的场景） #

如果确实不想依赖索引，或者有其他业务逻辑需要把最大值存在表内，可以用普通列+INSERT/UPDATE/DELETE触发器替代计算列，因为计算列本身不支持聚合类表达式：

1. 先新增普通字段存储最大值：

ALTER TABLE Mytable ADD max_dateandtime DATETIME NULL

2. 给表绑定3个触发器，每次表内数据有变动时，自动更新所有行的max_dateandtime为最新的最大值：

-- INSERT触发器
CREATE TRIGGER trg_Mytable_UpdateMax ON Mytable AFTER INSERT
AS
BEGIN
    DECLARE @max_dt DATETIME
    SELECT @max_dt = MAX(dateandtime) FROM Mytable
    UPDATE Mytable SET max_dateandtime = @max_dt
END
GO
-- UPDATE触发器
CREATE TRIGGER trg_Mytable_UpdateMax2 ON Mytable AFTER UPDATE
AS
BEGIN
    IF UPDATE(dateandtime)
    BEGIN
        DECLARE @max_dt DATETIME
        SELECT @max_dt = MAX(dateandtime) FROM Mytable
        UPDATE Mytable SET max_dateandtime = @max_dt
    END
END
GO
-- DELETE触发器
CREATE TRIGGER trg_Mytable_UpdateMax3 ON Mytable AFTER DELETE
AS
BEGIN
    DECLARE @max_dt DATETIME
    SELECT @max_dt = MAX(dateandtime) FROM Mytable
    UPDATE Mytable SET max_dateandtime = @max_dt
END
GO

3. 首次执行一次全量更新给字段赋值：

UPDATE Mytable SET max_dateandtime = (SELECT MAX(dateandtime) FROM Mytable)

后续你直接执行SELECT TOP 1 max_dateandtime FROM Mytable就能拿到最新值，不需要全表扫描。

注意：如果表数据量非常大、写入频率极高，触发器会带来一定的写入性能开销，适合读远多于写的场景。

方案3：定时作业维护全局最大值汇总表（适配对实时性要求不高的场景） #

如果你需要同时查询多个表的最大值，可以单独建一张汇总表，用SQL Server 2000的代理作业定时更新：

1. 新建汇总表：

CREATE TABLE TableMaxDate (
    table_name SYSNAME PRIMARY KEY,
    max_dateandtime DATETIME NOT NULL
)

2. 写一个更新存储过程：

CREATE PROC sp_UpdateAllTableMaxDate
AS
BEGIN
    -- 逐个表更新最大值，示例如下，新增其他表就加对应INSERT/UPDATE语句
    DECLARE @max_dt DATETIME
    SELECT @max_dt = MAX(dateandtime) FROM Mytable
    IF EXISTS(SELECT 1 FROM TableMaxDate WHERE table_name = 'Mytable')
        UPDATE TableMaxDate SET max_dateandtime = @max_dt WHERE table_name = 'Mytable'
    ELSE
        INSERT INTO TableMaxDate(table_name, max_dateandtime) VALUES('Mytable', @max_dt)
    -- 其他表同理添加
END
GO

3. 在SQL Server代理中新建定时作业，按照你能接受的延迟频率（比如每15分钟、每小时）执行一次上述存储过程。
   后续要查所有表的最大值直接查询TableMaxDate表即可，单表查询速度极快。

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