共用方式為

開始使用時態表

適用於：Fabric 中的 Azure SQL 資料庫 Azure SQL 受控執行個體SQL 資料庫

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

時間情境

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

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

範例數據表 WebSiteUserinfo 的數據表架構圖表。

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

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

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

Important

一律使用最新版的 SQL Server Management Studio。

建立新的資料表

  • 使用 [SSMS 物件總管] 中的 [ 新增 System-Versioned 數據表 ] 功能表項，以時態表範本開啟查詢編輯器，然後使用 [指定範本參數的值 ] [Ctrl+Shift+M] 填入範本：

    [新增系統版本數據表] 選項的 SSMS 螢幕快照。

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

    SSMS [新增項目] 對話框的螢幕快照，已選取 [時態資料表] System-Versioned。

  • 您也可以直接指定 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 型樹狀結構索引。 此組態最適合使用時態表的大部分案例，特別是用於資料稽核。

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

Note

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

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

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

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

SQL Server Management Studio 的螢幕快照，其中顯示物件總管和歷程記錄數據表。

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

讓我們探討一個替代情境，即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;

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

時態表架構的圖表。

步驟 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 StDevVisitedPages
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;

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

根據時態表歷史數據的頁面訪問圖表。

演進數據表架構

一般而言，在進行應用程式開發時，您必須變更時態表結構。 為此，只要執行一般 ALTER TABLE 語句和 Azure SQL Database 或 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 來變更時態表結構 。

控制歷程記錄數據的保留

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

Remarks

在這兩個 Azure SQL 資料庫 設定為鏡像至 Fabric 和 Fabric SQL 資料庫時，您可以建立時態表，但個別的歷程記錄數據表不會鏡像到 Fabric OneLake。 如需在時態表上設定 SYSTEM_VERSIONING 旗標的特定行為，請參閱 建立系統版本設定時態表。

相關內容

  • Last updated on