Microsoft SQL Server
Temporära bord

Anmärkningar

SKAPA temporära tabeller

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: På en INSERT sätter systemet värdet för ValidFrom- kolumnen till starttiden för den aktuella transaktionen (i UTC-tidszonen) baserat på systemklockan och tilldelar värdet för kolumnen ValidTo till det maximala värdet 9999- 12-31. Detta markerar raden som öppen.

UPPDATERINGAR: På en UPDATE lagrar systemet det föregående värdet på raden i historikstabellen och ställer in värdet för kolumnen ValidTo till starttiden för den aktuella transaktionen (i UTC-tidszonen) baserat på systemklockan. Detta markerar raden som stängd, med en period registrerad för vilken raden var giltig. I den aktuella tabellen uppdateras raden med sitt nya värde och systemet ställer in värdet för kolumnen ValidFrom till starttid för transaktionen (i UTC-tidszonen) baserat på systemklockan. Värdet för den uppdaterade raden i den aktuella tabellen för kolumnen ValidTo förblir det maximala värdet 9999-12-31.

DELETES : På en DELETE lagrar systemet det föregående värdet på raden i historikstabellen och ställer in värdet för kolumnen ValidTo till starttiden för den aktuella transaktionen (i UTC-tidszonen) baserat på systemklockan. Detta markerar raden som stängd, med en period registrerad för vilken föregående rad var giltig. I den aktuella tabellen tas raden bort. Frågor i den aktuella tabellen returnerar inte denna rad. Endast frågor som hanterar historikdata returnerar data som en rad är stängd för.

MERGE : På en MERGE uppför sig operationen exakt som om upp till tre uttalanden (en INSERT , UPDATE och / eller DELETE ) utförs, beroende på vad som anges som åtgärder i MERGE- uttalandet.

Tips: Tiderna inspelade i systemdatetime2-kolumnerna är baserade på själva transaktionens starttid. Till exempel kommer alla rader som är infogade i en enda transaktion att ha samma UTC-tid registrerad i kolumnen som motsvarar början på SYSTEM_TIME- perioden.

Hur frågar jag temporära data?

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;  

Returnera det verkliga värdet som anges i tid (FÖR SYSTEM_TIME AS OF )

Returnerar en tabell med rader som innehåller värden som var faktiska (aktuella) vid den angivna tidpunkten i det förflutna.

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

FÖR SYSTEM_TID MELLAN OCH

Samma som ovan i beskrivningen FÖR SYSTEM_TID FRÅN <start_datum> TILL <end_datum_tid>, förutom att tabellen över rader som returneras innehåller rader som blev aktiva på den övre gränsen definierad av slutpunkten <end_datum_tid>.

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

FÖR SYSTEM_TID FRÅN TILL

Returnerar en tabell med värdena för alla radversioner som var aktiva inom det angivna tidsområdet, oavsett om de började vara aktiva innan parametervärdet <start_date_time> för FROM-argumentet eller upphörde att vara aktivt efter parametervärdet <end_date_time> för TILL argument. Internt utförs en sammanslagning mellan den temporära tabellen och dess historiktabell och resultaten filtreras för att returnera värdena för alla radversioner som var aktiva när som helst under det angivna tidsintervallet. Rader som blev aktiva exakt på den nedre gränsen definierad av FROM-slutpunkten ingår och poster som blev aktiva exakt på den övre gränsen som definieras av TO-slutpunkten ingår inte.

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

FÖR SYSTEM_TID INNEHÅLLT IN ( , )

Returnerar en tabell med värdena för alla radversioner som öppnades och stängdes inom det angivna tidsintervallet som definieras av de två datatvärdena för argumentet CONTAINED IN. Rader som blev aktiva exakt på den undre gränsen eller upphörde att vara aktiva exakt på den övre gränsen ingår.

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

FÖR SYSTEMTID ALLA

Returnerar föreningen av rader som tillhör den aktuella och historikstabellen.

SELECT * FROM Employee
    FOR SYSTEM_TIME ALL

Skapa en minnesoptimerad systemversionerad temporär tabell och rensa upp SQL Server-historikstabellen

Att skapa en temporär tabell med en standardhistorikstabell är ett bekvämt alternativ när du vill kontrollera namngivning och fortfarande lita på system för att skapa historikstabell med standardkonfiguration. I exemplet nedan är en ny systemversionerad minnesoptimerad temporär tabell kopplad till en ny diskbaserad historikstabell.

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

Rensa upp SQL Server-historietabellen Med tiden kan historikstabellen växa betydligt. Eftersom infogning, uppdatering eller radering av data från historikstabellen inte är tillåtet, är det enda sättet att rensa upp historikstabellen först att inaktivera systemversion:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = OFF); GÅ

Ta bort onödiga data från historikstabellen:

    DELETE FROM dbo.EmployeeHistory

WHERE EndTime <= '2017-01-26 14:00:29';

och aktivera sedan systemversion igen:

ALTER TABLE dbo.Employee

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

Rengöring av historikstabellen i Azure SQL-databaser är lite annorlunda eftersom Azure SQL-databaser har inbyggt stöd för rengöring av historikstabellen. Först måste rensning av temporär historikretention vara aktiverad på databasnivå:

ALTER DATABASE CURRENT

Ställ in TEMPORAL_HISTORY_RETENTION ON GO

Ställ sedan in lagringsperioden per tabell:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 90 DAYS));

Detta kommer att ta bort alla data i historikstabellen som är äldre än 90 dagar. SQL Server 2016-databaser på plats stöder inte TEMPORAL_HISTORY_RETENTION och HISTORY_RETENTION_PERIOD och någon av ovanstående två frågor körs på SQL Server 2016-databaserna på följande platser kommer följande fel att uppstå.

För TEMPORAL_HISTORY_RETENTION kommer felet att vara:

Msg 102, Level 15, State 6, Line 34

Fel syntax nära 'TEMPORAL_HISTORY_RETENTION'.

För HISTORY_RETENTION_PERIOD kommer felet att vara:

Msg 102, Level 15, State 1, Line 39

Fel syntax nära 'HISTORY_RETENTION_PERIOD'.