Creazione di una tabella temporale con controllo delle versioni di sistemaCreating a System-Versioned Temporal Table

QUESTO ARGOMENTO SI APPLICA A: sìSQL Server (a partire dalla versione 2016)sìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Quando si crea una tabella temporale con controllo delle versioni di sistema esistono tre modi per specificare la tabella di cronologia:There are three ways to create a system-versioned temporal table with regards to how the history table is specified:

  • 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.Temporal table with an anonymous history table: you specify the schema of the current table and let the system create a corresponding history table with auto-generated name.

  • 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.Temporal table with a default history table: you specify the history table schema name and table name and let the system create a history table in that 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.Temporal table with a user-defined history table created beforehand: you create a history table that fits best your needs and then reference that table during temporal table creation.

Creazione di una tabella temporale con una tabella di cronologia anonimaCreating a temporal table with an anonymous history table

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.Creating a temporal table with an "anonymous" history table is a convenient option for quick object creation, especially in prototypes and test environments. È inoltre il modo più semplice di creare una tabella temporale in quanto non richiede alcun parametro nella clausola SYSTEM_VERSIONING .It is also the simplest way to create a temporal table since it doesn’t require any parameter in SYSTEM_VERSIONING clause. Nell'esempio seguente viene creata una nuova tabella con il controllo delle versioni di sistema attivato, senza definire il nome della tabella di cronologia.In the example below, a new table is created with system-versioning enabled without defining the name of the history table.

CREATE TABLE Department   
(    
     DeptID int NOT NULL PRIMARY KEY CLUSTERED  
   , DeptName varchar(50) NOT NULL  
   , ManagerID INT  NULL  
   , ParentDeptID int NULL  
   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL  
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL  
   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)     
)    
WITH (SYSTEM_VERSIONING = ON)   
;  

Note importantiImportant remarks

  • 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 / ENDA system-versioned temporal table must have a primary key defined and have exactly one PERIOD FOR SYSTEM_TIME defined with two datetime2 columns, declared as GENERATED ALWAYS AS ROW START / END

  • Le colonne PERIOD sono sempre considerate prive di supporto per i valori Null, anche se il supporto dei valori Null non è specificato.The PERIOD columns are always assumed to be non-nullable, even if nullability is not specified. Se le colonne PERIOD sono definite esplicitamente come dotate di supporto per i valori Null, l'istruzione CREATE TABLE non sarà eseguita.If the PERIOD columns are explicitly defined as nullable, the CREATE TABLE statement will fail.

  • La tabella di cronologia deve sempre essere allineata a livello di schema con la tabella corrente o la tabella temporale, in termini di numero di colonne, nomi delle colonne, ordinamento e tipi di dati.The history table must always be schema-aligned with the current or temporal table, in terms of number of columns, column names, ordering and data types.

  • Una tabella di cronologia anonima viene creata automaticamente nello stesso schema della tabella corrente o della tabella temporale.An anonymous history table is automatically created in the same schema as current or temporal table.

  • Il nome della tabella della cronologia anonima ha il seguente formato: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_[suffisso].The anonymous history table name has the following format: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_[suffix]. Il suffisso è facoltativo e sarà aggiunto solo se la prima parte del nome della tabella non è univoco.Suffix is optional and it will be added only if the first part of the table name is not unique.

  • La tabella di cronologia viene creata come tabella rowstore.The history table is created as a rowstore table. Se possibile, viene applicata la compressione di pagina, altrimenti la tabella di cronologia sarà non compressa.PAGE compression is applied if possible, otherwise the history table will be uncompressed. Ad esempio, alcune configurazioni di tabella, come le colonne di tipo sparse, non consentono la compressione.For example, some table configurations, such as SPARSE columns, do not allow compression.

  • Viene creato un indice cluster predefinito per la tabella di cronologia con un nome generato automaticamente in formato IX_<history_table_name>.A default clustered index is created for the history table with an auto-generated name in format IX_<history_table_name>. L'indice cluster contiene le colonne PERIOD (inizio, fine).The clustered index contains the PERIOD columns (end, start).

  • Per creare la tabella corrente come una tabella ottimizzata per la memoria, vedere Tabelle temporali con controllo delle versioni di sistema con tabelle ottimizzate per la memoria.To create the current table as a memory-optimized table, see System-Versioned Temporal Tables with Memory-Optimized Tables.

Creazione di una tabella temporale con una tabella di cronologia predefinitaCreating a temporal table with a default history table

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.Creating a temporal table with a default history table is a convenient option when you want to control naming and still rely on the system to create the history table with the default configuration. Nell'esempio seguente viene creata una nuova tabella con il controllo delle versioni di sistema attivato, definendo esplicitamente il nome della tabella di cronologia.In the example below, a new table is created with system-versioning enabled with the name of the history table explicitly defined.

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

Note importantiImportant remarks

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.The history table is created using the same rules as apply to creating an "anonymous" history table, with the following rules that apply specifically to the named history table.

  • Il nome dello schema è obbligatorio per il parametro HISTORY_TABLE .The schema name is mandatory for the HISTORY_TABLE parameter.

  • Se lo schema specificato non esiste, l'istruzione CREATE TABLE non verrà seguita.If the specified schema does not exist, the CREATE TABLE statement will fail.

  • Se la tabella specificata dal parametro HISTORY_TABLE esiste già, sarà convalidata rispetto alla nuova tabella temporale che viene creata in termini di coerenza dello schema e coerenza dei dati temporali.If the table specified by the HISTORY_TABLE parameter already exists, it will be validated against the newly created temporal table in terms of schema consistency and temporal data consistency. Se si specifica una tabella di cronologia non valida, l'istruzione CREATE TABLE non verrà eseguita.If you specify an invalid history table, the CREATE TABLE statement will fail.

Creazione di una tabella temporale con una tabella di cronologia definita dall'utenteCreating a temporal table with a user-defined history table

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 aggiuntivi.Creating a temporal table with user-defined history table is a convenient option when the user wants to specify a history table with specific storage options and additional indexes. Nell'esempio seguente viene creata una tabella di cronologia definita dall'utente con uno schema allineato con la tabella temporale che verrà creata.In the example below, a user-defined history table is created with a schema that is aligned with the temporal table that will be created. Per questa tabella di cronologia definita dall'utente vengono creati un indice columnstore cluster e un indice rowstore non cluster (albero B) aggiuntivo per le ricerche di punti.To this user-defined history table, a clustered columnstore index and additional non clustered rowstore (B-tree) index is created for point lookups. Dopo la creazione di questa tabella di cronologia definita dall'utente, viene creata la tabella temporale con controllo delle versioni di sistema specificando la tabella di cronologia definita dall'utente come tabella di cronologia predefinita.After this user-defined history table is created, the system-versioned temporal table is created specifying the user-defined history table as the default history table.

CREATE TABLE DepartmentHistory   
(    
     DeptID int NOT NULL  
   , DeptName varchar(50) NOT NULL  
   , ManagerID INT  NULL  
   , ParentDeptID int NULL  
   , SysStartTime datetime2 NOT NULL  
   , SysEndTime datetime2 NOT NULL   
);   
GO   
CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory   
   ON DepartmentHistory;   
CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS   
   ON DepartmentHistory (SysEndTime, SysStartTime, DeptID);   
GO   
CREATE TABLE Department   
(    
    DeptID int NOT NULL PRIMARY KEY CLUSTERED  
   , DeptName varchar(50) NOT NULL  
   , ManagerID INT  NULL  
   , ParentDeptID int NULL  
   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL  
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL     
   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)      
)    
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory))   
;  

Note importantiImportant remarks

  • 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.If you plan to run analytic queries on the historical data that employs aggregates or windowing functions, creating a clustered columnstore as a primary index is highly recommended for compression and query performance.

  • Se il caso d'uso principale è il controllo dei dati (ovvero la ricerca di modifiche nella cronologia per una singola riga della tabella corrente), è consigliabile creare una tabella di cronologia rowstore con un indice clusterIf the primary use case is data audit (i.e. searching for historical changes for a single row from the current table), then a good choice is to create rowstore history table with a clustered index

  • La tabella di cronologia non può avere una chiave primaria, chiavi esterne, indici univoci, vincoli di tabella e neppure trigger.The history table cannot have a primary key, foreign keys, unique indexes, table constraints or triggers. Non può essere configurata per Change Data Capture, rilevamento delle modifiche, replica transazionale o replica di tipo merge.It cannot be configured for change data capture, change tracking, transactional or merge replication.

Modificare una tabella non temporale per trasformarla in una tabella temporale con controllo delle versioni di sistemaAlter Non-Temporal Table to be System-Versioned Temporal Table

Quando è necessario attivare il controllo delle versioni di sistema usando una tabella esistente, come quando si vuole eseguire la migrazione di una soluzione temporale personalizzata al supporto incorporato.When you need to enable system-versioning using an existing table, such as when you wish to migrate a custom temporal solution to built-in support.
Ad esempio, si potrebbe avere un set di tabelle in cui il controllo delle versioni è implementato mediante trigger.For example, you may have a set of tables where versioning is implemented with triggers. L'uso del controllo delle versioni di sistema temporale è meno complesso e offre vantaggi aggiuntivi fra cui:Using temporal system-versioning is less complex and provides additional benefits including:

  • cronologia non modificabileimmutable history

  • nuova sintassi per le query con spostamento nel temponew syntax for time-travelling queries

  • migliori prestazioni DMLbetter DML performance

  • costi di manutenzione minimiminimal maintenance costs

    Quando si converte una tabella esistente, si consiglia di usare la clausola HIDDEN per nascondere le nuove colonne PERIOD al fine di evitare conseguenze sulle applicazioni esistenti che non sono progettate per gestire nuove colonne.When converting an existing table, consider using the HIDDEN clause to hide the new PERIOD columns to avoid impacting existing applications that are not designed to handle new columns.

Aggiunta del controllo delle versioni a tabelle non temporaliAdding versioning to non-temporal tables

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:If you want to start tracking changes for a non-temporal table that contains the data, you need to add the PERIOD definition and optionally provide a name for the empty history table that SQL Server will create for you:

CREATE SCHEMA History;   
GO   
ALTER TABLE InsurancePolicy   
   ADD   
      SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN    
           CONSTRAINT DF_SysStart DEFAULT SYSUTCDATETIME()  
      , SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN    
           CONSTRAINT DF_SysEnd DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),   
      PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);   
GO   
ALTER TABLE InsurancePolicy   
   SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.InsurancePolicy))   
;  

Note importantiImportant remarks

  • L'aggiunta di colonne che non supportano 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).Adding non-nullable columns with defaults to an existing table with data is a size of data operation on all editions other than SQL Server Enterprise Edition (on which it is a metadata operation). 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.With a large existing history table with data on SQL Server Standard Edition, adding a non-null column can be an expensive operation.

  • I vincoli per le colonne di inizio e fine periodo devono essere scelti attentamente:Constraints for period start and period end columns must be carefully chosen:

    • Il valore predefinito per la colonna di inizio specifica da che punto nel tempo le righe esistenti sono considerate valide.Default for start column specifies from which point in time you consider existing rows to be valid. Non può essere specificato come valore datetime futuro.It cannot be specified as a datetime point in the future.

    • L'ora di fine deve essere specificata come valore massimo per una precisione datetime2 specificaEnd time must be specified as the maximum value for a given datetime2 precision

  • Se si aggiunge Period, verranno eseguiti controlli di coerenza sulla tabella corrente per garantire che i valori predefiniti per le colonne di period siano validi.Adding period will perform a data consistency check on the current table to make sure that the defaults for period columns are valid.

  • Se viene specificata una tabella di cronologia esistente quando si attiva SYSTEM_VERSIONING, sarà eseguito un controllo di coerenza sui dati nella tabella corrente e nella tabella di cronologia.When an existing history table is specified when enabling SYSTEM_VERSIONING, a data consistency check will be performed across both the current and the history table. Può essere ignorato se si specifica DATA_CONISTENCY_CHECK = OFF come parametro aggiuntivo.It can be skipped if you specify DATA_CONISTENCY_CHECK = OFF as an additional parameter.

Eseguire la migrazione di tabelle esistenti al supporto incorporatoMigrate existing tables to built-in support

Questo esempio mostra come migrare una soluzione esistente basata su trigger al supporto temporale incorporato.This example shows how to migrate an existing solution based on triggers to build-in temporal support. In questo esempio si presuppone che la soluzione personalizzata corrente suddivida i dati attuali e cronologici in due tabelle utente separate (ProjectTaskCurrent e ProjectTaskHistory).For this example, we assume that the current custom solution splits the current and historical data in two separate user tables (ProjectTaskCurrent and 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 in questo esempio:If your existing solution uses single table to store actual and historical rows, then you should split the data into two tables prior to the migration steps shown in this example:

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

Note importantiImportant remarks

  • Facendo riferimento a colonne esistenti nella definizione di PERIOD viene modificato implicitamente generated_always_type in AS_ROW_START e AS_ROW_END per queste colonne.Referencing existing columns in PERIOD definition implicitly changes generated_always_type to AS_ROW_START and AS_ROW_END for those columns.

  • Se si aggiunge PERIOD verrà eseguito un controllo di coerenza dei dati sulla tabella corrente per garantire che i valori esistenti per le colonne di Period siano validiAdding PERIOD will perform a data consistency check on current table to make sure that the existing values for period columns are valid

  • È consigliabile impostare SYSTEM_VERSIONING con DATA_CONSISTENCY_CHECK = ON per eseguire i controlli di coerenza dei dati sui dati esistenti.It is highly recommended to set SYSTEM_VERSIONING with DATA_CONSISTENCY_CHECK = ON to enforce data consistency checks on existing data.

Questo articolo è stato utile?Did this Article Help You? Commenti e suggerimentiWe’re Listening

Quali informazioni si stanno cercando? La ricerca ha restituito i risultati desiderati?What information are you looking for, and did you find it? Microsoft incoraggia gli utenti a inviare i propri commenti per migliorare i contenutiWe’re listening to your feedback to improve the content. Inviare eventuali commenti all'indirizzo sqlfeedback@microsoft.comPlease submit your comments to sqlfeedback@microsoft.com

Vedere ancheSee Also

Tabelle temporali Temporal Tables
Introduzione alle tabelle temporali con controllo delle versioni di sistema Getting Started with System-Versioned Temporal Tables
Gestire la conservazione dei dati cronologici nelle tabelle temporali con controllo delle versioni di sistema Manage Retention of Historical Data in System-Versioned Temporal Tables
Tabelle temporali con controllo delle versioni di sistema con tabelle con ottimizzazione per la memoria System-Versioned Temporal Tables with Memory-Optimized Tables
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
Modifica dei dati in una tabella temporale con controllo delle versioni di sistema Modifying Data in a System-Versioned Temporal Table
Query sui dati in una tabella temporale con controllo delle versioni di sistema Querying Data in a System-Versioned Temporal Table
Modifica dello schema di una tabella temporale con controllo delle versioni di sistema Changing the Schema of a System-Versioned Temporal Table
Arresto del controllo delle versioni di sistema in una tabella temporale con controllo delle versioni di sistemaStopping System-Versioning on a System-Versioned Temporal Table