開始使用 Azure SQL 資料庫與 Azure SQL 受控執行個體的時態表

發行項
03/13/2024

暫存資料表是 Azure SQL 資料庫與 Azure SQL 受控執行個體的一項可程式性功能，可讓您追蹤和分析資料變更的完整歷程記錄，而不需要撰寫自訂程式碼。時態表會保持資料與時間內容之間的密切關係，因此只有在特定期間內，才會將預存的事實解譯為有效。時態表的這個屬性允許進行以有效時間為基礎的分析，並可從資料演進中取得獨到見解。

時態案例

本文說明在應用案例使用時態表的步驟。假設您想要追蹤從頭開始開發的新網站或想要透過使用者活動分析進行擴充的現有網站上的使用者活動。在此簡化的範例，我們假設在一段時間內瀏覽的網頁數量是必須在 Azure SQL 資料庫或 Azure SQL 受控執行個體裝載的網站資料庫中擷取及監視的指標。使用者活動的歷程分析目標是取得重新設計網站的資訊並為訪客提供更好的體驗。

此案例的資料庫模型簡單 - 使用者活動指標以單一整數欄位 PageVisited 表示，並擷取使用者設定檔的基本資訊。此外，針對以時間為基礎的分析，您會保留每個使用者的一系列資料列，其中每個資料列都代表特定使用者在特定時段內瀏覽的頁面數。

結構描述

幸運的是，您不需要投入任何心力來維護此活動資訊。使用時態表時，此程序是自動化 - 為您在網站設計期間提供充分的彈性，並有更多時間專注於資料分析本身。您唯一要做的就是確保 WebSiteInfo 資料表已設定為時態系統版本設定。在此案例中使用時態表的確切步驟如下所述。

步驟 1：將資料表設定為時態性

根據您是否要開始新的開發或升級現有的應用程式，您將透過新增時態屬性來建立時態表或修改現有時態表。一般而言，您的案例可以是這兩個選項的組合。使用 SQL Server Management Studio (SSMS)、SQL Server Data Tools (SSDT)、Azure Data Studio 或任何其他 Transact-SQL 開發工具來執行這些動作。

重要

建議一律使用最新版本的 Management Studio 以便與 Azure SQL 資料庫及 Azure SQL 受控執行個體更新保持同步。更新 SQL Server Management Studio。

建立新的資料表

使用 SSMS 物件總管中的操作功能表項目「新系統版本設定資料表 (New System-Versioned Table)」開啟包含時態表範本指令碼的查詢編輯器，然後使用「指定範本參數的值」 (Ctrl+Shift+M) 來填入範本：

SSMSNewTable

在 SSDT，將新項目新增至資料庫專案時，選擇「時態表 (系統版本設定)」範本。這會開啟資料表設計工具，並讓您輕鬆指定表格版面配置：

SSDTNewTable

您也可以直接指定 Transact-SQL 陳述式來建立時態表，如下列範例所示。每個時態表的必要元素都是 PERIOD 定義及 SYSTEM_VERSIONING 子句，以及另一個將儲存歷程記錄資料列版本的使用者資料表參考：

CREATE TABLE WebsiteUserInfo
(  
    [UserID] int NOT NULL PRIMARY KEY CLUSTERED
  , [UserName] nvarchar(100) NOT NULL
  , [PagesVisited] int NOT NULL
  , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )  
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WebsiteUserInfoHistory));

當您建立系統版本設定的時態表時，會自動建立具有預設組態的隨附歷程記錄資料表。預設歷程記錄資料表包含期間資料行 (結束、開始) 已啟用頁面壓縮的叢集 B 型樹狀結構索引。此組態最適合使用時態表的大部分案例，特別是用於資料稽核。

在此特定案例中，我們的目標是在較長的資料歷程記錄和具有較大的資料集上執行以時間為基礎的趨勢分析，因此歷程記錄資料表的記憶體選擇是叢集資料行存放區索引。叢集資料行存放區為分析查詢提供好的壓縮與效能。時態表可讓您彈性獨立設定目前和時態表上的設定索引。

注意

資料行存放區索引適用於業務關鍵層、一般用途層、進階層、標準層、S3 及更高層。

下列指令碼顯示記錄資料表上的預設索引如何變更為叢集資料行存放區：

CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
ON dbo.WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

時態表在物件總管以特定圖示表示，以便於識別，而其歷程記錄資料表會顯示為子節點。

AlterTable

將現有的資料表變更為時態表

讓我們討論 WebsiteUserInfo 資料表已經存在但並非設計來保留變更歷程記錄的替代案例。在此情況下，您可以簡單地將現有資料表擴充為時態表，如下列範例所示：

ALTER TABLE WebsiteUserInfo
ADD
    ValidFrom datetime2 (0) GENERATED ALWAYS AS ROW START HIDDEN  
        constraint DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME())
    , ValidTo datetime2 (0)  GENERATED ALWAYS AS ROW END HIDDEN
        constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE WebsiteUserInfo  
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WebsiteUserInfoHistory));
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
ON dbo.WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

步驟 2：定期執行工作負載

時態表的主要優點是，您不需要以任何方式變更或調整您的網站，即可執行變更追蹤。建立之後，時態表會在每次對資料執行修改時，透明地保存先前的資料列版本。

為了利用此特定案例的自動變更追蹤，讓我們在每次使用者結束其網站上的工作階段時更新資料行 PagesVisited：

UPDATE WebsiteUserInfo  SET [PagesVisited] = 5
WHERE [UserID] = 1;

請務必注意，更新查詢不需要知道實際作業發生的確切時間，也不需要保留歷程記錄資料以供未來分析。這兩方面均由 Azure SQL 資料庫和 Azure SQL 受控執行個體自動處理。下圖說明如何在每次更新時產生歷程記錄資料。

TemporalArchitecture

步驟 3：執行歷程記錄資料分析

現在，啟用時態系統版本控制時，您只需進行一次查詢即可進行歷程記錄資料分析。在本文中，我們將提供一些解決常見分析案例的範例 - 若要瞭解所有詳細資料，請探索 FOR SYSTEM_TIME 子句引入的各種選項。

若要查看截至一小時前瀏覽網頁數量排序的前 10 位使用者，請執行此查詢：

DECLARE @hourAgo datetime2 = DATEADD(HOUR, -1, SYSUTCDATETIME());
SELECT TOP 10 * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME AS OF @hourAgo
ORDER BY PagesVisited DESC

您可以輕鬆修改此查詢來分析一天前、一個月前或您希望的過去任何時間點的網站瀏覽情況。

若要對前一天進行基本統計分析，請使用以下範例：

DECLARE @twoDaysAgo datetime2 = DATEADD(DAY, -2, SYSUTCDATETIME());
DECLARE @aDayAgo datetime2 = DATEADD(DAY, -1, SYSUTCDATETIME());

SELECT UserID, SUM (PagesVisited) as TotalVisitedPages, AVG (PagesVisited) as AverageVisitedPages,
MAX (PagesVisited) AS MaxVisitedPages, MIN (PagesVisited) AS MinVisitedPages,
STDEV (PagesVisited) as StDevViistedPages
FROM dbo.WebsiteUserInfo
FOR SYSTEM_TIME BETWEEN @twoDaysAgo AND @aDayAgo
GROUP BY UserId

若要搜尋特定使用者在一段時間內的活動，請使用 CONTAINED IN 子句：

DECLARE @hourAgo datetime2 = DATEADD(HOUR, -1, SYSUTCDATETIME());
DECLARE @twoHoursAgo datetime2 = DATEADD(HOUR, -2, SYSUTCDATETIME());
SELECT * FROM dbo.WebsiteUserInfo
FOR SYSTEM_TIME CONTAINED IN (@twoHoursAgo, @hourAgo)
WHERE [UserID] = 1;

圖形視覺化對於時間查詢特別方便，因為您可以非常輕鬆地以直覺的方式顯示趨勢及使用模式：

TemporalGraph

不斷演進的資料表結構

一般而言，在進行應用程式開發時，您必須變更時態表結構。為此，只需執行一般 ALTER TABLE 陳述式，Azure SQL 資料庫或 Azure SQL 受控執行個體就會適當地將變更傳播至歷程記錄資料表。下列指令碼示範如何新增其他屬性以進行追蹤：

/*Add new column for tracking source IP address*/
ALTER TABLE dbo.WebsiteUserInfo
ADD  [IPAddress] varchar(128) NOT NULL CONSTRAINT DF_Address DEFAULT 'N/A';

同樣，您可以在工作負載作用中時變更資料行定義：

/*Increase the length of name column*/
ALTER TABLE dbo.WebsiteUserInfo
    ALTER COLUMN  UserName nvarchar(256) NOT NULL;

最後，您可以移除不再需要的資料行。

/*Drop unnecessary column */
ALTER TABLE dbo.WebsiteUserInfo
    DROP COLUMN TemporaryColumn;

或者，當您連線到資料庫 (線上模式) 或作為資料庫專案的一部分時 (離線模式)，請使用最新的 SSDT 來變更時態表結構。

控制歷程記錄資料的保留

使用系統設定版本的時態表時，歷程記錄資料表增加資料庫大小的程度可能會比一般資料表大。對於不斷成長的大型歷程記錄資料表來說，單是儲存成本和加諸於時態查詢之上的效能負擔就可能會造成問題。因此，開發資料保留原則來管理歷程記錄資料表中的資料是規劃及管理每個時態表生命週期的重要環節。使用 Azure SQL 資料庫及 Azure SQL 受控執行個體，可以使用下列方法來管理時態表中的歷程記錄資料：

下一步

如需時態表的詳細資訊，請參閱時態表。

共用方式為