Erste Schritte mit temporalen Tabellen in der Azure SQL-DatenbankGetting Started with Temporal Tables in Azure SQL Database

Temporale Tabellen sind eine neue Programmierfunktion der Azure SQL-Datenbank, mit der Sie den vollständigen Verlauf von Änderungen in Ihren Daten ohne benutzerdefinierte Codierung nachverfolgen und analysieren können.Temporal Tables are a new programmability feature of Azure SQL Database that allows you to track and analyze the full history of changes in your data, without the need for custom coding. Temporale Tabellen enthalten Daten, die eng mit dem zeitlichen Kontext verbunden sind, sodass gespeicherte Fakten nur im angegebenen Zeitraum als gültig interpretiert werden können.Temporal Tables keep data closely related to time context so that stored facts can be interpreted as valid only within the specific period. Diese Eigenschaft von temporalen Tabellen ermöglicht eine effiziente zeitbasierte Analyse und Einblicke in die Datenentwicklung.This property of Temporal Tables allows for efficient time-based analysis and getting insights from data evolution.

Temporales SzenarioTemporal Scenario

Dieser Artikel beschreibt die Schritte zur Nutzung temporaler Tabellen in einem Anwendungsszenario.This article illustrates the steps to utilize Temporal Tables in an application scenario. Stellen Sie sich vor, Sie möchten die Benutzeraktivität auf einer neuen Website nachverfolgen, die von Grund auf neu entwickelt wird, oder auf einer vorhandenen Website, die Sie mit der Benutzeraktivitätsanalyse erweitern möchten.Suppose that you want to track user activity on a new website that is being developed from scratch or on an existing website that you want to extend with user activity analytics. In diesem vereinfachten Beispiel setzen wir voraus, dass die Anzahl der besuchten Webseiten während einer Zeitspanne ein Indikator ist, der in der Websitedatenbank aufgezeichnet und überwacht werden muss, die in der Azure SQL-Datenbank gehostet wird.In this simplified example, we assume that the number of visited web pages during a period of time is an indicator that needs to be captured and monitored in the website database that is hosted on Azure SQL Database. Die Verlaufsanalyse der Benutzeraktivität soll Anregungen zum Neuentwurf der Website und bessere Erkenntnisse über die Besucher liefern.The goal of the historical analysis of user activity is to get inputs to redesign website and provide better experience for the visitors.

Das Datenbankmodell für dieses Szenario ist sehr einfach – die Metrik der Benutzeraktivität wird nur mit dem Ganzzahlfeld PageVisited dargestellt und zusammen mit grundlegenden Informationen im Benutzerprofil erfasst.The database model for this scenario is very simple - user activity metric is represented with a single integer field, PageVisited, and is captured along with basic information on the user profile. Außerdem verwalten Sie für die zeitbasierte Analyse eine Reihe von Zeilen für jeden Benutzer, wobei jede Zeile die Anzahl von Seiten darstellt, die ein bestimmter Benutzer innerhalb einer bestimmten Zeitspanne besucht.Additionally, for time-based analysis, you would keep a series of rows for each user, where every row represents the number of pages a particular user visited within a specific period of time.

Schema

Glücklicherweise erfordert die Verwaltung dieser Aktivitätsinformationen von Ihnen keinerlei Aufwand zur Bearbeitung Ihrer App.Fortunately, you do not need to put any effort in your app to maintain this activity information. Mit temporalen Tabellen ist dieser Prozess automatisiert – Sie genießen volle Flexibilität bei der Gestaltung der Website und haben mehr Zeit, um sich auf die eigentliche Datenanalyse zu konzentrieren.With Temporal Tables, this process is automated - giving you full flexibility during website design and more time to focus on the data analysis itself. Sie müssen einzig und allein sicherstellen, dass die Tabelle WebSiteInfo als temporal mit Systemversionsverwaltungkonfiguriert ist.The only thing you have to do is to ensure that WebSiteInfo table is configured as temporal system-versioned. Die genauen Schritte zum Verwenden temporaler Tabellen in diesem Szenario werden unten beschrieben.The exact steps to utilize Temporal Tables in this scenario are described below.

Schritt 1: Konfigurieren von Tabellen als temporalStep 1: Configure tables as temporal

Je nachdem, ob Sie eine neue Entwicklung beginnen oder eine vorhandene Anwendung aktualisieren, werden Sie entweder temporale Tabellen erstellen oder vorhandene ändern, indem Sie temporale Attribute hinzufügen.Depending on whether you are starting new development or upgrading existing application, you will either create temporal tables or modify existing ones by adding temporal attributes. Im Allgemeinen kann Ihr Szenario eine Kombination dieser beiden Optionen sein.In general case, your scenario can be a mix of these two options. Führen Sie diese Aktion mit SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT) oder einem anderen Tool zur Transact-SQL-Entwicklung aus.Perform these action using SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT) or any other Transact-SQL development tool.

Wichtig

Es wird empfohlen, immer die neueste Version von Management Studio zu verwenden, damit Sie mit Updates von Microsoft Azure und SQL-Datenbank synchron sind.It is recommended that you always use the latest version of Management Studio to remain synchronized with updates to Microsoft Azure and SQL Database. Aktualisieren Sie SQL Server Management Studio.Update SQL Server Management Studio.

Erstellen einer neuen TabelleCreate new table

Verwenden Sie das Kontextmenüelement „Neue Tabelle mit Systemversionsverwaltung“ im SSMS-Objekt-Explorer, um den Abfrage-Editor mit einem Vorlagenskript für eine temporale Tabelle zu öffnen, und füllen Sie die Vorlage dann mit „Werte für Vorlagenparameter angeben“ (STRG+UMSCHALT+M):Use context menu item “New System-Versioned Table” in SSMS Object Explorer to open the query editor with a temporal table template script and then use “Specify Values for Template Parameters” (Ctrl+Shift+M) to populate the template:

SSMSNewTable

Wählen Sie in SSDT beim Hinzufügen von neuen Elementen zum Datenbankprojekt die Vorlage „Temporale Tabelle (mit Systemversionsverwaltung)“ aus.In SSDT, choose “Temporal Table (System-Versioned)” template when adding new items to the database project. Damit öffnen Sie den Tabellen-Designer, sodass Sie mühelos das Tabellenlayout festlegen können:That will open table designer and enable you to easily specify the table layout:

SSDTNewTable

Sie können eine temporale Tabelle auch durch Angeben von Transact-SQL-Anweisungen direkt erstellen, wie im folgenden Beispiel gezeigt.You can also create temporal table by specifying the Transact-SQL statements directly, as shown in the example below. Beachten Sie, dass die obligatorischen Elemente aller temporalen Tabellen die Definition von PERIOD und die SYSTEM_VERSIONING-Klausel mit einem Verweis auf eine andere Benutzertabelle sind, die Verlaufszeilenversionen speichert:Note that the mandatory elements of every temporal table are the PERIOD definition and the SYSTEM_VERSIONING clause with a reference to another user table that will store historical row versions:

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));

Wenn Sie eine temporale Tabelle mit Systemversionsverwaltung erstellen, wird die zugehörige Verlaufstabelle mit der Standardkonfiguration automatisch erstellt.When you create system-versioned temporal table, the accompanying history table with the default configuration is automatically created. Die Standardverlaufstabelle enthält einen gruppierten B-Strukturindex für die Periodenspalten (Anfang, Ende) mit aktivierter Seitenkomprimierung.The default history table contains a clustered B-tree index on the period columns (end, start) with page compression enabled. Diese Konfiguration ist optimal für die Mehrzahl der Szenarien, in denen temporale Tabellen insbesondere für die Datenüberwachungverwendet werden.This configuration is optimal for the majority of scenarios in which temporal tables are used, especially for data auditing.

In diesem Fall soll über einen längeren Datenverlauf hinweg und mit größeren Datasets eine zeitbasierte Trendanalyse ausgeführt werden, darum wird als Speicher für die Verlaufstabelle ein gruppierter Columnstore-Index gewählt.In this particular case, we aim to perform time-based trend analysis over a longer data history and with bigger data sets, so the storage choice for the history table is a clustered columnstore index. Ein gruppierter Columnstore bietet sehr gute Komprimierung und Leistung für analytische Abfragen.A clustered columnstore provides very good compression and performance for analytical queries. Temporale Tabellen geben Ihnen die Flexibilität zum vollständig unabhängigen Konfigurieren von Indizes für aktuelle und temporalen Tabellen.Temporal Tables give you the flexibility to configure indexes on the current and temporal tables completely independently.

Hinweis

Columnstore-Indizes sind in der Premium-Ebene und in der Standardebene, in S3 und höher verfügbar.Columnstore indexes are available in the Premium tier and in the Standard tier, S3 and above.

Das folgende Skript zeigt, wie der Standardindex für die Verlaufstabelle in den gruppierten Columnstore geändert werden kann:The following script shows how default index on history table can be changed to the clustered columnstore:

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

Temporale Tabellen werden im Objekt-Explorer zur leichteren Erkennung mit einem bestimmten Symbol dargestellt, während die zugehörige Verlaufstabelle als untergeordneter Knoten angezeigt wird.Temporal Tables are represented in the Object Explorer with the specific icon for easier identification, while its history table is displayed as a child node.

AlterTable

Ändern einer vorhandenen Tabelle in temporalAlter existing table to temporal

Wir behandeln nun das alternative Szenario, in dem die WebsiteUserInfo-Tabelle bereits vorhanden ist, jedoch nicht zum Speichern eines Änderungsverlaufs konzipiert wurde.Let’s cover the alternative scenario in which the WebsiteUserInfo table already exists, but was not designed to keep a history of changes. In diesem Fall können Sie die vorhandene Tabelle, wie im folgenden Beispiel gezeigt, einfach zu einer temporalen erweitern:In this case, you can simply extend the existing table to become temporal, as shown in the following example:

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); 

Schritt 2: Reguläres Ausführen Ihrer WorkloadStep 2: Run your workload regularly

Der Hauptvorteil von temporalen Tabellen ist, dass Sie Ihre Website zum Nachverfolgen von Änderungen in keiner Weise ändern oder anpassen müssen.The main advantage of Temporal Tables is that you do not need to change or adjust your website in any way to perform change tracking. Nach der Erstellung behalten temporale Tabellen jedes Mal transparent vorherige Zeilenversionen bei, wenn Sie Änderungen an Ihren Daten ausführen.Once created, Temporal Tables transparently persist previous row versions every time you perform modifications on your data.

Um die automatische Änderungsnachverfolgung für dieses spezielle Szenario nutzen zu können, richten wir es ein, dass die Spalte PagesVisited jedes Mal aktualisiert wird, wenn ein Benutzer seine Sitzung auf der Website beendet:In order to leverage automatic change tracking for this particular scenario, let’s just update column PagesVisited every time a user ends their session on the website:

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

Es ist wichtig, zu beachten, dass die Aktualisierungsabfrage nicht die genaue Zeit kennen muss, zu der der eigentliche Vorgang aufgetreten ist, noch wie die Verlaufsdaten zur späteren Analyse beibehalten werden.It is important to notice that the update query doesn’t need to know the exact time when the actual operation occurred nor how historical data will be preserved for future analysis. Beide Aspekte werden automatisch von der Azure SQL-Datenbank behandelt.Both aspects are automatically handled by the Azure SQL Database. Das folgende Diagramm veranschaulicht, wie Verlaufsdaten bei jedem Update generiert werden.The following diagram illustrates how history data is being generated on every update.

TemporalArchitecture

Schritt 3: Ausführen der VerlaufsdatenanalyseStep 3: Perform historical data analysis

Da nun die temporale Systemversionsverwaltung aktiviert ist, ist die Analyse der Verlaufsdaten nur eine Abfrage von Ihnen entfernt.Now when temporal system-versioning is enabled, historical data analysis is just one query away from you. In diesem Artikel zeigen wir einige Beispiele, die allgemeine Analyseszenarien behandeln. Um alle Details zu erfahren, können Sie sich mit den verschiedenen Optionen vertraut machen, die mit der FOR SYSTEM_TIME-Klausel eingeführt werden.In this article, we will provide a few examples that address common analysis scenarios - to learn all details, explore various options introduced with the FOR SYSTEM_TIME clause.

Um die Top 10-Benutzer geordnet nach der Anzahl der besuchten Webseiten mit Stand vor einer Stunde anzuzeigen, führen Sie diese Abfrage aus:To see the top 10 users ordered by the number of visited web pages as of an hour ago, run this query:

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

Sie können diese Abfrage problemlos ändern, um die Websitebesuche vor einem Tag, einem Monat oder zu einem beliebigen Zeitpunkt in der Vergangenheit zu analysieren.You can easily modify this query to analyze the site visits as of a day ago, a month ago or at any point in the past you wish.

Um eine grundlegende statistische Analyse für den vorherigen Tag auszuführen, verwenden Sie das folgende Beispiel:To perform basic statistical analysis for the previous day, use the following example:

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

Um nach Aktivitäten eines bestimmten Benutzers in einer Zeitspanne zu suchen, verwenden Sie die CONTAINED IN-Klausel:To search for activities of a specific user, within a period of time, use the CONTAINED IN clause:

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;

Grafische Visualisierung ist besonders geeignet für temporale Abfragen, da Sie Trends und Verwendungsmuster unkompliziert in intuitiver Weise anzeigen können:Graphic visualization is especially convenient for temporal queries as you can show trends and usage patterns in an intuitive way very easily:

TemporalGraph

Entwicklung des TabellenschemasEvolving table schema

In der Regel müssen Sie das Schema der temporalen Tabelle während der App-Entwicklung ändern.Typically, you will need to change the temporal table schema while you are doing app development. Führen Sie dafür einfach die regulären ALTER TABLE-Anweisungen aus, und die Azure SQL-Datenbank leitet Änderungen entsprechend an die Verlaufstabelle weiter.For that, simply run regular ALTER TABLE statements and Azure SQL Database will appropriately propagate changes to the history table. Das folgende Skript zeigt, wie Sie ein zusätzliches Attribut für die Nachverfolgung hinzufügen können:The following script shows how you can add additional attribute for tracking:

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

Auf ähnliche Weise können Sie die Spaltendefinition ändern, während Ihre Workload aktiv ist:Similarly, you can change column definition while your workload is active:

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

Schließlich können Sie eine Spalte entfernen, die Sie nicht mehr benötigen.Finally, you can remove a column that you do not need anymore.

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

Alternativ verwenden Sie die aktuellen SSDT , um das Schema der temporalen Tabelle zu ändern, während Sie mit der Datenbank verbunden sind (Onlinemodus), oder als Teil des Datenbankprojekts (Offlinemodus).Alternatively, use latest SSDT to change temporal table schema while you are connected to the database (online mode) or as part of the database project (offline mode).

Steuern der Aufbewahrung von VerlaufsdatenControlling retention of historical data

Mit temporalen Tabellen mit Systemversionsverwaltung kann die Verlaufstabelle die Datenbankgröße stärker steigern als reguläre Tabellen.With system-versioned temporal tables, the history table may increase the database size more than regular tables. Eine große und stetig wachsende Verlaufstabelle kann ein Problem darstellen, sowohl aufgrund der reinen Speicherkosten als auch aufgrund der Tatsache, dass temporale Abfragen eine zusätzliche Belastung mit sich bringen.A large and ever-growing history table can become an issue both due to pure storage costs as well as imposing a performance tax on temporal querying. Daher ist die Entwicklung einer Aufbewahrungsrichtlinie für die Verwaltung von Daten in der Verlaufstabelle ein wichtiger Aspekt der Planung und Verwaltung des Lebenszyklus jeder temporalen Tabelle.Hence, developing a data retention policy for managing data in the history table is an important aspect of planning and managing the lifecycle of every temporal table. Die Azure SQL-Datenbank bietet Ihnen die folgenden Methoden zum Verwalten von Verlaufsdaten in der temporalen Tabelle:With Azure SQL Database, you have the following approaches for managing historical data in the temporal table:

Nächste SchritteNext steps