Aan de slag met tijdelijke tabellen

van toepassing op:Azure SQL DatabaseAzure SQL Managed InstanceSQL-database in Fabric

Tijdelijke tabellen zijn een programmeerfunctie waarmee u de volledige geschiedenis van wijzigingen in uw gegevens kunt bijhouden en analyseren, zonder dat u aangepaste codering nodig hebt. Tijdelijke tabellen houden gegevens nauw verwant aan tijdcontext, zodat opgeslagen feiten alleen binnen de specifieke periode als geldig kunnen worden geïnterpreteerd. Deze eigenschap van tijdelijke tabellen biedt efficiënte op tijd gebaseerde analyse en het verkrijgen van inzichten uit gegevensontwikkeling.

Temporal scenario

Dit artikel illustreert de stappen voor het gebruik van tijdelijke tabellen in een toepassingsscenario. Stel dat u gebruikersactiviteiten wilt bijhouden op een nieuwe website die volledig nieuw wordt ontwikkeld of op een bestaande website die u wilt uitbreiden met analyse van gebruikersactiviteiten. In dit vereenvoudigde voorbeeld wordt ervan uitgegaan dat het aantal bezochte webpagina's gedurende een bepaalde periode een indicator is die moet worden vastgelegd en bewaakt in de websitedatabase die wordt gehost in Azure SQL Database of Azure SQL Managed Instance. Het doel van de historische analyse van gebruikersactiviteit is om invoer te krijgen om de website opnieuw te ontwerpen en een betere ervaring voor de bezoekers te bieden.

Het databasemodel voor dit scenario is eenvoudig: metrische gegevens over gebruikersactiviteit worden weergegeven met één geheel getalveld, PageVisited en worden samen met basisinformatie over het gebruikersprofiel vastgelegd. Daarnaast houdt u voor een op tijd gebaseerde analyse een reeks rijen voor elke gebruiker, waarbij elke rij het aantal pagina's vertegenwoordigt dat een bepaalde gebruiker binnen een bepaalde periode heeft bezocht.

Gelukkig hoeft u geen moeite te doen in uw app om deze activiteitsgegevens te behouden. Met tijdelijke tabellen wordt dit proces geautomatiseerd, waardoor u volledige flexibiliteit krijgt tijdens het ontwerpen van websites en meer tijd om u te richten op de gegevensanalyse zelf. Het enige wat u moet doen, is ervoor te zorgen dat WebSiteInfo de tabel is geconfigureerd als tijdelijke systeemversie. De exacte stappen voor het gebruik van tijdelijke tabellen in dit scenario worden hieronder beschreven.

Stap 1: Tabellen configureren als tijdelijk

Afhankelijk van of u nieuwe ontwikkeling start of een bestaande toepassing bijwerkt, maakt u tijdelijke tabellen of wijzigt u bestaande tabellen door tijdelijke kenmerken toe te voegen. In het algemeen kan uw scenario een combinatie van deze twee opties zijn. Voer deze actie uit met behulp van SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), de mssql-extensie voor Visual Studio Code of een ander Transact-SQL ontwikkelhulpprogramma.

Nieuwe tabel maken

• Gebruik het contextmenu-item New System-Versioned Table in SSMS Object Explorer om de queryeditor te openen met een tijdelijke tabelsjabloonscript en gebruik vervolgens Waarden opgeven voor sjabloonparameters (Ctrl+Shift+M) om de sjabloon te vullen:

  

• Kies in SSDT de sjabloon Tijdelijke tabel (System-Versioned)' bij het toevoegen van nieuwe items aan het databaseproject. Hiermee opent u de ontwerpfunctie voor tabellen en kunt u eenvoudig de tabelindeling opgeven:

  

• U kunt ook een tijdelijke tabel maken door de Transact-SQL instructies rechtstreeks op te geven, zoals wordt weergegeven in het volgende voorbeeld. De verplichte elementen van elke tijdelijke tabel zijn de PERIOD definitie en de SYSTEM_VERSIONING component met een verwijzing naar een andere gebruikerstabel die historische rijversies opslaat:

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

Standaardgeschiedenistabel

Wanneer u een tijdelijke tabel met systeemversies maakt, wordt de bijbehorende geschiedenistabel met de standaardconfiguratie automatisch gemaakt. De standaardgeschiedenistabel bevat een geclusterde B-tree-index op de periodekolommen (einde, begin) met paginacompressie ingeschakeld. Deze configuratie is optimaal voor de meeste scenario's waarin tijdelijke tabellen worden gebruikt, met name voor gegevenscontrole.

In dit specifieke geval streven we ernaar om op tijd gebaseerde trendanalyse uit te voeren in een langere gegevensgeschiedenis en met grotere gegevenssets, zodat de opslagkeuze voor de geschiedenistabel een geclusterde columnstore-index is. Een geclusterde columnstore biedt goede compressie en prestaties voor analytische query's. Tijdelijke tabellen bieden u de flexibiliteit om indexen voor de huidige en tijdelijke tabellen volledig onafhankelijk te configureren.

In het volgende script ziet u hoe de standaardindex in de geschiedenistabel kan worden gewijzigd in de geclusterde columnstore:

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

Tijdelijke tabellen worden weergegeven in objectverkenner met het specifieke pictogram voor eenvoudigere identificatie, terwijl de geschiedenistabel wordt weergegeven als een onderliggend knooppunt.

Bestaande tabel wijzigen in tijdelijk

Laten we het alternatieve scenario bespreken waarin de WebsiteUserInfo tabel al bestaat, maar die niet is ontworpen om een geschiedenis van wijzigingen bij te houden. In dit geval kunt u de bestaande tabel eenvoudig uitbreiden om tijdelijk te worden, zoals wordt weergegeven in het volgende voorbeeld:

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

Stap 2: uw workload regelmatig uitvoeren

Het belangrijkste voordeel van tijdelijke tabellen is dat u uw website niet hoeft te wijzigen of aan te passen om wijzigingen bij te houden. Zodra de tijdelijke tabellen zijn gemaakt, blijven eerdere rijversies transparant behouden telkens wanneer u wijzigingen aan uw gegevens uitvoert.

Als u automatische wijzigingen bijhouden voor dit specifieke scenario wilt gebruiken, gaan we de kolom PagesVisited bijwerken telkens wanneer een gebruiker de sessie op de website beëindigt:

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

Het is belangrijk om te weten dat de updatequery niet precies hoeft te weten wanneer de werkelijke bewerking heeft plaatsgevonden, noch hoe historische gegevens worden bewaard voor toekomstige analyse. Beide aspecten worden automatisch afgehandeld. In het volgende diagram ziet u hoe geschiedenisgegevens worden gegenereerd voor elke update.

Stap 3: Historische gegevensanalyse uitvoeren

Wanneer tijdelijke systeemversiebeheer is ingeschakeld, is historische gegevensanalyse slechts één query van u verwijderd. In dit artikel geven we enkele voorbeelden die betrekking hebben op veelvoorkomende analysescenario's. Leer alle details door de verschillende opties te verkennen die zijn geïntroduceerd met de FOR SYSTEM_TIME clausule.

Voer deze query uit om de top 10 gebruikers te zien, gerangschikt op het aantal bezochte webpagina's tot een uur geleden.

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

U kunt deze query eenvoudig wijzigen om de sitebezoeken te analyseren vanaf een dag geleden, een maand geleden of op elk gewenst moment in het verleden.

Als u een eenvoudige statistische analyse wilt uitvoeren voor de vorige dag, gebruikt u het volgende voorbeeld:

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

Om binnen een bepaalde periode te zoeken naar activiteiten van een specifieke gebruiker, gebruikt u de CONTAINED IN-clausule.

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 visualisatie is vooral handig voor tijdelijke query's, omdat u trends en gebruikspatronen op een intuïtieve manier heel eenvoudig kunt weergeven:

Tabelschema ontwikkelen

Normaal gesproken moet u het tijdelijke tabelschema wijzigen terwijl u app-ontwikkeling uitvoert. Voer hiervoor gewoon reguliere ALTER TABLE instructies uit en Azure SQL Database of Azure SQL Managed Instance voert wijzigingen in de geschiedenistabel op de juiste manier door.

In het volgende script ziet u hoe u extra kenmerk voor het bijhouden kunt toevoegen:

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

Op dezelfde manier kunt u de kolomdefinitie wijzigen terwijl uw workload actief is:

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

Ten slotte kunt u een kolom verwijderen die u niet meer nodig hebt.

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

U kunt ook de nieuwste SSDT gebruiken om het tijdelijke tabelschema te wijzigen terwijl u bent verbonden met de database (onlinemodus) of als onderdeel van het databaseproject (offlinemodus).

Retentie van historische gegevens beheren

Met tijdelijke tabellen met systeemversies kan de geschiedenistabel de database groter maken dan gewone tabellen. Een grote en steeds groeiende geschiedenistabel kan een probleem worden vanwege pure opslagkosten en het opleggen van een prestatiebelasting voor tijdelijke query's. Daarom is het ontwikkelen van een beleid voor gegevensretentie voor het beheren van gegevens in de geschiedenistabel een belangrijk aspect van het plannen en beheren van de levenscyclus van elke tijdelijke tabel. Houd rekening met de volgende benaderingen voor het beheren van historische gegevens in de tijdelijke tabel:

• Table Partitioning
• Aangepast opschoningsscript

Remarks

In zowel Azure SQL Database die is geconfigureerd voor spiegeling in Fabric - en Fabric SQL-database, kunt u tijdelijke tabellen maken, maar de respectieve geschiedenistabellen worden niet gespiegeld naar Fabric OneLake. Zie SYSTEM_VERSIONING voor specifieke informatie over het instellen van de vlag op temporele tabellen.

Related content

• Temporal Tables