Microsoft SQL Server
Tijdelijke tabellen

Opmerkingen

CREATE tijdelijke tabellen

CREATE TABLE dbo.Employee   
(    
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED   
  , [Name] nvarchar(100) NOT NULL  
  , [Position] varchar(100) NOT NULL   
  , [Department] varchar(100) NOT NULL  
  , [Address] nvarchar(1024) NOT NULL  
  , [AnnualSalary] decimal (10,2) NOT NULL  
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END  
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)  
 )    
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));  

INSERTS: Op een INSERT stelt het systeem de waarde voor de ValidFrom- kolom in op de begintijd van de huidige transactie (in de UTC-tijdzone) op basis van de systeemklok en wijst de waarde voor de ValidTo- kolom toe aan de maximale waarde van 9999- 12-31. Dit markeert de rij als open.

UPDATES: Op een UPDATE slaat het systeem de vorige waarde van de rij op in de historietabel en stelt de waarde voor de kolom ValidTo in op de begintijd van de huidige transactie (in de UTC-tijdzone) op basis van de systeemklok. Dit markeert de rij als gesloten, met een geregistreerde periode waarvoor de rij geldig was. In de huidige tabel wordt de rij bijgewerkt met de nieuwe waarde en stelt het systeem de waarde voor de kolom ValidFrom in op de begintijd voor de transactie (in de UTC-tijdzone) op basis van de systeemklok. De waarde voor de bijgewerkte rij in de huidige tabel voor de kolom ValidTo blijft de maximale waarde van 9999-12-31.

VERWIJDEREN : Bij een VERWIJDEREN slaat het systeem de vorige waarde van de rij op in de historietabel en stelt de waarde voor de kolom ValidTo in op de begintijd van de huidige transactie (in de UTC-tijdzone) op basis van de systeemklok. Dit markeert de rij als gesloten, met een geregistreerde periode waarvoor de vorige rij geldig was. In de huidige tabel wordt de rij verwijderd. Query's van de huidige tabel retourneren deze rij niet. Alleen query's die omgaan met historische gegevens retourneren gegevens waarvoor een rij is gesloten.

MERGE : op een MERGE gedraagt de bewerking zich precies alsof er maximaal drie instructies (een INSERT , een UPDATE en / of een DELETE ) worden uitgevoerd, afhankelijk van wat is opgegeven als acties in de MERGE- instructie.

Tip: de tijden die zijn vastgelegd in de kolommen datetime2 van het systeem zijn gebaseerd op de begintijd van de transactie zelf. Voor alle rijen die in een enkele transactie zijn ingevoegd, wordt bijvoorbeeld dezelfde UTC-tijd geregistreerd in de kolom die overeenkomt met het begin van de periode SYSTEM_TIME .

Hoe vraag ik tijdelijke gegevens op?

SELECT * FROM Employee   
    FOR SYSTEM_TIME    
        BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'   
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;  

Retourneer het werkelijke waarde opgegeven tijdstip (VOOR SYSTEM_TIME ALS VAN )

Retourneert een tabel met rijen met de waarden die actueel (huidig) waren op het opgegeven tijdstip in het verleden.

SELECT * FROM Employee   
    FOR SYSTEM_TIME AS  OF '2016-08-06 08:32:37.91'

VOOR SYSTEM_TIME TUSSEN EN

Hetzelfde als hierboven in de beschrijving FOR SYSTEM_TIME VAN <start_date_time> TOT <end_date_time>, behalve dat de tabel met geretourneerde rijen rijen bevat die actief werden op de bovengrens gedefinieerd door het eindpunt <end_date_time>.

SELECT * FROM Employee   
    FOR SYSTEM_TIME BETWEEN  '2015-01-01' AND '2015-12-31' 

VOOR SYSTEM_TIME VAN NAAR

Retourneert een tabel met de waarden voor alle rijversies die actief waren binnen het opgegeven tijdbereik, ongeacht of ze actief werden vóór de parameterwaarde <start_date_time> voor het argument FROM of niet meer actief waren na de parameterwaarde <end_date_time> voor de TO argument. Intern wordt een unie uitgevoerd tussen de temporele tabel en de historietabel ervan en worden de resultaten gefilterd om de waarden te retourneren voor alle rijversies die op elk moment gedurende het opgegeven tijdsbereik actief waren. Rijen die actief werden op precies de ondergrens gedefinieerd door het VAN-eindpunt worden opgenomen en records die actief werden op precies de bovengrens gedefinieerd door het TO-eindpunt worden niet opgenomen.

SELECT * FROM Employee   
    FOR SYSTEM_TIME FROM '2015-01-01' TO '2015-12-31' 

VOOR SYSTEM_TIME BEVAT IN ( , )

Retourneert een tabel met de waarden voor alle rijversies die zijn geopend en gesloten binnen het opgegeven tijdbereik dat is gedefinieerd door de twee datetime-waarden voor het argument CONTAINED IN. Rijen die precies op de ondergrens actief werden of niet meer precies op de bovengrens actief waren, zijn inbegrepen.

SELECT * FROM Employee
    FOR SYSTEM_TIME CONTAINED IN ('2015-04-01', '2015-09-25')   

VOOR SYSTEM_TIME ALL

Retourneert de unie van rijen die bij de huidige en de geschiedenistabel horen.

SELECT * FROM Employee
    FOR SYSTEM_TIME ALL

Een geheugengeoptimaliseerde systeemversie van de tijdelijke tabel maken en de SQL Server-historietabel opschonen

Het maken van een tijdelijke tabel met een standaardgeschiedenistabel is een handige optie als u de naamgeving wilt beheren en toch op het systeem wilt vertrouwen om de geschiedenistabel met de standaardconfiguratie te maken. In het onderstaande voorbeeld is een nieuwe geheugengeoptimaliseerde tijdelijke systeemtabel gekoppeld aan een nieuwe op geschiedenis gebaseerde schijftabel.

CREATE SCHEMA History  
GO  
CREATE TABLE dbo.Department   
(  
    DepartmentNumber char(10) NOT NULL PRIMARY KEY NONCLUSTERED,   
    DepartmentName varchar(50) NOT NULL,   
    ManagerID int  NULL,   
    ParentDepartmentNumber char(10) NULL,   
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,   
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,     
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)     
)  
WITH   
    (  
        MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA,  
        SYSTEM_VERSIONING = ON ( HISTORY_TABLE = History.DepartmentHistory )   
    );  

De geschiedenistabel van SQL Server opschonen Na verloop van tijd kan de geschiedenistabel aanzienlijk groeien. Aangezien het invoegen, bijwerken of verwijderen van gegevens uit de historietabel niet is toegestaan, is de enige manier om de historietabel op te ruimen eerst het versienummer van het systeem uit te schakelen:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = UIT); GAAN

Verwijder overbodige gegevens uit de geschiedenistabel:

    DELETE FROM dbo.EmployeeHistory

WAAR Eindtijd <= '2017-01-26 14:00:29';

en schakel vervolgens systeemversie opnieuw in:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo]. [EmployeeHistory], DATA_CONSISTENCY_CHECK = ON));

Het opschonen van de geschiedenistabel in Azure SQL-databases is een beetje anders, omdat Azure SQL-databases ingebouwde ondersteuning hebben voor het opschonen van de geschiedenistabel. Ten eerste moet het opschonen van tijdelijke geschiedenisretentie op databaseniveau worden ingeschakeld:

ALTER DATABASE CURRENT

STEL TEMPORAL_HISTORY_RETENTION OP GAAN

Stel vervolgens de bewaartermijn per tabel in:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = AAN (HISTORY_RETENTION_PERIOD = 90 DAGEN));

Hiermee worden alle gegevens in de historietabel verwijderd die ouder zijn dan 90 dagen. SQL Server 2016 lokale databases ondersteunen TEMPORAL_HISTORY_RETENTION en HISTORY_RETENTION_PERIOD niet en beide bovenstaande query's worden uitgevoerd op de lokale SQL Server 2016-databases de volgende fouten zullen optreden.

Voor TEMPORAL_HISTORY_RETENTION fout zal zijn:

Msg 102, Level 15, State 6, Line 34

Onjuiste syntaxis in de buurt van 'TEMPORAL_HISTORY_RETENTION'.

Voor HISTORY_RETENTION_PERIOD fout zal zijn:

Msg 102, Level 15, State 1, Line 39

Onjuiste syntaxis in de buurt van 'HISTORY_RETENTION_PERIOD'.