Komma igång med Temporala tabeller i Azure SQL DatabaseGetting Started with Temporal Tables in Azure SQL Database

Temporala tabeller är en ny funktion för programmering i Azure SQL Database som hjälper dig att spåra och analysera den fullständiga historiken för ändringar i dina data, utan att behöva anpassad kodning.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. Temporala tabeller lagrar data som är nära förknippat med tiden kontext så att lagrade fakta kan tolkas som giltiga endast inom den specifika perioden.Temporal Tables keep data closely related to time context so that stored facts can be interpreted as valid only within the specific period. Den här egenskapen för Temporala tabeller möjliggör effektiv tidsbaserade analys och få information från de datautvecklingen.This property of Temporal Tables allows for efficient time-based analysis and getting insights from data evolution.

Den temporala ScenarioTemporal Scenario

Den här artikeln beskrivs stegen för att använda Temporala tabeller i ett scenario för programmet.This article illustrates the steps to utilize Temporal Tables in an application scenario. Anta att du vill spåra användaraktivitet på en ny webbplats som utvecklas från grunden eller på en befintlig webbplats som du vill utöka med användaren aktivitet analytics.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. I det här förenklad exemplet förutsätter vi att antalet besökta webbsidor under en viss tidsperiod är en indikator som ska samlas in och övervakas i webbplats-databasen som finns på Azure SQL Database.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. Målet med historisk analys av användaraktivitet är att få indata för att göra om webbplats och ge bättre upplevelse för besökare.The goal of the historical analysis of user activity is to get inputs to redesign website and provide better experience for the visitors.

Databasmodellen för det här scenariot är mycket enkelt – användaren aktivitet mått representeras med en enda heltalsfält PageVisited, och registreras tillsammans med grundläggande information om användarens profil.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. Dessutom för tidsbaserade analys du ser till att en serie med rader för varje användare, där varje rad motsvarar antalet sidor som en viss användare som har besökt under en viss tidsperiod.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

Som tur är kan behöver du inte att placera alla arbete i din app för att underhålla den här informationen för aktiviteten.Fortunately, you do not need to put any effort in your app to maintain this activity information. Med Temporala tabeller automatiskt den här processen – vilket ger dig fullständig flexibilitet under webbplatsdesign och mer tid att fokusera på dataanalysen själva.With Temporal Tables, this process is automated - giving you full flexibility during website design and more time to focus on the data analysis itself. Det enda du behöver göra är att se till att WebSiteInfo tabellen har konfigurerats som temporala systemversionstabeller.The only thing you have to do is to ensure that WebSiteInfo table is configured as temporal system-versioned. Visa hur du använder Temporala tabeller i det här scenariot beskrivs nedan.The exact steps to utilize Temporal Tables in this scenario are described below.

Steg 1: Konfigurera tabeller som temporalaStep 1: Configure tables as temporal

Beroende på om du startar utvecklingen av nya eller uppgradera befintliga program, ska du skapa den temporala tabeller eller ändra befintliga genom att lägga till den temporala attribut.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. I allmänhet fallet ditt scenario kan vara en blandning av de här två alternativen.In general case, your scenario can be a mix of these two options. Utför dessa åtgärder med hjälp av SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT) eller några andra utvecklingsverktyg för Transact-SQL.Perform these action using SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT) or any other Transact-SQL development tool.

Viktigt

Det rekommenderas att du alltid använder den senaste versionen av Management Studio för att förbli synkroniserad med uppdateringar av Microsoft Azure och SQL Database.It is recommended that you always use the latest version of Management Studio to remain synchronized with updates to Microsoft Azure and SQL Database. Uppdatera SQL Server Management Studio.Update SQL Server Management Studio.

Skapa ny tabellCreate new table

Använda snabbmenyn för ”ny Systemversionstabellen” i SSMS Object Explorer för att öppna frågeredigeraren med ett skript för den temporala tabellen mallar och fylla mallen med hjälp av ”ange värden för mallparametrar” (Ctrl + Skift + 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

I SSDT, väljer du ”Temporal tabell (Systemversionstabeller)” mallen när du lägger till nya objekt i databasen-projektet.In SSDT, choose “Temporal Table (System-Versioned)” template when adding new items to the database project. Som öppnar Tabelldesigner och gör att du kan enkelt ange layouten Tabell:That will open table designer and enable you to easily specify the table layout:

SSDTNewTable

Du kan också skapa den temporala tabellen genom att ange Transact-SQL-uttryck direkt, som visas i exemplet nedan.You can also create temporal table by specifying the Transact-SQL statements directly, as shown in the example below. Observera att de obligatoriska elementen för varje temporaltabellen är PERIOD-definitionen och SYSTEM_VERSIONING-satsen med en referens till en annan Användartabell som lagrar historisk radversioner: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));

När du skapar den temporala systemversionstabellen skapas automatiskt den medföljande historiktabellen med standardkonfigurationen.When you create system-versioned temporal table, the accompanying history table with the default configuration is automatically created. Standard historiktabellen innehåller ett grupperat index i B-trädet på periodkolumner (slut start) med sidan komprimering aktiverat.The default history table contains a clustered B-tree index on the period columns (end, start) with page compression enabled. Den här konfigurationen är optimalt för flesta scenarier där temporala tabeller används, särskilt för datagranskning.This configuration is optimal for the majority of scenarios in which temporal tables are used, especially for data auditing.

I det här fallet är vårt mål att utföra analyser av tidsbaserade trend över en längre historik och med större datamängder, så lagringsalternativ för historiktabellen är ett grupperat kolumnlagringsindex.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. Ett grupperat columnstore ger mycket bra komprimering och prestanda för analytiska frågor.A clustered columnstore provides very good compression and performance for analytical queries. Temporala tabeller ger dig möjlighet att konfigurera index på den aktuella och den temporala tabellen helt oberoende av varandra.Temporal Tables give you the flexibility to configure indexes on the current and temporal tables completely independently.

Anteckning

Columnstore-index är tillgängliga i Premium-nivån och standardnivån, S3 och senare.Columnstore indexes are available in the Premium tier and in the Standard tier, S3 and above.

Följande skript visar hur Standardindex för historiktabellen kan ändras till grupperade: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); 

Temporala tabeller representeras i Object Explorer med appens specifika ikon lättare kan identifiera, även om dess historiktabellen visas som en underordnad nod.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

Ändra befintlig tabellen till temporalaAlter existing table to temporal

Vi täcker alternativt scenario där WebsiteUserInfo tabellen redan finns, men har inte utformats för att behålla historiken för ändringar.Let’s cover the alternative scenario in which the WebsiteUserInfo table already exists, but was not designed to keep a history of changes. I det här fallet kan du helt enkelt utöka den befintliga tabellen ska bli temporala, som visas i följande exempel: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); 

Steg 2: Kör din arbetsbelastning regelbundetStep 2: Run your workload regularly

Den största fördelen med Temporala tabeller är att du inte behöver ändra eller anpassa din webbplats på något sätt att utföra ändringsspårning.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. När du skapat bevara Temporala tabeller transparent tidigare radversioner varje gång du utföra ändringar på dina data.Once created, Temporal Tables transparently persist previous row versions every time you perform modifications on your data.

För att kunna utnyttja automatisk ändringsspårning för det här scenariot ska vi bara uppdatera kolumnen PagesVisited varje gång när användaren avslutar göra åtaganden sessionen på webbplats:In order to leverage automatic change tracking for this particular scenario, let’s just update column PagesVisited every time when user ends her/his session on the website:

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

Det är viktigt att Observera att uppdateringsfrågan inte behöver veta exakt tid när faktiska åtgärden utfördes inte heller hur historiska data bevaras för framtida analys.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. Båda aspekter hanteras automatiskt av Azure SQL-databasen.Both aspects are automatically handled by the Azure SQL Database. Följande diagram illustrerar hur historikdata genereras på varje uppdatering.The following diagram illustrates how history data is being generated on every update.

TemporalArchitecture

Steg 3: Analysera historiska dataStep 3: Perform historical data analysis

När den temporala systemversionshanteringen är aktiverad, nu är bara en fråga från dig analys av historiska data.Now when temporal system-versioning is enabled, historical data analysis is just one query away from you. I den här artikeln kommer vi att ge några exempel som hanterar vanliga scenarier för analys - vill veta mer information om alla, utforska olika alternativ som introduceras med den FOR SYSTEM_TIME satsen.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.

Om du vill se de 10 viktigaste användarna ordnat efter antalet besökta webbsidor från och med en timme sedan, kör du den här frågan: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

Du kan enkelt ändra den här frågan för att analysera besök från och med en dag sedan, sedan månaden eller vid någon tidpunkt tidigare du vill.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.

Använd följande exempel för att utföra grundläggande statistisk analys för föregående dag: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

Om du vill söka efter aktiviteter för en specifik användare inom en viss tidsperiod, Använd instruktionen INNEHÖLL: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;

Grafisk visualisering är särskilt användbart för den temporala frågor som du kan visa trender och användningsmönster i ett intuitivt sätt enkelt:Graphic visualization is especially convenient for temporal queries as you can show trends and usage patterns in an intuitive way very easily:

TemporalGraph

Utvecklas tabellschemaEvolving table schema

Normalt behöver ändra schemat för den temporala tabellen medan du arbetar med utveckling av appar.Typically, you will need to change the temporal table schema while you are doing app development. För att kommer bara köra regelbundna ALTER TABLE-instruktioner och Azure SQL Database på lämpligt sätt vidarebefordrar ändringar till historiktabellen.For that, simply run regular ALTER TABLE statements and Azure SQL Database will appropriately propagate changes to the history table. Följande skript visar hur du kan lägga till ytterligare attribut för spårning: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';

Du kan ändra kolumndefinitionen när din arbetsbelastning är aktiv: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;

Slutligen kan du ta bort en kolumn som du inte behöver längre.Finally, you can remove a column that you do not need anymore.

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

Du kan också använda senaste SSDT att ändra schemat för den temporala tabellen medan du är ansluten till databasen (onlineläge) eller som en del av databasprojektet (offlineläge).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).

Kontrollera kvarhållningen av historiska dataControlling retention of historical data

Med systemversionerade temporala tabeller öka historiktabellen databasstorleken mer än vanliga tabeller.With system-versioned temporal tables, the history table may increase the database size more than regular tables. En stor och ständigt växande historiktabellen kan bli ett problem som både på grund av ren lagringskostnader samt aktiviteternas en prestanda skatt på den temporala frågor.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. Därför kan är utveckla en databevarandeprincip för att hantera data i historiktabellen en viktig aspekt av planering och hantering av livscykeln för varje temporal tabell.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. Med Azure SQL Database kan ha du följande metoder för att hantera historisk data i den temporala tabellen:With Azure SQL Database, you have the following approaches for managing historical data in the temporal table:

Nästa stegNext steps

Kolla in detaljerad information om Temporala tabeller MSDN-dokumentationen.For detailed information on Temporal Tables, check out MSDN documentation. Gå till Channel 9 att höra en framgångshistoria för kundens verkliga temporala implementering och titta på en live temporala demonstration.Visit Channel 9 to hear a real customer temporal implementation success story and watch a live temporal demonstration.