查詢系統建立版本時態表中的資料

  • 發行項

適用於： SQL Server 2016 (13.x) 及更新版本 Azure SQL DatabaseAzure SQL 受控執行個體

當您想要取得時態表中資料的最新 (目前) 狀態，您能夠以您查詢非時態表的相同方式查詢。 如果 PERIOD 資料行未隱藏，其值會出現在 SELECT * 查詢中。 如果您將 PERIOD 資料行指定為 HIDDEN，其值不會出現在 SELECT * 查詢中。 當 PERIOD 資料行隱藏時，您必須參考 SELECT 子句中的 PERIOD 資料行，以傳回這些資料行的值。

若要執行任何以時間為基礎之類型的分析，請搭配四個特定時態次子句使用新的 FOR SYSTEM_TIME 子句來查詢在目前和歷程記錄資料表之間的資料。 如需這些子句的詳細資訊，請參閱時態表和 FROM (Transact-SQL)

  • AS OF <date_time>
  • FROM <start_date_time> TO <end_date_time>
  • BETWEEN <start_date_time> AND <end_date_time>
  • CONTAINED IN (<start_date_time>, <end_date_time>)
  • ALL

FOR SYSTEM_TIME在查詢中，可以針對每個資料表個別指定 。 它可以在通用資料表運算式、資料表值函式和預存程序內使用。 搭配時態表使用資料表別名時，時態表名稱和別名之間必須包含 FOR SYSTEM_TIME 子句 (請參閱使用 AS OF 次子句查詢特定時間中的第二個範例)。

使用 AS OF 次子句查詢特定的時間

當您需要重新建構資料於過去特定時間的狀態，請使用 AS OF 次子句。 您可以使用 PERIOD 資料行定義中指定之 datetime2 類型的有效位數，重新建構資料。

AS OF 次子句可以搭配常數常值或變數使用，以便您以動態方式指定時間的情況。 提供的值會解譯為 UTC 時間。

第一個範例傳回 dbo.Department 資料表 AS OF 過去特定時間的狀態。

/*State of entire table AS OF specific date in the past*/
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF '2021-09-01 T10:00:00.7230011';

第二個範例針對資料列子集比較兩個時間點之間的值。

DECLARE @ADayAgo DATETIME2;
SET @ADayAgo = DATEADD(day, -1, sysutcdatetime());

/*Comparison between two points in time for subset of rows*/
SELECT D_1_Ago.[DeptID],
    D.[DeptID],
    D_1_Ago.[DeptName],
    D.[DeptName],
    D_1_Ago.[ValidFrom],
    D.[ValidFrom],
    D_1_Ago.[ValidTo],
    D.[ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF @ADayAgo AS D_1_Ago
INNER JOIN [Department] AS D
    ON D_1_Ago.[DeptID] = [D].[DeptID]
        AND D_1_Ago.[DeptID] BETWEEN 1 AND 5;

在時態查詢中，將檢視與 AS OF 次子句搭配使用

在需要複雜時間點分析時，使用檢視很有用。 常見範例是產生一份今天的商務報表，包含上個月的值。

通常，客戶會有一個許多資料表牽涉到外部索引鍵關聯性的正規化資料庫模型。 該正規化模型的資料在過去某個時間點看起來的樣子很有挑戰性，因為所有的資料表都會以自己的步調獨立變更。

在此情況下，最好的選擇是建立檢視，並套用 AS OF 次子句到整個檢視。 使用這種方法可讓您將資料存取層的模型從時間點分析中分離，同時 SQL Server 會以透明的方式將 AS OF 子句套用至所有加入檢視定義的時態表。 此外，您可以在相同的檢視中將時態表與非時態表結合，而 AS OF 只會套用至時態表。 如果檢視未參考至少一個時態表，則套用時態性查詢子句至檢視會失敗，並產生錯誤。

下列範例程式碼會建立加入三個時態表的檢視：Department、CompanyLocation 和 LocationDepartments：

CREATE VIEW [dbo].[vw_GetOrgChart]
AS
SELECT [CompanyLocation].LocID,
    [CompanyLocation].LocName,
    [CompanyLocation].City,
    [Department].DeptID,
    [Department].DeptName
FROM [dbo].[CompanyLocation]
LEFT JOIN [dbo].[LocationDepartments]
    ON [CompanyLocation].LocID = LocationDepartments.LocID
LEFT JOIN [dbo].[Department]
    ON LocationDepartments.DeptID = [Department].DeptID;
GO

您現在可以使用 AS OF 次子句和 datetime2 常值來查詢檢視：

/* Querying view AS OF */
SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF'2021-09-01 T10:00:00.7230011';

或者，您可以將 AS OF 次子句與當地時區和 AT TIME ZONE 搭配使用來查詢檢視：

/* Querying view AS OF with local time*/
DECLARE @LocalTime DATETIMEOFFSET = '2021-09-01 10:00:00.7230011 -07:00';

SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF @LocalTime AT TIME ZONE 'UTC';

查詢一段時間的特定資料列變更

當您需要針對目前資料表 (又稱為資料稽核) 中的特定資料列取得所有歷程變更時，時態性次子句 FROM ... TO、BETWEEN ... AND 和 CONTAINED IN 很有用。

前兩個次子句會傳回與指定期間重疊的資料列版本 (也就是指定週期開始之前和指定週期結束之後)，而 CONTAINED IN 只傳回存在於指定期間內的那些資料列版本。

如果您只搜尋非目前資料列版本，您應該直接查詢記錄資料表，以獲得最佳查詢效能。 當您需要查詢目前和和歷程記錄資料，沒有任何限制時，請使用 ALL。

/* Query using BETWEEN...AND sub-clause*/
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo],
    IIF(YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department]
FOR SYSTEM_TIME BETWEEN '2021-01-01' AND '2021-12-31'
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using CONTAINED IN sub-clause */
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME CONTAINED IN ('2021-04-01', '2021-09-25')
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using ALL sub-clause */
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo],
    IIF(YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department]
FOR SYSTEM_TIME ALL
ORDER BY [DeptID],
    [ValidFrom] DESC;

後續步驟