sql-server中使用索引 View 获取最新记录

我正在开发一个监控项目，每次收集数据时，我都会为该集合生成一个新 ID。

我希望在索引 View 中拥有最新的代码。

SELECT THE_ID = MAX(THE_ID)  FROM [dbo].[monit_server_space_by_drive] 

类似这样的:

CREATE VIEW V_LAST_ID  
WITH SCHEMABINDING 
AS 
SELECT THE_ID = MAX(THE_ID)  FROM [dbo].[monit_server_space_by_drive] 
 
CREATE UNIQUE CLUSTERED INDEX UC_IDX_V_LAST_ID  ON V_LAST_ID (THE_ID) 

这可能吗，或者有其他选择吗？

这就是我打算使用它的方式:

SELECT  
   M.[THE_ID] 
  ,M.[serverid] 
  ,s.SQLServerName 
  ,s.[Environment] 
  ,M.[DRIVE] 
  ,M.[Volume_Size_GB] 
  ,[VolumeUsed_GB] = CAST (M.[VolumeUsed_MB] / 1024.00 AS NUMERIC (18,2)) 
  ,M.[VolumeSpaceAvailable_GB] 
 , DrivePercentUsed = 100 - M.[VolumePercentAvailable] 
  ,M.[VolumePercentAvailable] 
 
  FROM [dbo].[monit_server_space_by_drive] M 
  INNER JOIN DBO.V_LAST_ID  V  
      ON M.THE_ID = V.THE_ID 
  INNER JOIN [dbo].[tblServers] S 
      ON M.serverid = s.ServerID 
  order by M.[ClusterName] 

表[dbo].[monit_server_space_by_drive]的主键由三个字段组成:

USE [Monitoring] 
GO 
 
ALTER TABLE [dbo].[monit_server_space_by_drive] ADD  CONSTRAINT [PK_monit_server_space_by_drive] PRIMARY KEY CLUSTERED  
(   [THE_ID] ASC, 
[serverid] ASC, 
[volumeID] ASC 
)ON [FGMONITORING] 

表定义:

CREATE TABLE [dbo].[monit_server_space_by_drive]( 
[THE_ID] [int] NOT NULL, 
[serverid] [int] NOT NULL, 
[ClusterName] [nvarchar](260) NOT NULL, 
[nodeid] [int] NOT NULL, 
[DRIVE] [nchar](1) NULL, 
[volumeID] [int] NOT NULL, 
[LastSync] [datetime] NULL, 
[Volume_Size_MB] [numeric](18, 2) NULL, 
[Volume_Size_GB] [numeric](18, 2) NULL, 
[VolumeSpaceAvailable_MB] [numeric](18, 2) NULL, 
[VolumeSpaceAvailable_GB] [numeric](18, 2) NULL, 
[VolumePercentAvailable] [numeric](5, 2) NULL, 
[dt] [datetime] NULL, 
[_year] [smallint] NULL, 
[_month] [tinyint] NULL, 
[_day] [tinyint] NULL, 
[_week] [tinyint] NULL CONSTRAINT [DF_monit_server_space_by_drive__week]  DEFAULT ((0)), 
[_hour] [tinyint] NULL, 
[_min] [tinyint] NULL, 
[_weekday] [tinyint] NULL, 
[VolumeUsed_MB]  AS ([volume_size_MB]-[volumeSpaceAvailable_MB]) PERSISTED, 
 CONSTRAINT [PK_monit_server_space_by_drive] PRIMARY KEY CLUSTERED  
 ( 
[THE_ID] ASC, 
[serverid] ASC, 
[volumeID] ASC 
) ON [FGMONITORING_INDEX] 
) ON [FGMONITORING] 

下面是该表所保存数据的示例。每次我收集连接数据时，THE_ID 就会加一。在同一个 THE_ID 中，我拥有环境中的所有服务器。

请您参考如下方法:

如果这可行，那么每次插入新记录时， View 的索引都必须更新。

为什么不更直接地使用触发器将最后一个 ID 更新到一个小表中呢？

CREATE TABLE DBO.V_LAST_ID( [THE_ID] [int] NOT NULL ); 
 
INSERT DBO.V_LAST_ID (THE_ID) VALUES (0); --Starting row 
 
CREATE TRIGGER Dbo.KeepV_LAST_IDNumber ON [dbo].[monit_server_space_by_drive] 
AFTER INSERT AS 
BEGIN 
   DECLARE @MaxInserted int; 
 
   SELECT @MaxInserted = MAX(THE_ID) FROM inserted; 
 
   UPDATE DBO.V_LAST_ID 
   SET THE_ID = @MaxInserted 
   WHERE THE_ID < @MaxInserted; 
END 

然后您可以根据需要使用此表而不是 View 。