Gestire la conservazione dei dati cronologici nelle tabelle temporali con controllo delle versioni di sistemaManage Retention of Historical Data in System-Versioned Temporal Tables

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

Le tabelle temporali con controllo delle versioni di sistema consentono alla tabella di cronologia di aumentare le dimensioni del database in modo superiore rispetto alle tabelle normali, in particolare se si verificano le condizioni seguenti:With system-versioned temporal tables, the history table may increase database size more than regular tables, particularly under the following conditions:

  • I dati cronologici vengono conservati per un lungo periodo di tempoYou retain historical data for a long period of time

  • Si esegue un aggiornamento o un'eliminazione con un modello di modifica dati con impatto elevatoYou have an update or delete heavy data modification pattern

    Una tabella di cronologia di grandi dimensioni e in continua crescita può costituire un problema, a causa dei semplici costi di archiviazione e dell'impatto sulle prestazioni delle query temporali.A large and ever-growing history table can become an issue both due to pure storage costs as well as imposing a performance tax on temporal querying. Lo sviluppo dei criteri di conservazione dei dati per la gestione dei dati nella tabella di cronologia è quindi un aspetto importante della pianificazione e della gestione del ciclo di vita di ogni tabella temporale.Hence, developing a data retention policy for managing data in the history table is an important aspect of planning and managing the lifecycle of every temporal table.

Gestione della conservazione dei dati per la tabella di cronologiaData retention management for history table

Per gestire la conservazione dei dati della tabella temporale, è prima di tutto necessario determinare il periodo di conservazione obbligatorio per ogni tabella temporale.Managing temporal table data retention begins with determining the required retention period for each temporal table. Nella maggior parte dei casi, i criteri di conservazione devono essere considerati come parte della logica di business dell'applicazione che usa le tabelle temporali.Your retention policy, in most cases, should be considered to be part of the business logic of the application using the temporal tables. Ad esempio, le applicazioni negli scenari di controllo dei dati e di spostamento cronologico prevedono requisiti rigorosi a livello di durata della disponibilità dei dati cronologici per le query online.For example, applications in data audit and time travel scenarios have firm requirements in terms of for how long historical data must be available for online querying.

Dopo avere determinato il periodo di conservazione dei dati, è necessario sviluppare un piano per la gestione dei dati cronologici, per la modalità e la posizione di archiviazione dei dati cronologici e per l'eliminazione dei dati cronologici precedenti ai requisiti di conservazione.Once you determine your data retention period, your next step is to develop a plan for managing historical data how and where you store your historical data and how to delete historical data that is older than your retention requirements. Sono disponibili i seguenti quattro possibili approcci per la gestione dei dati cronologici nella tabella di cronologia temporale:The following four approaches for managing historical data in the temporal history table are available:

  • Estensione databaseStretch Database

  • Partizionamento delle tabelleTable Partitioning

  • Script di pulizia personalizzatoCustom Cleanup Script

  • Criteri di conservazioneRetention Policy

    In ogni approccio la logica per la migrazione o la pulizia dei dati cronologici è basata sulla colonna che corrisponde alla fine del periodo nella tabella corrente.With each of these approaches, the logic for migrating or cleaning history data is based on the column that corresponds to end of period in the current table. Il valore relativo alla fine del periodo per ogni riga determina il momento in cui la versione della riga diventa "chiusa", ovvero quando viene inserita nella tabella di cronologia.The end of period value for each row determines the moment when the row version becomes “closed”, i.e. when it lands in the history table. Ad esempio, la condizione SysEndTime < DATEADD (DAYS, -30, SYSUTCDATETIME ()) specifica che i dati cronologici più vecchi di un mese devono essere rimossi o spostati dalla tabella di cronologia.For example, the condition SysEndTime < DATEADD (DAYS, -30, SYSUTCDATETIME ()) specifies that historical data older than one month needs to be removed or moved out from the history table.

NOTA: gli esempi in questo argomento usano questo esempio di tabella temporale.NOTE: The examples in this topic use this Temporal Table example.

Uso dell'approccio con Estensione databaseUsing Stretch Database approach

NOTA: l'approccio con Estensione database si applica solo a SQL Server 2017SQL Server 2017 e non a Database SQLSQL Database.NOTE: Using the Stretch Database approach only applies to SQL Server 2017SQL Server 2017 and does not apply to Database SQLSQL Database.

Estensione database in SQL Server 2017SQL Server 2017 esegue la migrazione dei dati cronologici in modo trasparente in Azure.Stretch Database in SQL Server 2017SQL Server 2017 migrates your historical data transparently to Azure. Per una maggiore sicurezza, è possibile crittografare i dati in transito usando la funzionalità Crittografia sempre attiva di SQL Server.For additional security, you can encrypt data in motion using SQL Server's Always Encrypted feature. È anche possibile usare la sicurezza a livello di riga e altre funzionalità avanzate per la sicurezza di SQL Server con Estensione database e un database temporale per proteggere i dati.Additionally, you can use Row-Level Security and other advanced SQL Server security features with Temporal and Stretch Database to protect your data.

L'approccio con Estensione database consente di estendere alcune o tutte le tabelle di cronologia temporali in Azure e SQL Server sposterà automaticamente i dati cronologici in Azure.Using the Stretch Database approach, you can stretch some or all of your temporal history tables to Azure and SQL Server will silently move historical data to Azure. L'abilitazione di una tabella di cronologia per l'estensione non modifica la modalità di interazione con la tabella temporale a livello di modifica dei dati e di query temporali.Stretch-enabling a history table does not change how you interact with the temporal table in terms of data modification and temporal querying.

  • Estensione dell'intera tabella di cronologia: configurare Estensione database per l'intera tabella di cronologia se lo scenario principale è costituito dal controllo dei dati in un ambiente con modifiche frequenti dei dati e query relativamente rare sui dati cronologici.Stretch the entire history table: Configure Stretch Database for your entire history table if your main scenario is data audit in the environment with frequent data changes and relatively rare querying on historical data. In altri termini, usare questo approccio se le prestazioni delle query temporali non sono essenziali.In other words, use this approach if performance of temporal querying is not critical. In questo caso, Azure potrebbe risultare economicamente conveniente.In this case, the cost-effectiveness provided by Azure may be compelling.
    Quando si estende l'intera tabella di cronologia, è possibile usare la procedura guidata per l'estensione o Transact-SQL.When stretching the entire history table, you can either use the Stretch Wizard or Transact-SQL. Ecco un esempio di entrambi.Examples of both appear below.

  • Estensione di una parte della tabella di cronologia: configurare Estensione database solo per una parte della tabella di cronologia per migliorare le prestazioni se lo scenario principale prevede principalmente l'esecuzione di query sui dati cronologici recenti, ma si vuole mantenere l'opzione per l'esecuzione di query su dati cronologici precedenti se necessario, archiviando al tempo stesso questi dati in modalità remota a un costo inferiore.Stretch a portion of the history table: Configure Stretch Database for only a portion of your history table to improve performance if your main scenario involves primarily querying recent historical data, but you wish to preserve the option to query older historical data when needed while storing this data remotely at a lower cost. Transact-SQL consente di ottenere questo risultato specificando una funzione di predicato per selezionare le righe di cui verrà eseguita la migrazione dalla tabella di cronologia, invece di eseguire la migrazione di tutte le righe.With Transact-SQL, you can accomplish this by specifying a predicate function to select the rows that will be migrated from the history table rather than migrating all of the rows. Quando si utilizzano le tabelle temporali, è in genere consigliabile spostare i dati in base alla condizione temporale, ovvero in base all'età della versione della riga nella tabella di cronologia.When you work with temporal tables, it typically makes sense to move data based on time condition (i.e. based on age of the row version in the history table).
    L'uso di una funzione di predicato deterministica consente di mantenere una parte della cronologia nello stesso database con i dati correnti, mentre viene eseguita la migrazione del resto della cronologia in Azure.Using a deterministic predicate function, you can keep portion of history in the same database with the current data, while the rest is migrated to Azure.
    Per esempi e limitazioni, vedere Selezionare le righe di cui eseguire la migrazione tramite una funzione di filtro (Estensione database).For examples and limitations, see Select rows to migrate by using a filter function (Stretch Database). Poiché le funzioni non deterministiche non sono valide, per trasferire i dati cronologici usando una finestra temporale scorrevole è necessario modificare in modo regolare la definizione della funzione di predicato inline, in modo che la finestra di righe mantenuta in locale sia costante a livello di età.Because non-deterministic functions are not valid, if you want to transfer history data in sliding window manner, you would need to regularly alter definition of the inline predicate function so that window of rows you keep locally is constant in terms of age. La finestra temporale scorrevole consente di spostare in modo costante i dati temporali più vecchi di un mese in Azure.Sliding window allows you to constantly move historical data older than one month to Azure. Ecco un esempio di questo approccio.An example of this approach appears below.

NOTA: Estensione database esegue la migrazione dei dati in Azure.NOTE: Stretch Database migrates data to Azure. È quindi necessario avere un account Azure e una sottoscrizione per la fatturazione.Therefore, you have to have an Azure account and a subscription for billing. Per ottenere un account di valutazione gratuito di Azure, fare clic sulla versione di valutazione gratuita di un mese.To get a free trial Azure account, click Free One-Month Trial.

È possibile configurare una tabella di cronologia temporale per l'estensione usando la procedura guidata per l'estensione o Transact-SQL ed è possibile abilitare una tabella di cronologia temporale per l'estensione se il controllo delle versioni di sistema è attivato.You can configure a temporal history table for Stretch using either the Stretch Wizard or Transact-SQL, and you can stretch-enable a temporal history table while system-versioning is set to ON. L'estensione della tabella corrente non è consentita perché si tratta di un'operazione superflua.Stretching the current table is not allowed because it does not make sense to stretch the current table.

Uso della procedura guidata per l'estensione per estendere l'intera tabella di cronologiaUsing the Stretch Wizard to stretch the entire history table

Il metodo più semplice per i principianti consiste nell'usare la procedura guidata per l'estensione per abilitare l'estensione per l'intero database e quindi selezionare la tabella di cronologia temporale all'interno della procedura guidata per l'estensione. Questo esempio presuppone che la tabella Department sia stata configurata come tabella temporale con controllo delle versioni di sistema in un database altrimenti vuoto.The easiest method for beginners is to use the Stretch Wizard to enable stretch for the entire database and then select the temporal history table within the Stretch wizard (this example assumes that you have configured the Department table as a system-versioned temporal table in an otherwise empty database). In SQL Server 2016SQL Server 2016non è possibile fare clic con il pulsante destro del mouse sulla tabella di cronologia temporale stessa e scegliere Estendi.In SQL Server 2016SQL Server 2016, you cannot right-click the temporal history table itself and click Stretch.

  1. Fare clic con il pulsante destro del mouse sul database e scegliere Attività, quindi Estendie infine **** per avviare la procedura guidata.Right-click your database and point to Tasks, point to Stretch, and then click Enable to launch the wizard.

  2. Nella finestra Selezionare le tabelle selezionare la casella di controllo della tabella di cronologia temporale e quindi fare clic su Avanti.In the Select tables window, select the checkbox for the temporal history table and click Next.

    Selezione della tabella di cronologia nella pagina Selezione tabelleSelecting the history table on the Select tables page

  3. Nella finestra Configura Azure specificare le proprie credenziali di accesso.In the Configure Azure window, provide your login credentials. Accedere a Microsoft Azure o iscriversi per ottenere un account.Sign in to Microsoft Azure or sign-up for an account. Selezionare la sottoscrizione da usare e l'area di Azure.Select the subscription to use, select the Azure region. Creare quindi un nuovo server o selezionare un server esistente.Then either create a new server or select an existing server. Fare clic su Avanti.Click Next.

    Creare un nuovo server di Azure - Procedura guidata Estensione databaseCreate new Azure server - Stretch Database wizard

  4. Nella finestra Credenziali protette specificare una password per la chiave master del database per proteggere le credenziali del database SQL Server di origine e quindi fare clic su Avanti.In the Secure credentials window, provide a password for the database master key to secure your source SQL Server database credential and click Next.

    Pagina Credenziali protette della procedura guidata Estensione databaseSecure credentials page of the Stretch Database wizard

  5. Nella finestra Selezionare l'indirizzo IP specificare l'intervallo di indirizzi IP per SQL Server per consentire al server di Azure di comunicare con SQL Server. Se si seleziona un server esistente per cui esiste già una regola del firewall, è qui sufficiente fare clic su Avanti per usare tale regola.In the Select IP address window, provide the IP address range for your SQL Server to let your Azure server communicate with your SQL Server (if you select an existing server for which a firewall rule already exists, simply click Next here to use the existing firewall rule). Fare clic su Avanti e quindi su Fine per abilitare Estensione database ed estendere la tabella di cronologia temporale.Click Next and then click Finish to enable Stretch Database and stretch the temporal history table.

    Pagina Selezionare l'indirizzo IP della procedura guidata Estensione databaseSelect IP address page of the Stretch Database wizard

  6. Al termine della procedura guidata, verificare che il database sia stato abilitato per l'estensione.When the wizard completes, verify that your database was successfully stretch-enabled. Notare le icone in Esplora oggetti che indicano che il database è stato esteso.Notice the icons in Object Explorer indicating the database was stretched

NOTA: se l'abilitazione del database per l'estensione non riesce, esaminare il log degli errori.NOTE: If the Enable Database for Stretch fails, review the error log. Un errore comune consiste nella configurazione non corretta della regola del firewall.A common error is improperly configuring the firewall rule.

Vedere anche:See also:

Uso di Transact-SQL per estendere l'intera tabella di cronologiaUsing Transact-SQL to stretch the entire history table

È anche possibile usare Transact-SQL per abilitare l'estensione sul server locale e abilitare Estensione database per un database.You can also use Transact-SQL to enable Stretch on the local server and Enable Stretch Database for a database. È quindi possibile usare Transact-SQL per abilitare Estensione database in una tabella.You can then use Transact-SQL to enable Stretch Database on a table. Con un database già abilitato per l'estensione, eseguire lo script di Transact-SQL seguente per estendere una tabella di cronologia temporale con controllo delle versioni di sistema esistente:With a database previously enabled for Stretch Database, execute the following Transact-SQL script to stretch an existing system-versioned temporal history table:

ALTER TABLE <history table name>   
SET (REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OUTBOUND));  

Uso di Transact-SQL per estendere una parte della tabella di cronologiaUsing Transact-SQL to stretch a portion of the history table

Per estendere solo una parte della tabella di cronologia, creare prima di tutto una funzione di predicato inline.To stretch only a portion of the history table, you start by creating an inline predicate function. Per questo esempio si presupponga di aver configurato la funzione di predicato inline per la prima volta il 1° dicembre 2015 e di voler estendere in Azure tutta la cronologia precedente al 1° novembre 2015.For this example, let’s assume that you configured inline predicate function for the first time on December 1, 2015 and want to stretch to Azure all history date older than November 1, 2015. Per ottenere questo risultato, creare prima di tutto la funzione seguente:To accomplish this, start by creating the following function:

CREATE FUNCTION dbo.fn_StretchBySystemEndTime20151101(@systemEndTime datetime2)   
RETURNS TABLE   
WITH SCHEMABINDING    
AS    
RETURN SELECT 1 AS is_eligible   
  WHERE @systemEndTime < CONVERT(datetime2, '2015-11-01T00:00:00', 101) ;  

Usare quindi lo script seguente per aggiungere il predicato di filtro alla tabella di cronologia e impostare lo stato della migrazione su OUTBOUND per abilitare la migrazione dei dati basata sui predicati per la tabella di cronologia.Next, use the following script to add the filter predicate to the history table and set the migration state to OUTBOUND to enable predicate based data migration for the history table.

ALTER TABLE <history table name>   
SET (   
        REMOTE_DATA_ARCHIVE = ON   
                (   
                        FILTER_PREDICATE = dbo.fn_StretchBySystemEndTime20151101 (SysEndTime)  
                                , MIGRATION_STATE = OUTBOUND   
                )  
        )   
;  

Per mantenere una finestra temporale scorrevole, è necessario che la funzione di predicato sia precisa ogni giorno, ovvero che la condizione della riga di filtro venga modificata di un giorno tutti i giorni.To maintain a sliding window, you need to make predicate function to be accurate every day (i.e. change filtering row condition every day by one day). Lo script seguente è lo script da eseguire il 2 dicembre 2015:The following script is the script that you would you need to execute on December 2, 2015:

BEGIN TRAN  
           /*(1) Create new predicate function definition */  
        CREATE FUNCTION dbo.fn_StretchBySystemEndTime20151102(@systemEndTime datetime2)  
        RETURNS TABLE  
        WITH SCHEMABINDING   
        AS   
        RETURN SELECT 1 AS is_eligible  
               WHERE @systemEndTime < CONVERT(datetime2,'2015-11-02T00:00:00', 101)  
        GO  

        /*(2) Set the new function as filter predicate */  
        ALTER TABLE <history table name>  
        SET   
        (  
               REMOTE_DATA_ARCHIVE = ON  
               (  
                       FILTER_PREDICATE = dbo.fn_StretchBySystemEndTime20151102(SysEndTime),  
                       MIGRATION_STATE = OUTBOUND  
               )  
        )   
COMMIT ;  

Usare SQL Server Agent o un altro meccanismo di pianificazione per assicurare una definizione valida per la funzione di predicato in ogni momento.Use SQL Server Agent or some other scheduling mechanism to ensure valid predicate function definition all the time.

Uso dell'approccio con partizionamento delle tabelleUsing Table Partitioning Approach

Ilpartizionamento delle tabelle può semplificare la gestione e il ridimensionamento di tabelle di grandi dimensioni.Table partitioning can make large tables more manageable and scalable. L'approccio con partizionamento delle tabelle consente di usare le partizioni delle tabelle di cronologia per implementare la pulizia dei dati personalizzata o l'archiviazione offline in base a una condizione temporale.Using the table partitioning approach, you can use history table partitions to implement custom data cleanup or offline archival based on a time condition. Il partizionamento delle tabelle offrirà anche vantaggi a livello di prestazioni in caso di query su tabelle temporali relative a un subset di cronologia dei dati mediante l'eliminazione delle partizioni.Table partitioning will also give you performance benefits when querying temporal tables on a subset of data history by using partition elimination.

Il partizionamento delle tabelle consente di implementare un approccio con finestra temporale scorrevole per spostare le parti più vecchie dalla tabella di cronologia e mantenere costanti le dimensioni della parte conservata in termini di età, mantenendo i dati della tabella di cronologia uguali al periodo di conservazione necessario.With table partitioning, you can implement a sliding window approach to move out oldest portion of the historical data from the history table and keep the size of the retained part constant in terms of age - maintaining data in the history table equal to required retention period. L'operazione di disattivazione dei dati dalla tabella di cronologia è supportata se SYSTEM_VERSIONING è ON, ovvero è possibile pulire una parte dei dati di cronologia senza introdurre una finestra di manutenzione o bloccare i carichi di lavoro normali.The operation of switching data out from the history table is supported while SYSTEM_VERSIONING is ON, which means that you can clean a portion of the history data without introducing a maintenance windows or blocking your regular workloads.

NOTA: per eseguire il cambio di partizione, è necessario che l'indice cluster nella tabella di cronologia sia allineato allo schema di partizionamento, ovvero che contenga SysEndTime.NOTE: In order to perform partition switching, your clustered index on history table must be aligned with the partitioning schema (it has to contain SysEndTime). La tabella di cronologia predefinita creata dal sistema contiene un indice cluster che include le colonne SysEndTime e SysStartTime, ottimali per il partizionamento, l'inserimento di nuovi dati di cronologia e le query temporali tipiche.The default history table created by the system contains a clustered index that includes the SysEndTime and SysStartTime columns, which is optimal for partitioning, inserting new history data, and typical temporal querying. Per altre informazioni, vedere Temporal Tables.For more information, see Temporal Tables.

In un approccio con finestra temporale scorrevole è necessario eseguire due set di attività:A sliding window approach has two sets of tasks that you need to perform:

  • Attività di configurazione del partizionamentoA partitioning configuration task

  • Attività di manutenzione ricorrenti della partizioneRecurring partition maintenance tasks

    Si supponga, ad esempio, che si vogliano conservare i dati cronologici per 6 mesi e inserire i dati di ogni mese in una partizione separata.For the illustration, let’s assume that we want to keep historical data for 6 months and that we want to keep every month of data in a separate partition. Si presupponga anche di avere attivato il controllo delle versioni di sistema nel mese di settembre 2015.Also, let’s assume that we activated system-versioning in September of 2015.

    Un'attività di configurazione del partizionamento crea la configurazione iniziale del partizionamento per la tabella di cronologia.A partitioning configuration task creates the initial partitioning configuration for the history table. Per questo esempio viene creato un numero di partizioni corrispondente alle dimensioni della finestra temporale scorrevole, in mesi, oltre a una partizione aggiuntiva vuota già preparata, come illustrato di seguito.For this example, we would create the same number partitions as the size of sliding window, in months, plus one additional empty partition pre-prepared (explained below). Questa configurazione assicura che il sistema sarà in grado di archiviare correttamente nuovi dati quando viene avviata per la prima volta l'attività ricorrente di manutenzione della partizione e assicura che le partizioni non verranno mai suddivise con i dati, per evitare spostamenti costosi dei dati.This configuration ensures that the system will be able to store new data correctly when we start the recurring partition maintenance task for the first time and guarantees that we never split partitions with data to avoid expensive data movements. È consigliabile eseguire questa attività usando Transact-SQL con lo script di esempio seguente.You should perform this task using Transact-SQL using the example script below.

    La figura seguente illustra la configurazione iniziale del partizionamento per la conservazione di 6 mesi di dati.The following picture shows initial partitioning configuration to keep 6 months of data.

    PartizionamentoPartitioning

NOTA: vedere la sezione Considerazioni sulle prestazioni con il partizionamento delle tabelle più avanti per informazioni sulle conseguenze dell'uso di RANGE LEFT invece di RANGE RIGHT sulle prestazioni durante la configurazione del partizionamento.NOTE: See Performance considerations with table partitioning below for the performance implications of using RANGE LEFT versus RANGE RIGHT when configuring partitioning.

Si noti che la prima e l'ultima partizione sono "aperte" sul limite inferiore e superiore, rispettivamente, per assicurare che ogni nuova riga abbia una partizione di destinazione, indipendentemente dal valore della colonna di partizionamento.Note that first and last partition are “open” on lower and upper boundaries respectively to ensure that every new row has destination partition regardless of the value in partitioning column.
Con il passare del tempo, le nuove righe della tabella di cronologia verranno inserite in partizioni superiori.As time goes by, new rows in history table will land in higher partitions. Quando la sesta partizione viene riempita, si raggiunge il limite del periodo di conservazione specificato.When 6th partition gets filled up, we will have reached the targeted retention period. Questo è il momento in cui avviare per la prima volta l'attività ricorrente di manutenzione della partizione. Questa attività deve essere pianificata per l'esecuzione periodica, una volta al mese in questo esempio.This is the moment to start the recurring partition maintenance task for the first time (it needs to be scheduled to run periodically, once per month in this example).

La figura seguente illustra le attività ricorrenti di manutenzione della partizione. Vedere la procedura dettagliata più avanti.The following picture illustrates the recurring partition maintenance tasks (see detailed steps below).

Partizionamento2Partitioning2

Ecco la procedura dettagliata per le attività ricorrenti di manutenzione della partizione:The detailed steps for the recurring partition maintenance tasks are:

  1. SWITCH OUT: creare una tabella di gestione temporanea e quindi cambiare una partizione tra la tabella di cronologia e la tabella di gestione temporanea usando l'istruzione ALTER TABLE (Transact-SQL) con l'argomento SWITCH PARTITION. Vedere l'esempio C relativo al cambio di partizioni tra tabelle.SWITCH OUT: Create a staging table and then switch a partition between the history table and the staging table using the ALTER TABLE (Transact-SQL) statement with the SWITCH PARTITION argument (see Example C. Switching partitions between tables).

    ALTER TABLE <history table> SWITCH PARTITION 1 TO <staging table>  
    

    Dopo il cambio di partizione, è possibile archiviare facoltativamente i dati dalla tabella di gestione temporanea e quindi eliminare o troncare la tabella di gestione temporanea in modo da essere pronti per quando sarà necessario eseguire di nuovo questa attività ricorrente di manutenzione della partizione.After the partition switch, you can optionally archive the data from staging table and then either drop or truncate the staging table to be ready for the next time you need to perform this recurring partition maintenance task.

  2. MERGE RANGE: unire la partizione 1 vuota con la partizione 2 usando l'istruzione ALTER PARTITION FUNCTION (Transact-SQL) con MERGE RANGE. Vedere l'esempio B.MERGE RANGE: Merge the empty partition 1 with partition 2 using the ALTER PARTITION FUNCTION (Transact-SQL) with MERGE RANGE (See example B). Rimuovendo il limite inferiore mediante questa funzione, si unisce effettivamente la partizione 1 vuota con la partizione 2 precedente per formare una nuova partizione 1.By removing the lowest boundary using this function, you effectively merge the empty partition 1 with the former partition 2 to form new partition 1. Vengono modificati anche i numeri ordinali relativi alle altre partizioni.The other partitions also effectively change their ordinals.

  3. SPLIT RANGE: creare una nuova partizione 7 vuota usando l'istruzione ALTER PARTITION FUNCTION (Transact-SQL) con SPLIT RANGE. Vedere l'esempio A.SPLIT RANGE: Create a new empty partition 7 using the ALTER PARTITION FUNCTION (Transact-SQL) with SPLIT RANGE (See example A). Aggiungendo un nuovo limite superiore mediante questa funzione, si crea effettivamente una partizione separata per il mese successivo.By adding a new upper boundary using this function, you effectively create a separate partition for the upcoming month.

Usare Transact-SQL per creare partizioni nella tabella di cronologiaUse Transact-SQL to create partitions on history table

Usare lo script di Transact-SQL nella finestra di codice seguente per creare la funzione di partizione e ricreare l'indice cluster in modo che sia allineato a livello di partizioni con lo schema delle partizioni e le partizioni.Use the Transact-SQL script in the code window below to create the partition function, the partition schema, and recreate the clustered index to be partition-aligned with the partition schema, partitions. Per questo esempio verrà creato un approccio con finestra temporale scorrevole di sei mesi con partizioni mensili a partire dal mese di settembre 2015.For this example, we will creating a six-month sliding window approach with monthly partitions beginning September, 2015.

BEGIN TRANSACTION  

        /*Create partition function*/  
        CREATE PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_SysEndTime] (datetime2(7))   
                    AS RANGE LEFT FOR VALUES   
                                (N'2015-09-30T23:59:59.999'  
                                , N'2015-10-31T23:59:59.999'  
                                , N'2015-11-30T23:59:59.999'  
                                , N'2015-12-31T23:59:59.999'  
                                , N'2016-01-31T23:59:59.999'  
                                , N'2016-02-29T23:59:59.999')  

        /*Create partition scheme*/  
        CREATE PARTITION SCHEME [sch_Partition_DepartmentHistory_By_SysEndTime]   
                        AS PARTITION [fn_Partition_DepartmentHistory_By_SysEndTime]   
                        TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])  

        /*Re-create index to be partition-aligned with the partitioning schema*/  
        CREATE CLUSTERED INDEX [ix_DepartmentHistory] ON [dbo].[DepartmentHistory]  
        (  
                    [SysEndTime] ASC,  
                    [SysStartTime] ASC  
        )  
            WITH   
                        (PAD_INDEX = OFF  
                        , STATISTICS_NORECOMPUTE = OFF  
                        , SORT_IN_TEMPDB = OFF  
                        , DROP_EXISTING = ON  
                        , ONLINE = OFF  
                        , ALLOW_ROW_LOCKS = ON  
                        , ALLOW_PAGE_LOCKS = ON  
                        , DATA_COMPRESSION = PAGE)  
            ON [sch_Partition_DepartmentHistory_By_SysEndTime] ([SysEndTime])  

COMMIT TRANSACTION;  

Uso di Transact-SQL per la manutenzione di partizioni in uno scenario con finestra temporale scorrevoleUsing Transact-SQL to maintain partitions in sliding window scenario

Usare lo script di Transact-SQL nella finestra di codice seguente per la manutenzione delle partizioni nello scenario con finestra temporale scorrevole.Use the Transact-SQL script in the code window below to maintain partitions in the sliding window scenario. Per questo esempio verrà disattivata la partizione per il mese di settembre 2015 mediante MERGE RANGE e quindi verrà aggiunta una nuova partizione per il mese di marzo 2016 mediante SPLIT RANGE.For this example, we will switch out the partition for September of 2015 using MERGE RANGE and then add a new partition for March of 2016 using SPLIT RANGE.

BEGIN TRANSACTION  

         /*(1)  Create staging table */  
         CREATE TABLE [dbo].[staging_DepartmentHistory_September_2015]  
        (  
                 [DeptID] [int] NOT NULL  
                 , [DeptName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  
                 , [ManagerID] [int] NULL  
                 ,  [ParentDeptID] [int] NULL  
                 ,  [SysStartTime] [datetime2](7) NOT NULL  
                 ,  [SysEndTime] [datetime2](7) NOT NULL  
         ) ON [PRIMARY]  
         WITH  
         (  
              DATA_COMPRESSION = PAGE  
         )  

         /*(2) Create index on the same filegroups as the partition that will be switched out*/  
         CREATE CLUSTERED INDEX [ox_staging_DepartmentHistory_September_2015]    
         ON [dbo].[staging_DepartmentHistory_September_2015]  
         (  
                  [SysEndTime] ASC,  
                  [SysStartTime] ASC  
         )  
      WITH   
          (  
               PAD_INDEX = OFF  
               , SORT_IN_TEMPDB = OFF  
               , DROP_EXISTING = OFF  
               , ONLINE = OFF  
               , ALLOW_ROW_LOCKS = ON  
               , ALLOW_PAGE_LOCKS = ON  
          )   
         ON [PRIMARY]  

         /*(3) Create constraints matching the partition that will be switched out*/  
         ALTER TABLE [dbo].[staging_DepartmentHistory_September_2015]  WITH CHECK   
               ADD  CONSTRAINT [chk_staging_DepartmentHistory_September_2015_partition_1]   
                    CHECK  ([SysEndTime]<=N'2015-09-30T23:59:59.999')  
         ALTER TABLE [dbo].[staging_DepartmentHistory_September_2015]   
               CHECK CONSTRAINT [chk_staging_DepartmentHistory_September_2015_partition_1]  

         /*(4) Switch partition to staging table*/  
         ALTER TABLE [dbo].[DepartmentHistory]   
         SWITCH PARTITION 1 TO [dbo].[staging_DepartmentHistory_September_2015]   
         WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE))  

         /*(5) [Commented out] Optionally archive the data and drop staging table  
         INSERT INTO [ArchiveDB].[dbo].[DepartmentHistory]   
         SELECT * FROM [dbo].[staging_DepartmentHistory_September_2015];  
         DROP TABLE [dbo].[staging_DepartmentHIstory_September_2015];  
         */  

         /*(6) merge range to move lower boundary one month ahead*/  
         ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_SysEndTime]()   
               MERGE RANGE(N'2015-09-30T23:59:59.999')  

         /*(7) Create new empty partition for "April and after" by creating new boundary point and specifying NEXT USED file group*/  
         ALTER PARTITION SCHEME [sch_Partition_DepartmentHistory_By_SysEndTime] NEXT USED [PRIMARY]  
         ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_SysEndTime]() SPLIT RANGE(N'2016-03-31T23:59:59.999')  

COMMIT TRANSACTION  

È possibile modificare leggermente lo script precedente e usarlo nel processo di manutenzione mensile regolare:You can slightly modify script above and use it in regular monthly maintenance process:

  1. Nel passaggio (1) creare una nuova tabella di gestione temporanea per il mese da rimuovere. Il mese successivo nell'esempio è ottobre.In step (1) create new staging table for the month you want to remove (October would be next one in our example)

  2. Nel passaggio (3) creare e controllare il vincolo corrispondente al mese di dati da rimuovere: [SysEndTime]<=N'2015-10-31T23:59:59.999' per la partizione di ottobre.In step (3) create and check constraint that matches the month of data you want to remove: [SysEndTime]<=N'2015-10-31T23:59:59.999' for October partition

  3. Nel passaggio (4) eseguire l'istruzione SWITCH per la partizione 1 nella tabella di gestione temporanea appena creata.In step (4) SWITCH partition 1 to newly created staging table

  4. Nel passaggio (6) modificare la funzione di partizione unendo il limite inferiore: MERGE RANGE(N'2015-10-31T23:59:59.999' dopo la rimozione dei dati per ottobre.In step (6) alter partition function by merging lower boundary: MERGE RANGE(N'2015-10-31T23:59:59.999' after you moved out data for October

  5. Nel passaggio (7) suddividere la funzione di partizione creando un nuovo limite superiore: SPLIT RANGE (N'2016-04-30T23:59:59.999' dopo la rimozione dei dati per ottobre.In step (7) split partition function creating new upper boundary: SPLIT RANGE (N'2016-04-30T23:59:59.999' after you moved out data for October.

    La soluzione ottimale consiste tuttavia nell'eseguire uno script di Transact-SQL generico, in grado di eseguire l'azione appropriata ogni mese, senza modifiche allo script.However, the optimal solution would be to regularly run a generic Transact-SQL script that is a capable of performing the appropriate action every month without script modification. È possibile generalizzare lo script precedente in modo che reagisca ai parametri specificati, ovvero un limite inferiore da unire e un nuovo limite che verrà creato con la suddivisione della partizione.It is possible to generalize the script above to act upon provided parameters (lower boundary that needs to be merged and new boundary that will be created by with partition split). Per evitare di creare ogni mese una tabella di gestione temporanea, è possibile crearne una prima e riutilizzarla cambiando il vincolo di verifica in modo che corrisponda alla partizione che verrà disattivata.In order to avoid staging table creation every month, you can create one beforehand and reuse by changing check constraint to match partition that will be switched out. Per informazioni su come automatizzare completamente la finestra temporale scorrevole usando uno script di Transact-SQL, vedere le pagine seguenti.Take a look at the following pages to get ideas on how sliding window can be fully automated using a Transact-SQL script.

Considerazioni sulle prestazioni con il partizionamento delle tabellePerformance considerations with table partitioning

È importante eseguire le operazioni MERGE e SPLIT RANGE per evitare qualsiasi spostamento di dati, perché lo spostamento di dati può provocare un overhead significativo delle prestazioni.It is important to perform the MERGE and SPLIT RANGE operations to avoid any data movement as data movement can incur significant performance overhead. Per altre informazioni, vedere Modificare una funzione di partizione. Per ottenere questo risultato, usare RANGE LEFT invece di RANGE RIGHT quando si esegue l'istruzione CREATE PARTITION FUNCTION (Transact-SQL).For more information, see Modify a Partition Function.You accomplish this by using RANGE LEFT rather than RANGE RIGHT when you CREATE PARTITION FUNCTION (Transact-SQL).

Ecco prima di tutto una spiegazione visiva del significato delle opzioni RANGE LEFT e RANGE RIGHT:Let’s first visually explain meaning of the RANGE LEFT and RANGE RIGHT options:

Partizionamento3Partitioning3

Quando si definisce una funzione di partizione come RANGE LEFT, i valori specificati corrispondono ai limiti superiori delle partizioni.When you define a partition function as RANGE LEFT, the specified values are the upper boundaries of the partitions. Quando si usa RANGE RIGHT, i valori specificati sono i limiti inferiori delle partizioni.When you use RANGE RIGHT, the specified values are the lower boundaries of the partitions. Quando si usa l'operazione MERGE RANGE per rimuovere un limite dalla definizione della funzione di partizione, l'implementazione sottostante rimuove anche la partizione che contiene il limite.When you use the MERGE RANGE operation to remove a boundary from the partition function definition, the underlying implementation also removes the partition which contains the boundary. Se tale partizione non è vuota, i dati verranno spostati nella partizione che è il risultato dell'operazione MERGE RANGE.If that partition is not empty, data will be moved to the partition that is result of MERGE RANGE operation.

In uno scenario con finestra temporale scorrevole, si rimuove sempre il limite inferiore della partizione.In sliding window scenario, we always remove lowest partition boundary.

  • Caso RANGE LEFT: nel caso di RANGE LEFT, il limite inferiore della partizione appartiene alla partizione 1, che è vuota, dopo il cambio di partizioni, quindi MERGE RANGE non provocherà alcuno spostamento di dati.RANGE LEFT case: In RANGE LEFT case, the lowest partition boundary belongs to partition 1, which is empty (after partition switch out), so MERGE RANGE won’t incur any data movement.

  • Caso RANGE RIGHT: nel caso di RANGE RIGHT, il limite inferiore della partizione appartiene alla partizione 2, che non è vuota perché è stato presupposto che la partizione 1 sia stata vuotata dalla disattivazione.RANGE RIGHT case: In RANGE RIGHT case, the lowest partition boundary belongs to partition 2, which is not empty as we assumed that partition 1 was emptied by switch out. In questo caso MERGE RANGE provocherà lo spostamento di dati, perché i dati dalla partizione 2 verranno spostati nella partizione 1.In this case MERGE RANGE will incur data movement (data from partition 2 will be moved to partition 1). Per evitare questo problema, è necessario che RANGE RIGHT nello scenario con finestra temporale scorrevole abbia la partizione 1, che è sempre vuota.To avoid this, RANGE RIGHT in the sliding window scenario needs to have partition 1, which is always empty. Se si usa RANGE RIGHT, è quindi necessario creare e mantenere una partizione aggiuntiva rispetto al caso di RANGE LEFT.This means that if we use RANGE RIGHT, we should create and maintain one additional partition compared to RANGE LEFT case.

    Conclusione: l'uso di RANGE LEFT nella partizione con finestra temporale scorrevole è molto più semplice per la gestione delle partizioni e consente di evitare lo spostamento dei dati.Conclusion: Using RANGE LEFT in sliding partition is much simpler for the partition management and avoids data movement. La definizione dei limiti delle partizioni con RANGE RIGHT risulta tuttavia leggermente più semplice, perché non è necessario gestire i problemi di data/ora di tipo "time tick".However, defining partition boundaries with RANGE RIGHT is slightly simpler as you don't have to deal with datetime time tick issues.

Uso dell'approccio con script di pulizia personalizzatoUsing Custom Cleanup Script Approach

Nei casi in cui gli approcci con Estensione database e con partizionamento delle tabelle non sono opzioni valide, il terzo approccio consiste nell'eliminare i dati dalla tabella di cronologia usando lo script di pulizia personalizzato.In cases when the Stretch Database and table partitioning approached are not viable options, the third approach is to delete the data from history table using the custom cleanup script. L'eliminazione dei dati dalla tabella di cronologia è possibile solo se SYSTEM_VERSIONING = OFF.Deleting data from history table is possible only when SYSTEM_VERSIONING = OFF. Per evitare l'incoerenza dei dati, eseguire una pulizia durante la finestra di manutenzione, quando i carichi di lavoro che modificano i dati non sono attivi, oppure entro una transazione, bloccando effettivamente altri carichi di lavoro.In order to avoid data inconsistency, perform cleanup either during the maintenance window (when workloads that modify data are not active) or within a transaction (effectively blocking other workloads). Questa operazione richiede l'autorizzazione CONTROL sulla tabella corrente e sulla tabella di cronologia.This operation requires CONTROL permission on current and history tables.

Per ridurre al minimo il blocco delle applicazioni normali e delle query utente, eliminare i dati in blocchi ridotti, con un ritardo durante l'esecuzione dello script di pulizia all'interno di una transazione.To minimally block regular applications and user queries, delete data in smaller chunks with a delay when performing the cleanup script inside a transaction. Anche se non esistono dimensioni ottimali per ogni blocco di dati da eliminare per tutti gli scenari, l'eliminazione di più di 10.000 righe in una singola transazione potrebbe avere un impatto significativo.While there is no optimal size of for each data chunk to be deleted for all scenarios, deleting more than 10,000 rows in a single transaction may impose a significant impact.

La logica di pulizia è uguale per ogni tabella temporale, quindi è possibile automatizzare la pulizia in modo relativamente semplice tramite una stored procedure generica pianificata per l'esecuzione periodica per ogni tabella temporale di cui si vuole limitare la cronologia dei dati.The cleanup logic is the same for every temporal table, so it can be automated relatively easily through a generic stored procedure that you schedule to run periodically for every temporal table for which you want to limit data history.

Il diagramma seguente illustra come organizzare la logica di pulizia per una singola tabella, in modo da ridurre l'impatto sui carichi di lavoro in esecuzione.The following diagram illustrates how your cleanup logic should be organized for a single table to reduce impact on the running workloads.

CustomCleanUpScriptDiagramCustomCleanUpScriptDiagram

Ecco alcune indicazioni generali per l'implementazione del processo.Here are some high-level guidelines for implementing the process. Pianificare la logica di pulizia in modo che venga eseguita ogni giorno ed eseguire l'iterazione su tutte le tabelle temporali che necessitano della pulizia dei dati.Schedule cleanup logic to run every day and iterate over all temporal tables that need data cleanup. Usare SQL Server Agent o uno strumento diverso per pianificare questo processo:Use SQL Server Agent or different tool to schedule this process:

  • Eliminare i dati cronologici in ogni tabella temporale a partire dalle righe più vecchie fino alle righe più recenti in diverse iterazioni in piccoli blocchi ed evitare di eliminare tutte le righe in una singola transazione, come illustrato nella figura precedente.Delete historical data in every temporal table starting from the oldest to the most recent rows in several iterations in small chunks and avoid deleting all rows in a single transaction as shown on picture above.

  • Implementare ogni iterazione come una chiamata della stored procedure generica che rimuove una parte di dati dalla tabella di cronologia. Per informazioni su questa procedura, vedere l'esempio di codice seguente.Implement every iteration as an invocation of generic stored procedure that removes a portion of data from the history table (see code example below for this procedure).

  • Calcolare il numero di righe da eliminare per una singola tabella temporale ogni volta che si chiama il processo.Calculate how many rows you need to delete for an individual temporal table every time you invoke the process. In base a tale valore e al numero di iterazioni desiderato, determinare in modo dinamico i punti di suddivisione per ogni chiamata di procedura.Based on that and number of number of iterations you want to have determine dynamically split points for every procedure invocation.

  • Pianificare un periodo di ritardo tra le iterazioni per una singola tabella, in modo da ridurre l'impatto sulle applicazioni che accedono alla tabella temporale.Plan to have a period of delay between iterations for a single table to reduce impact on applications that access the temporal table.

    Una stored procedure che elimina i dati per una singola tabella temporale potrebbe avere un aspetto analogo a quello del frammento di codice seguente. Verificare attentamente il codice e modificarlo prima di applicarlo all'ambiente specifico:A stored procedure that deletes the data for a single temporal table might look like in the following code snippet (review this code carefully and adjust it before apply in your environment):

DROP PROCEDURE IF EXISTS sp_CleanupHistoryData;  
GO  

CREATE PROCEDURE sp_CleanupHistoryData  
         @temporalTableSchema sysname  
       , @temporalTableName sysname  
       , @cleanupOlderThanDate datetime2  
AS  
    DECLARE @disableVersioningScript nvarchar(max) = '';  
    DECLARE @deleteHistoryDataScript nvarchar(max) = '';  
    DECLARE @enableVersioningScript nvarchar(max) = '';  

DECLARE @historyTableName sysname    
DECLARE @historyTableSchema sysname    
DECLARE @periodColumnName sysname    

/*Generate script to discover history table name and end of period column for given temporal table name*/  
EXECUTE sp_executesql   
    N'SELECT @hst_tbl_nm = t2.name, @hst_sch_nm = s.name, @period_col_nm = c.name  
        FROM sys.tables t1   
           JOIN sys.tables t2 on t1.history_table_id = t2.object_id  
        JOIN sys.schemas s on t2.schema_id = s.schema_id  
            JOIN sys.periods p on p.object_id = t1.object_id  
           JOIN sys.columns c on p.end_column_id = c.column_id and c.object_id = t1.object_id  
                  WHERE   
                 t1.name = @tblName and s.name = @schName'  
                , N'@tblName sysname  
                , @schName sysname  
                , @hst_tbl_nm sysname OUTPUT  
                , @hst_sch_nm sysname OUTPUT  
                , @period_col_nm sysname OUTPUT'  
                , @tblName = @temporalTableName  
                , @schName = @temporalTableSchema  
                , @hst_tbl_nm = @historyTableName OUTPUT  
                , @hst_sch_nm = @historyTableSchema OUTPUT  
                , @period_col_nm = @periodColumnName OUTPUT   

IF @historyTableName IS NULL OR @historyTableSchema IS NULL OR @periodColumnName IS NULL  
    THROW 50010, 'History table cannot be found. Either specified table is not system-versioned temporal or you have provided incorrect argument values.', 1  

/*Generate 3 statements that will run inside a transaction: SET SYSTEM_VERSIONING = OFF, DELETE FROM history_table, SET SYSTEM_VERSIONING = ON */  
SET @disableVersioningScript =  @disableVersioningScript + 'ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + '] SET (SYSTEM_VERSIONING = OFF)'  
SET @deleteHistoryDataScript =  @deleteHistoryDataScript + ' DELETE FROM  [' + @historyTableSchema + '].[' + @historyTableName + ']   
     WHERE ['+ @periodColumnName + '] < ' + '''' + convert(varchar(128), @cleanupOlderThanDate, 126) +  ''''   
SET @enableVersioningScript =  @enableVersioningScript + ' ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + ']   
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[' + @historyTableName + '], DATA_CONSISTENCY_CHECK = OFF )); '   

BEGIN TRAN  
    EXEC (@disableVersioningScript);  
    EXEC (@deleteHistoryDataScript);  
    EXEC (@enableVersioningScript);  
COMMIT;  

Approccio criteri di conservazione di cronologia temporaleUsing Temporal History Retention Policy Approach

Nota: approccio utilizzando i criteri di conservazione della cronologia temporale si applica ai Database SQLSQL Database e 2017 di SQL Server a partire dalla versione CTP 1.3.NOTE: Using the Temporal History Retention Policy approach applies to Database SQLSQL Database and SQL Server 2017 starting from CTP 1.3.

Periodo di memorizzazione cronologia temporale può essere configurato a livello di singola tabella, che consente agli utenti di creare degli oggetti eliminati flessibile criteri.Temporal history retention can be configured at the individual table level, which allows users to create flexible aging polices. L'applicazione di memorizzazione temporale è semplice: è necessario un solo parametro da impostare durante la modifica dello schema o di creazione tabella.Applying temporal retention is simple: it requires only one parameter to be set during table creation or schema change.

Dopo aver definito i criteri di conservazione, Database SQL di Azure avvia verifica regolarmente se sono presenti righe cronologiche idonei per la pulizia automatica dei dati.After you define retention policy, Azure SQL Database starts checking regularly if there are historical rows that are eligible for automatic data cleanup. Identificazione delle righe corrispondenti e la rimozione dalla tabella di cronologia si verificano in modo trasparente, l'attività in background pianificata, eseguire dal sistema.Identification of matching rows and their removal from the history table occur transparently, in the background task that is scheduled and run by the system. Condizione di validità per le righe della tabella di cronologia viene controllato in base alla colonna che rappresenta di fine del periodo SYSTEM_TIME.Age condition for the history table rows is checked based on the column representing end of SYSTEM_TIME period. Se il periodo di memorizzazione, ad esempio, è impostato su sei mesi, idonei per la pulizia delle righe della tabella soddisfano la condizione seguente:If retention period, for example, is set to six months, table rows eligible for cleanup satisfy the following condition:

ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())

Nell'esempio precedente, si presuppone che la colonna ValidTo corrisponde alla fine del periodo SYSTEM_TIME.In the preceding example, we assumed that ValidTo column corresponds to the end of SYSTEM_TIME period.

Come configurare i criteri di conservazione?How to configure retention policy?

Prima di configurare criteri di conservazione per una tabella temporale, controllare innanzitutto se il mantenimento di cronologia temporale è abilitato a livello di database:Before you configure retention policy for a temporal table, check first whether temporal historical retention is enabled at the database level:

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases

Flag del database is_temporal_history_retention_enabled è impostata su ON per impostazione predefinita, ma gli utenti possono modificare con l'istruzione ALTER DATABASE.Database flag is_temporal_history_retention_enabled is set to ON by default, but users can change it with ALTER DATABASE statement. Si è impostato su OFF anche automaticamente dopo il punto di ripristino di tempo.It is also automatically set to OFF after point in time restore operation. Per attivare la pulizia di memorizzazione cronologia temporale per il database, eseguire l'istruzione seguente:To enable temporal history retention cleanup for your database, execute the following statement:

ALTER DATABASE <myDB>
SET TEMPORAL_HISTORY_RETENTION  ON

Criteri di conservazione viene configurato durante la creazione della tabella, specificando un valore per il parametro HISTORY_RETENTION_PERIOD:Retention policy is configured during table creation by specifying value for the HISTORY_RETENTION_PERIOD parameter:

CREATE TABLE dbo.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,
        HISTORY_RETENTION_PERIOD = 6 MONTHS
     )
 );

È possibile specificare il periodo di conservazione con unità di tempo diversi: giorni, settimane, mesi e anni.You can specify retention period by using different time units: DAYS, WEEKS, MONTHS, and YEARS. Se HISTORY_RETENTION_PERIOD viene omesso, viene utilizzata la memorizzazione INFINITA.If HISTORY_RETENTION_PERIOD is omitted, INFINITE retention is assumed. È inoltre possibile utilizzare in modo esplicito infinito (parola chiave).You can also use INFINITE keyword explicitly. In alcuni scenari, si consiglia di configurare retention dopo la creazione della tabella o per modificare in precedenza valore configurato.In some scenarios, you may want to configure retention after table creation, or to change previously configured value. In questo caso usare l'istruzione ALTER TABLE:In that case use ALTER TABLE statement:

ALTER TABLE dbo.WebsiteUserInfo
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));

Per esaminare lo stato corrente del criterio di conservazione, utilizzare la seguente query che unisce i flag di abilitazione della memorizzazione temporale a livello di database con periodi di memorizzazione per le singole tabelle:To review current state of the retention policy, use the following query that joins temporal retention enablement flag at the database level with retention periods for individual tables:

SELECT DB.is_temporal_history_retention_enabled,
SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
T1.name as TemporalTableName,  SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
T2.name as HistoryTableName,T1.history_retention_period,
T1.history_retention_period_unit_desc
FROM sys.tables T1  
OUTER APPLY (select is_temporal_history_retention_enabled from sys.databases
where name = DB_NAME()) AS DB
LEFT JOIN sys.tables T2   
ON T1.history_table_id = T2.object_id WHERE T1.temporal_type = 2

Il Database SQL consente di eliminare obsoleti righe?How SQL Database deletes aged rows?

Il processo di pulizia dipende dal layout indice della tabella di cronologia.The cleanup process depends on the index layout of the history table. È importante notare che solo le tabelle di cronologia con un indice cluster (albero B o columnstore) possono avere criteri di conservazione finito configurato.It is important to notice that only history tables with a clustered index (B-tree or columnstore) can have finite retention policy configured. Un'attività in background viene creata per eseguire la pulizia dei dati obsoleti per tutte le tabelle temporali con periodo di memorizzazione finito.A background task is created to perform aged data cleanup for all temporal tables with finite retention period. Logica di pulizia per l'indice cluster rowstore (albero B) Elimina le righe obsolete in blocchi più piccoli (fino a 10 KB) riducendo al minimo l'utilizzo nei log del database e il sottosistema dei / o.Cleanup logic for the rowstore (B-tree) clustered index deletes aged rows in smaller chunks (up to 10K) minimizing pressure on database log and I/O subsystem. Anche se viene utilizzata la logica di pulizia necessarie indice B-tree, ordine di eliminazione per le righe anteriore al periodo di conservazione non può essere garantito sia ben.Although cleanup logic utilizes required B-tree index, order of deletions for the rows older than retention period cannot be firmly guaranteed. Di conseguenza, non accettano tutte le dipendenze nell'ordine di pulizia nelle applicazioni.Hence, do not take any dependency on the cleanup order in your applications.

L'attività di pulizia per columnstore cluster rimuove i gruppi di intera riga in una sola volta (in genere contiene 1 milione di righe ogni), che è molto efficiente, in particolare quando i dati cronologici viene generati a un ritmo elevato.The cleanup task for the clustered columnstore removes entire row groups at once (typically contain 1 million of rows each), which is very efficient, especially when historical data is generated at a high pace.

Clustered columnstore conservazioneClustered columnstore retention

Compressione dei dati eccellente e rende pulizia efficiente memorizzazione columnstore indice cluster la soluzione ottimale per gli scenari quando il carico di lavoro genera rapidamente elevate quantità di dati cronologici.Excellent data compression and efficient retention cleanup makes clustered columnstore index a perfect choice for scenarios when your workload rapidly generates high amount of historical data. Tale modello è tipico per i carichi di lavoro con utilizzo intensivo di elaborazione delle transazioni che utilizzano le tabelle temporali per il rilevamento delle modifiche e il controllo, l'analisi delle tendenze o IoT inserimento di dati.That pattern is typical for intensive transactional processing workloads that use temporal tables for change tracking and auditing, trend analysis, or IoT data ingestion.

Verificare gestire i dati cronologici nelle tabelle temporali con criteri di conservazione per altri dettagli.Please check Manage historical data in Temporal Tables with retention policy for more details.

Vedere ancheSee Also

Tabelle temporali Temporal Tables
Introduzione alle tabelle temporali con controllo delle versioni di sistema Getting Started with System-Versioned Temporal Tables
Verifiche di coerenza del sistema della tabella temporale Temporal Table System Consistency Checks
Partizionamento con le tabelle temporali Partitioning with Temporal Tables
Considerazioni e limitazioni delle tabelle temporali Temporal Table Considerations and Limitations
Sicurezza di una tabella temporale Temporal Table Security
Tabelle temporali con controllo delle versioni di sistema con tabelle con ottimizzazione per la memoria System-Versioned Temporal Tables with Memory-Optimized Tables
Funzioni e viste per i metadati delle tabelle temporali Temporal Table Metadata Views and Functions