Condividi tramite

Creare una tabella temporale con controllo delle versioni di sistema

  • Articolo

Si applica a: SQL Server 2016 (13.x) e versioni successive database SQL di Azure Istanza gestita di SQL di Azure database SQL in Microsoft Fabric

Esistono 3 modi per creare una tabella temporale con controllo delle versioni di sistema quando si considera come viene definita la tabella di cronologia:

  • Tabella temporale con una tabella di cronologia anonima: si specifica lo schema della tabella corrente e il sistema crea una tabella di cronologia corrispondente con un nome generato automaticamente.

  • Tabella temporale con una tabella di cronologia predefinita: si specificano il nome dello schema della tabella di cronologia e il nome della tabella e il sistema crea la tabella di cronologia in quello schema.

  • Tabella temporale con una tabella di cronologia definita dall'utente creata in precedenza: si crea la tabella di cronologia più adatta alle esigenze e poi si fa riferimento alla tabella durante la creazione della tabella temporale.

Creare una tabella temporale con una tabella di cronologia anonima

Creare una tabella temporale con una tabella di cronologia anonima è una soluzione comoda per poter generare rapidamente oggetti, specialmente nei prototipi e negli ambienti di test. È inoltre il modo più semplice per creare una tabella temporale in quanto non richiede alcun parametro nella clausola SYSTEM_VERSIONING. Nell'esempio seguente viene creata una nuova tabella con il controllo delle versioni di sistema attivato, senza definire il nome della tabella di cronologia.

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);

Osservazioni:

Una tabella temporale con il controllo delle versioni di sistema deve avere una chiave primaria definita e avere esattamente un PERIOD FOR SYSTEM_TIME definito con due colonne datetime2, dichiarate come GENERATED ALWAYS AS ROW START o GENERATED ALWAYS AS ROW END.

Le colonne PERIOD sono sempre considerate prive di supporto per i valori Null, anche se il supporto dei valori Null non è specificato. Se le colonne PERIOD sono definite esplicitamente per ammettere valori che ammettono i valori Null, l'istruzione CREATE TABLE non sarà eseguita.

La tabella di cronologia deve sempre essere allineata a livello di schema con la tabella corrente o la tabella temporale, in termini di numero e nomi delle colonne, ordinamento e tipi di dati.

Una tabella di cronologia anonima viene creata automaticamente nello stesso schema della tabella corrente o della tabella temporale.

Il nome della tabella di cronologia anonima presenta il seguente formato: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_<suffix>. Il suffisso è facoltativo e viene aggiunto solo se la prima parte del nome tabella non è univoco.

La tabella di cronologia viene creata come tabella rowstore. Se possibile, viene applicata la compressione PAGE, altrimenti la tabella di cronologia non viene compressa. Ad esempio, alcune configurazioni di tabella, come le colonne SPARSE, non consentono la compressione.

Viene creato un indice cluster predefinito per la tabella di cronologia con un nome generato automaticamente in formato IX_<history_table_name>. L'indice cluster contiene le colonne PERIOD (inizio, fine).

Nel database SQL di Infrastruttura la tabella di cronologia creata non viene con mirroring in Fabric OneLake.

Per creare la tabella corrente come una tabella ottimizzata per la memoria, si veda Tabelle temporali con controllo delle versioni di sistema con tabelle ottimizzate per la memoria.

Creare una tabella temporale con una tabella di cronologia predefinita

Creare una tabella temporale con una tabella di cronologia predefinita è una soluzione comoda quando si vuole controllare la denominazione e al tempo stesso lasciare che sia il sistema a creare la tabella di cronologia con la configurazione predefinita. Nell'esempio seguente viene creata una nuova tabella con il controllo delle versioni di sistema attivato, definendo esplicitamente il nome della tabella di cronologia.

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Osservazioni:

La tabella di cronologia viene creata con le stesse regole valide per la creazione di una tabella di cronologia "anonima", inoltre si applicano le seguenti regole specifiche alla tabella di cronologia denominata.

  • Il nome dello schema è obbligatorio per il parametro HISTORY_TABLE.

  • Se lo schema specificato non esiste, l'istruzione CREATE TABLE non viene seguita.

  • Se la tabella specificata dal parametro HISTORY_TABLE esiste già, è convalidata rispetto alla nuova tabella temporale che viene creata in termini di coerenza dello schema e coerenza dei dati temporali. Se si specifica una tabella di cronologia non valida, l'istruzione CREATE TABLE non viene eseguita.

Creare una tabella temporale con una tabella di cronologia definita dall'utente

Creare una tabella temporale con una tabella di cronologia definita dall'utente è una soluzione comoda quando l'utente vuole specificare la tabella di cronologia con determinate opzioni di archiviazione e indici diversi ottimizzati per le query cronologiche. Nell'esempio seguente viene creata una tabella di cronologia definita dall'utente con uno schema allineato con la tabella temporale che viene creata. Per questa tabella di cronologia definita dall'utente vengono creati un indice columnstore cluster e un indice rowstore non cluster (albero B) extra per le ricerche di punti. Dopo la creazione di questa tabella di cronologia definita dall'utente, viene creata la tabella temporale specificando la tabella di cronologia definita dall'utente come tabella di cronologia predefinita.

Nota

Nella documentazione viene usato in modo generico il termine albero B in riferimento agli indici. Negli indici rowstore, il motore di database implementa un albero B+. Ciò non si applica a indici columnstore o a indici in tabelle ottimizzate per la memoria. Per altre informazioni, vedere Architettura e guida per la progettazione degli indici SQL Server e Azure SQL.

CREATE TABLE DepartmentHistory
(
    DeptID INT NOT NULL,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 NOT NULL,
    ValidTo DATETIME2 NOT NULL
);
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory ON DepartmentHistory;

CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_Period_Columns
ON DepartmentHistory (ValidTo, ValidFrom, DeptID);
GO

CREATE TABLE Department
(
    DeptID int NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Osservazioni:

Se si prevede di eseguire query analitiche sui dati storici che impiegano funzioni di aggregazione o suddivisione in finestre, si consiglia vivamente di creare un indice columnstore cluster come indice primario per la compressione dei dati e le prestazioni delle query.

Se si prevede di utilizzare le tabelle temporali per la verifica dei dati (cioè per la ricerca di modifiche cronologiche per una singola riga della tabella corrente), è necessario creare una tabella di cronologia rowstore con un indice cluster.

La tabella di cronologia non può avere una chiave primaria, chiavi esterne, indici univoci, vincoli di tabella o trigger. Non può essere configurata per Change Data Capture, rilevamento delle modifiche, replica transazionale o replica di tipo merge.

Nel database SQL di Infrastruttura e in database SQL di Azure con il mirroring di Fabric configurato, quando si usa una tabella esistente come tabella di cronologia durante la creazione della tabella temporale, la tabella esistente smette di eseguire il mirroring.

Modificare una tabella non temporale per trasformarla in una tabella temporale con controllo delle versioni di sistema

È possibile attivare il controllo delle versioni di sistema usando una tabella non-temporale esistente, come quando si vuole eseguire la migrazione di una soluzione temporale personalizzata al supporto predefinito.

Ad esempio, si può avere un set di tabelle in cui il controllo delle versioni è implementato mediante trigger. L'uso del controllo delle versioni di sistema temporale è meno complesso e offre altri vantaggi, fra cui:

  • Cronologia non modificabile
  • Nuova sintassi per le query con spostamento cronologico
  • Prestazioni DML ottimizzate
  • Costi di manutenzione minimi

Quando si converte una tabella esistente, è consigliabile usare la clausola HIDDEN per nascondere le nuove colonne PERIOD (le colonne datetime2 ValidFrom e ValidTo) al fine di evitare conseguenze sulle applicazioni esistenti che non specificano esplicitamente i nomi delle colonne (ad es. SELECT * o INSERT senza un elenco delle colonne) o non sono progettate per gestire nuove colonne.

Aggiungere il controllo delle versioni a tabelle non temporali

Se si vuole iniziare a monitorare le modifiche di una tabella non temporale che contiene i dati, è necessario aggiungere la definizione PERIOD e facoltativamente specificare un nome per una tabella di cronologia vuota che sarà creata da SQL Server:

CREATE SCHEMA History;
GO

ALTER TABLE InsurancePolicy ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME(),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);
GO

ALTER TABLE InsurancePolicy
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.InsurancePolicy));
GO

Importante

La precisione per DATETIME2 deve allinearsi con la precisione per la tabella sottostante. Vedere le osservazioni seguenti.

Osservazioni:

L'aggiunta di colonne che non ammettono i valori Null con valori predefiniti a una tabella esistente contenente dati corrisponde a un'operazione di dimensionamento dei dati in tutte le edizioni eccetto SQL Server Enterprise Edition (in cui corrisponde a un'operazione di metadati). Con una tabella di cronologia esistente di grandi dimensioni contenente dati in SQL Server Standard Edition, aggiungere una colonna che non supporta i valori Null può essere un'operazione costosa.

I vincoli per le colonne di inizio e fine periodo devono essere scelti attentamente:

  • Il valore predefinito per la colonna di inizio specifica da che punto nel tempo le righe esistenti sono considerate valide. Non può essere specificato come valore datetime futuro.

  • L'ora di fine deve essere specificata come valore massimo per una determinata precisione datetime2, ad esempio 9999-12-31 23:59:59 o 9999-12-31 23:59:59.9999999.

Se si aggiunge PERIOD verrà eseguita una verifica coerenza dei dati sulla tabella corrente per garantire che i valori esistenti per le colonne di Period siano validi.

Se viene specificata una tabella di cronologia esistente quando si attiva SYSTEM_VERSIONING, sarà eseguita una verifica coerenza sui dati nella tabella corrente e nella tabella di cronologia. Può essere ignorata se si specifica DATA_CONSISTENCY_CHECK = OFF come parametro extra.

Eseguire la migrazione di tabelle esistenti al supporto incorporato

Questo esempio mostra come eseguire la migrazione da una soluzione esistente basata su trigger al supporto temporale incorporato. In questo esempio si presuppone che la soluzione personalizzata corrente suddivida i dati attuali e storici in due tabelle utente separate (ProjectTaskCurrent e ProjectTaskHistory).

Se la soluzione esistente usa una singola tabella per archiviare le righe attuali e cronologiche, è necessario suddividere i dati in due tabelle prima di eseguire i passaggi della migrazione illustrati nel seguente esempio. Prima di tutto, eliminare il trigger nella tabella temporale futura. Assicurarsi quindi che le colonne PERIOD non ammettano i valori Null.

/* Drop trigger on future temporal table */
DROP TRIGGER ProjectCurrent_OnUpdateDelete;

/* Make sure that future period columns are non-nullable */
ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent ADD PERIOD
FOR SYSTEM_TIME([ValidFrom], [ValidTo]);

ALTER TABLE ProjectTaskCurrent SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.ProjectTaskHistory,
        DATA_CONSISTENCY_CHECK = ON
    )
);

Osservazioni:

Facendo riferimento a colonne esistenti nella definizione di PERIOD modifica implicitamente generated_always_type in AS_ROW_START e AS_ROW_END per queste colonne.

Se si aggiunge PERIOD verrà eseguita una verifica coerenza dei dati sulla tabella corrente per garantire che i valori esistenti per le colonne di Period siano validi.

Si raccomanda di impostare SYSTEM_VERSIONING con DATA_CONSISTENCY_CHECK = ON per eseguire le verifiche coerenza dei dati sui dati esistenti.

Se si preferiscono colonne nascoste, usare il comando ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.

Contenuto correlato