Administración de la retención de datos históricos en las tablas temporales con versiones del sistemaManage Retention of Historical Data in System-Versioned Temporal Tables

SE APLICA A: síSQL Server síAzure SQL Database noAzure Synapse Analytics (SQL DW) noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Con las tablas temporales con versiones del sistema, la tabla de historial puede aumentar el tamaño de la base de datos más que las tablas normales, especialmente en las siguientes condiciones:With system-versioned temporal tables, the history table may increase database size more than regular tables, particularly under the following conditions:

  • Retención de datos históricos durante un largo períodoYou retain historical data for a long period of time

  • Disponibilidad de una actualización o eliminación del modelo de modificación de gran cantidad de datosYou have an update or delete heavy data modification pattern

Una tabla de historial de gran tamaño y creciente puede ser un problema debido a los costos de almacenamiento puro y a la imposición de un impuesto de rendimiento sobre las consultas temporales.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. Por lo tanto, al desarrollar una directiva de retención de datos para administrar datos en la tabla de historial es un aspecto importante de la planeación y la administración del ciclo de vida de cada tabla temporal.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.

Administración de la retención de datos para la tabla de historialData retention management for history table

La administración de la retención de datos de la tabla temporal empieza por determinar el período de retención requerido para cada tabla temporal.Managing temporal table data retention begins with determining the required retention period for each temporal table. La directiva de retención, en la mayoría de los casos, debe considerarse parte de la lógica de negocios de la aplicación mediante las tablas temporales.Your retention policy, in most cases, should be considered to be part of the business logic of the application using the temporal tables. Por ejemplo, las aplicaciones de datos de auditoría y escenarios de viaje en el tiempo tienen requisitos firmes en términos de cuánto tiempo deben estar disponibles los datos históricos para la consulta en línea.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.

Una vez que determine el período de retención de datos, el siguiente paso es desarrollar un plan para administrar los datos históricos, cómo y dónde almacenar los datos históricos y cómo eliminar los datos históricos que son anteriores a los requisitos de retención.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. Los cuatro enfoques siguientes están disponibles para administrar los datos históricos en la tabla temporal de historial:The following four approaches for managing historical data in the temporal history table are available:

Con cada uno de estos enfoques, la lógica para la migración o limpieza de datos del historial se basa en la columna que se corresponde con el final del período en la tabla actual.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. El final del valor del período para cada fila determina el momento en el que la versión de fila se "cierra", es decir, cuando llega a la tabla de historial.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. Por ejemplo, la condición SysEndTime < DATEADD (DAYS, -30, SYSUTCDATETIME ()) especifica que esos datos históricos anteriores a un mes tienen quitarse o extraerse de la tabla de historial.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: Los ejemplos de este tema usan este ejemplo de tabla temporal.NOTE: The examples in this topic use this Temporal Table example.

Uso del enfoque de Stretch DatabaseUsing Stretch Database approach

NOTA: El uso del enfoque de Stretch Database solo se aplica a SQL ServerSQL Server y no se aplica a SQL DatabaseSQL Database.NOTE: Using the Stretch Database approach only applies to SQL ServerSQL Server and does not apply to SQL DatabaseSQL Database.

Stretch Database en SQL ServerSQL Server migra los datos históricos de forma transparente a Azure.Stretch Database in SQL ServerSQL Server migrates your historical data transparently to Azure. Para obtener seguridad adicional, puede cifrar los datos en movimiento con la característica Always Encrypted de SQL Server.For additional security, you can encrypt data in motion using SQL Server's Always Encrypted feature. Además, puede usar Seguridad de nivel de fila y otras características de seguridad avanzadas de SQL Server con Temporal y Stretch Database para proteger los datos.Additionally, you can use Row-Level Security and other advanced SQL Server security features with Temporal and Stretch Database to protect your data.

Con el enfoque de Stretch Database, puede ajustar algunas o todas las tablas de historial temporales en Azure y SQL Server moverá de forma silenciosa los datos históricos a 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. La habilitación del ajuste de una tabla de historial no cambia la forma en la que interactúa con la tabla temporal en términos de modificación de datos y consultas temporales.Stretch-enabling a history table does not change how you interact with the temporal table in terms of data modification and temporal querying.

  • Ajuste de la tabla de historial completa: configure Stretch Database para la tabla de historial completa si el escenario principal es la auditoría de datos en un entorno con cambios frecuentes de datos y una consulta relativamente poco frecuente sobre los datos históricos.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. En otras palabras, puede utilizar este enfoque si el rendimiento de las consultas temporales no es importante.In other words, use this approach if performance of temporal querying is not critical. En este caso, la rentabilidad proporcionada por Azure puede resultar atractiva.In this case, the cost-effectiveness provided by Azure may be compelling.
    Cuando ajuste la tabla de historial completa, puede usar el Asistente de Stretch o Transact-SQL.When stretching the entire history table, you can either use the Stretch Wizard or Transact-SQL. A continuación aparecen ejemplos de ambos.Examples of both appear below.

  • Ajuste de una parte de la tabla de historial: configure Stretch Database para una sola parte de la tabla de historial para mejorar el rendimiento si el escenario principal implica principalmente consultar datos históricos recientes, pero quiere conservar la opción de consultar datos históricos anteriores cuando sea necesario mientras se almacenan esos datos de forma remota a un menor costo.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. Con Transact-SQL, puede hacerlo si especifica una función de predicado para seleccionar las filas que se va a migrar de la tabla de historial en lugar de migrar todas las filas.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. Cuando se trabaja con las tablas temporales, normalmente tiene sentido mover los datos en función de la condición de tiempo (es decir, según la edad de la versión de fila en la tabla de historial).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).
    Utilice una función de predicado determinista para mantener una parte del historial en la misma base de datos con los datos actuales, mientras el resto se migra a 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.
    Para ver ejemplos y limitaciones, consulte Selección de las filas que se van a migrar mediante una función de filtro (Stretch Database).For examples and limitations, see Select rows to migrate by using a filter function (Stretch Database). Puesto que las funciones no determinista no son válidas, si desea transferir datos de historial al estilo de ventana deslizante, necesitaría alterar regularmente la definición de las funciones de predicado en línea de manera que la ventana de filas que mantenga localmente sea constante en términos de edad.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 ventana deslizante le permite mover constantemente datos históricos con una antigüedad superior a un mes a Azure.Sliding window allows you to constantly move historical data older than one month to Azure. A continuación, aparece un ejemplo de este enfoque.An example of this approach appears below.

NOTA: Stretch Database migra los datos a Azure.NOTE: Stretch Database migrates data to Azure. Por lo tanto, necesita una cuenta de Azure y una suscripción para la facturación.Therefore, you have to have an Azure account and a subscription for billing. Para obtener una cuenta de Azure de evaluación gratuita, haga clic en Evaluación gratuita de un mes.To get a free trial Azure account, click Free One-Month Trial.

Puede configurar una tabla de historial temporal para Stretch con el Asistente de Stretch o Transact-SQL, y puede habilitar el ajuste para una tabla de historial temporal mientras la versión del sistema se establece en ON.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. El ajuste de la tabla actual no está permitido porque no tiene sentido aplicarlo.Stretching the current table is not allowed because it does not make sense to stretch the current table.

Uso del Asistente de Stretch para ajustar la tabla de historial completoUsing the Stretch Wizard to stretch the entire history table

El método más sencillo para los principiantes es usar el Asistente de Stretch para habilitar el ajuste para la base de datos completa y, después, seleccionar la tabla de historial temporal en el Asistente de Stretch (en este ejemplo se supone que ha configurado la tabla de departamento como una tabla temporal con versiones del sistema en una base de datos vacía).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). En SQL Server 2016 (13.x)SQL Server 2016 (13.x), no puede hacer clic con el botón derecho en la propia tabla de historial temporal y hacer clic en Stretch.In SQL Server 2016 (13.x)SQL Server 2016 (13.x), you cannot right-click the temporal history table itself and click Stretch.

  1. Haga clic con el botón derecho en la base de datos y seleccione Tareas, seleccione Stretchy, después, haga clic en Habilitar para iniciar el asistente.Right-click your database and point to Tasks, point to Stretch, and then click Enable to launch the wizard.

  2. En la ventana Seleccionar tablas , seleccione la casilla de la tabla de historial temporal y haga clic en Siguiente.In the Select tables window, select the checkbox for the temporal history table and click Next.

    Selección de la tabla de historial en la página Seleccionar tablasSelecting the history table on the Select tables page

  3. En la ventana Configuración de Azure , proporcione las credenciales de inicio de sesión.In the Configure Azure window, provide your login credentials. Inicie sesión en Microsoft Azure o regístrese para obtener una cuenta.Sign in to Microsoft Azure or sign-up for an account. Seleccione la suscripción que va a usar y la región de Azure.Select the subscription to use, select the Azure region. Después, cree un nuevo servidor o seleccione un servidor existente.Then either create a new server or select an existing server. Haga clic en Siguiente.Click Next.

    Creación de un nuevo Asistente para Stretch Database de servidor de AzureCreate new Azure server - Stretch Database wizard

  4. En la ventana Credenciales de seguridad , proporcione una contraseña para la clave maestra de base de datos para proteger sus credenciales de base de datos de SQL Server de origen y haga clic en Siguiente.In the Secure credentials window, provide a password for the database master key to secure your source SQL Server database credential and click Next.

    Página de credenciales seguras del Asistente para Stretch DatabaseSecure credentials page of the Stretch Database wizard

  5. En la ventana Seleccionar dirección IP , proporcione el intervalo de direcciones IP para SQL Server para que el servidor de Azure se comunique con SQL Server (si selecciona un servidor existente para el ya existe una regla de firewall, simplemente haga clic en Siguiente aquí para usar la regla de firewall existente).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). Haga clic en Siguiente y, después, en Finalizar para habilitar Stretch Database y ajustar la tabla de historial temporal.Click Next and then click Finish to enable Stretch Database and stretch the temporal history table.

    Página Seleccionar dirección IP del Asistente para Stretch DatabaseSelect IP address page of the Stretch Database wizard

  6. Cuando se complete el asistente, compruebe que se haya habilitado el ajuste correctamente para la base de datos.When the wizard completes, verify that your database was successfully stretch-enabled. Vea los iconos del Explorador de objetos que indican que se ha ajustado la base de datos.Notice the icons in Object Explorer indicating the database was stretched

NOTA: Si se produce un error en Habilitar base de datos para Stretch, revise el registro de errores.NOTE: If the Enable Database for Stretch fails, review the error log. Un error común consiste en configurar incorrectamente la regla de firewall.A common error is improperly configuring the firewall rule.

Vea también:See also:

Uso de Transact-SQL para ajustar la tabla de historial completoUsing Transact-SQL to stretch the entire history table

También puede usar Transact-SQL para habilitar Stretch en el servidor local y Habilitación de Stretch Database para una base de datos.You can also use Transact-SQL to enable Stretch on the local server and Enable Stretch Database for a database. Después, puede usar Transact-SQL para habilitar Stretch Database en una tabla.You can then use Transact-SQL to enable Stretch Database on a table. Con una base de datos habilitada previamente para Stretch Database, ejecute el siguiente script de Transact-SQL para ajustar una tabla de historial temporal con versiones del sistema existente: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 de Transact-SQL para ajustar una parte de la tabla de historialUsing Transact-SQL to stretch a portion of the history table

Para ajustar solo una parte de la tabla de historial primero debe crear una función de predicado en línea.To stretch only a portion of the history table, you start by creating an inline predicate function. En este ejemplo, supongamos que ha configurado la función de predicado en línea por primera vez el 1 de diciembre de 2015 y quiere ajustar en Azure todas las fechas de historial anteriores al 1 de noviembre de 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. Para lograr esto, empiece por crear la siguiente función: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) ;  

a continuación, use el siguiente script para agregar el predicado de filtro a la tabla de historial y establecer el estado de la migración en OUTBOUND para permitir la migración de datos basado en predicado para la tabla de historial.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   
                )  
        )   
;  

Para mantener una ventana deslizante, necesita que la función de predicado sea precisa cada día (es decir, cambiar la condición de fila de filtrado cada día en un día).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). El siguiente script es el script que necesitaría ejecutar el 2 de diciembre de 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 ;  

utilice el Agente SQL Server o algún otro mecanismo de programación para asegurarse de que la definición de la función de predicado es válida todo el tiempo.Use SQL Server Agent or some other scheduling mechanism to ensure valid predicate function definition all the time.

Uso del enfoque de la partición de tablaUsing Table Partitioning Approach

Lapartición de tabla puede hacer que las tablas grandes sean más escalables y fáciles de administrar.Table partitioning can make large tables more manageable and scalable. Con el enfoque de partición de tabla, puede usar particiones de tabla de historial para implementar la limpieza de datos personalizada o el archivado sin conexión según una condición de tiempo.Using the table partitioning approach, you can use history table partitions to implement custom data cleanup or offline archival based on a time condition. La partición de tabla también le proporcionará ventajas de rendimiento cuando se realicen consultas de tablas temporales en un subconjunto de historial de datos mediante la eliminación de una partición.Table partitioning will also give you performance benefits when querying temporal tables on a subset of data history by using partition elimination.

Con la partición de tabla, puede implementar un enfoque de ventana deslizante para extraer la parte más antigua de los datos históricos de la tabla de historial y mantener el tamaño de la parte retenida constante en términos de edad: manteniendo los datos en la tabla de historial igual que en el período de retención requerido.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. Se admite la operación de conmutación de datos fuera de la tabla de historial cuando SYSTEM_VERSIONING está activado, lo que significa que puede limpiar una parte de los datos del historial sin introducir una ventana de mantenimiento o bloquear las cargas de trabajo normales.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: Para realizar la conmutación de particiones, el índice agrupado en la tabla de historial debe alinearse con el esquema de partición (debe contener 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 tabla de historial predeterminada creada por el sistema contiene un índice agrupado que incluye las columnas SysEndTime y SysStartTime, que es óptimo para la creación de particiones, la inserción de nuevos datos de historial y la típica consulta temporal.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. Para obtener más información, consulte Temporal Tables.For more information, see Temporal Tables.

Un enfoque de ventana deslizante tiene dos conjuntos de tareas que tiene que realizar:A sliding window approach has two sets of tasks that you need to perform:

  • Una tarea de configuración de particiónA partitioning configuration task

  • Tareas periódicas de mantenimiento de particiónRecurring partition maintenance tasks

En la ilustración, supongamos que se quieren mantener datos históricos durante seis meses y mantener todos los meses de datos en una partición independiente.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. Además, supongamos que se ha activado el control de versiones del sistema en septiembre de 2015.Also, let's assume that we activated system-versioning in September of 2015.

Una tarea de configuración de particiones crea la configuración inicial de partición de la tabla de historial.A partitioning configuration task creates the initial partitioning configuration for the history table. En este ejemplo, crearíamos las mismas particiones de número que el tamaño de la ventana deslizante, en meses, más una partición vacía adicional preparada previamente (se explica a continuación).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). Esta configuración garantiza que el sistema podrá almacenar correctamente los datos nuevos cuando iniciemos la tarea de mantenimiento periódico de la partición la primera vez y garantiza que nunca dividiremos las particiones con datos para evitar movimientos de datos valiosos.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. Debe realizar esta tarea mediante Transact-SQL con el siguiente script de ejemplo.You should perform this task using Transact-SQL using the example script below.

En la siguiente imagen se muestra la configuración inicial de la creación de particiones para mantener 6 meses de datos.The following picture shows initial partitioning configuration to keep 6 months of data.

PartitioningPartitioning

NOTA: Vea las consideraciones de rendimiento con las particiones de tabla siguientes para las implicaciones de rendimiento de uso de la opción RANGE LEFT frente a la opción RANGE RIGHT al configurar la creación de particiones.NOTE: See Performance considerations with table partitioning below for the performance implications of using RANGE LEFT versus RANGE RIGHT when configuring partitioning.

Tenga en cuenta que la primera y última partición están "abiertas" en los límites inferior y superior respectivamente para asegurarse de que cada fila nueva tiene la partición de destino con independencia del valor de la columna de partición.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.
A medida que pasa el tiempo, las nuevas filas de la tabla del historial se dirigirán a particiones superiores.As time goes by, new rows in history table will land in higher partitions. Cuando se llene la partición 6ª, se habrá alcanzado el período de retención de destino.When 6th partition gets filled up, we will have reached the targeted retention period. Este es el momento en el que se debe iniciar la tarea de mantenimiento periódico de la partición por primera vez (debe programarse para ejecutarse periódicamente; una vez al mes en este ejemplo).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 imagen siguiente muestra las tareas de mantenimiento periódico de la partición (vea los pasos detallados a continuación).The following picture illustrates the recurring partition maintenance tasks (see detailed steps below).

Partitioning2Partitioning2

Los pasos detallados para las tareas de mantenimiento periódico de la partición son:The detailed steps for the recurring partition maintenance tasks are:

  1. SWITCH OUT: permite crear una tabla de almacenamiento provisional y, después, cambiar una partición entre la tabla de historial y la tabla de almacenamiento provisional mediante la instrucción ALTER TABLE (Transact-SQL) con el argumento SWITCH PARTITION (vea el ejemplo C. Cambio de particiones entre tablas).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>  
    

    Después del cambio de partición, puede archivar opcionalmente los datos de la tabla de almacenamiento provisional y, a continuación, quitar o truncar la tabla de almacenamiento provisional para que estén listos para la próxima vez que necesite realizar esta tarea de mantenimiento periódico de la partición.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: permite combinar la partición 1 vacía con la partición 2 mediante ALTER PARTITION FUNCTION (Transact-SQL) con la opción MERGE RANGE (vea el ejemplo B).MERGE RANGE: Merge the empty partition 1 with partition 2 using the ALTER PARTITION FUNCTION (Transact-SQL) with MERGE RANGE (See example B). Al quitar el límite inferior con esta función, se combina eficazmente la partición vacía 1 con la partición anterior 2 para formar una nueva partición 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. Las demás particiones también cambian de forma efectiva sus ordinales.The other partitions also effectively change their ordinals.

  3. SPLIT RANGE: permite crear una partición 7 vacía mediante ALTER PARTITION FUNCTION (Transact-SQL) con la opción SPLIT RANGE (vea el ejemplo A).SPLIT RANGE: Create a new empty partition 7 using the ALTER PARTITION FUNCTION (Transact-SQL) with SPLIT RANGE (See example A). Al agregar un nuevo límite superior mediante esta función, crea eficazmente una partición independiente para el próximo mes.By adding a new upper boundary using this function, you effectively create a separate partition for the upcoming month.

Uso de Transact-SQL para crear particiones en la tabla de historialUse Transact-SQL to create partitions on history table

Utilice el script de Transact-SQL en la ventana de código siguiente para crear la función de partición y el esquema de partición, y volver a crear el índice agrupado para que la partición se alinee con las particiones o el esquema de partición.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. En este ejemplo, crearemos un enfoque de ventana deslizante de seis meses con particiones mensuales a partir de septiembre de 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 de Transact-SQL para mantener particiones en el escenario de ventana deslizanteUsing Transact-SQL to maintain partitions in sliding window scenario

Utilice el script de Transact-SQL en la siguiente ventana de código siguiente para mantener las particiones en el escenario de ventana deslizante.Use the Transact-SQL script in the code window below to maintain partitions in the sliding window scenario. En este ejemplo, se conmutará la partición de septiembre de 2015 con la opción MERGE RANGE y, a continuación, se agregará una nueva partición de marzo de 2016 con la opción 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  
  

Puede modificar ligeramente el script anterior y usarlo en el proceso normal de mantenimiento mensual:You can slightly modify script above and use it in regular monthly maintenance process:

  1. En el paso (1), cree una nueva tabla de almacenamiento provisional para el mes que desee quitar (octubre sería el siguiente en nuestro ejemplo)In step (1) create new staging table for the month you want to remove (October would be next one in our example)

  2. En el paso (3), cree la restricción y compruebe que coincide con el mes de datos que quiere quitar: [SysEndTime]<=N'2015-10-31T23:59:59.999' para la partición de octubreIn 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. En el paso (4), cambie la partición 1 a la tabla de almacenamiento provisional recién creadaIn step (4) SWITCH partition 1 to newly created staging table

  4. En el paso (6), modifique la función de partición mediante la combinación del límite inferior: MERGE RANGE(N'2015-10-31T23:59:59.999' después de extraer los datos de octubreIn 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. En el paso (7), divida la función de partición mediante la creación del límite superior: SPLIT RANGE (N'2016-04-30T23:59:59.999' después de extraer los datos de octubre.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.

Sin embargo, la mejor solución sería ejecutar regularmente un script de Transact-SQL genérico que fuese capaz de llevar a cabo la acción apropiada cada mes sin modificar el 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. Es posible generalizar el script anterior para que actúe sobre los parámetros proporcionados (límite inferior que debe combinarse y límite nuevo que se creará con la división de particiones).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). Para evitar la creación de una tabla de almacenamiento provisional cada mes, puede crear una con antelación y volver a usarla cambiando la restricción de comprobación para que coincida con la partición que se conmutará. Eche un vistazo a las páginas siguientes para obtener ideas sobre cómo la ventana deslizante puede automatizarse al completo mediante un script de Transact-SQL.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. Take a look at the following pages to get ideas on how sliding window can be fully automated using a Transact-SQL script.

Consideraciones de rendimiento con las particiones de tablaPerformance considerations with table partitioning

Es importante realizar las operaciones MERGE y SPLIT RANGE para evitar cualquier movimiento de datos, ya que este puede provocar una sobrecarga considerable del rendimiento.It is important to perform the MERGE and SPLIT RANGE operations to avoid any data movement as data movement can incur significant performance overhead. Para obtener más información, vea Modificar una función de partición. Conseguirá esto usando la opción RANGE LEFT en lugar de la opción RANGE RIGHT cuando aplique 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).

Primero se va a explicar visualmente el significado de las opciones RANGE LEFT y RANGE RIGHT:Let's first visually explain meaning of the RANGE LEFT and RANGE RIGHT options:

Partitioning3Partitioning3

Si se define una función de partición como RANGE LEFT, los valores especificados son los límites superiores de las particiones.When you define a partition function as RANGE LEFT, the specified values are the upper boundaries of the partitions. Cuando utilice la opción RANGE RIGHT, los valores especificados son los límites inferiores de las particiones.When you use RANGE RIGHT, the specified values are the lower boundaries of the partitions. Cuando utilice la operación MERGE RANGE para quitar un límite de la definición de la función de partición, la implementación subyacente también quita la partición que contiene el límite.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. Si esa partición no está vacía, los datos se moverán a la partición que resulta de la operación MERGE RANGE.If that partition is not empty, data will be moved to the partition that is result of MERGE RANGE operation.

En el escenario de ventana deslizante, siempre quitamos el límite inferior de la partición.In sliding window scenario, we always remove lowest partition boundary.

  • Caso RANGE LEFT: en el caso RANGE LEFT, el límite inferior de la partición pertenece a la partición 1, que está vacía (después de conmutar la partición), por lo que MERGE RANGE no causará ningún movimiento de datos.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: en el caso RANGE RIGHT, el límite inferior de la partición pertenece a la partición 2, que no está vacía ya que se ha supuesto que la partición 1 se había vaciado mediante la conmutación. En este caso, la opción MERGE RANGE provocará un movimiento de datos (los datos de la partición 2 se moverán a la partición 1).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 this case MERGE RANGE will incur data movement (data from partition 2 will be moved to partition 1). Para evitar esto, la opción RANGE RIGHT del escenario de ventana deslizante debe tener la partición 1, que siempre está vacía.To avoid this, RANGE RIGHT in the sliding window scenario needs to have partition 1, which is always empty. Esto significa que si usamos la opción RANGE RIGHT, debemos crear y mantener una partición adicional en comparación con el caso de RANGE LEFT.This means that if we use RANGE RIGHT, we should create and maintain one additional partition compared to RANGE LEFT case.

Conclusión: el uso de RANGE LEFT en la partición deslizante es mucho más simple para la administración de la partición y evita el movimiento de datos.Conclusion: Using RANGE LEFT in sliding partition is much simpler for the partition management and avoids data movement. Sin embargo, la definición de los límites de partición con la opción RANGE RIGHT es un poco más simple, ya que no tiene que tratar con problemas de marca de tiempo de fecha y hora.However, defining partition boundaries with RANGE RIGHT is slightly simpler as you don't have to deal with datetime time tick issues.

Uso del enfoque de script de limpieza personalizadoUsing Custom Cleanup Script Approach

En los casos en los que el enfoque de particiones de tabla y Stretch Database no sean opciones viables, el tercer enfoque consiste en eliminar los datos de la tabla de historial con el script de limpieza personalizado.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. La eliminación de los datos de la tabla de historial es posible solo cuando aplica SYSTEM_VERSIONING = OFF.Deleting data from history table is possible only when SYSTEM_VERSIONING = OFF. Para evitar la incoherencia de datos, realice la limpieza durante la ventana de mantenimiento (cuando las cargas de trabajo que modifican datos no están activas) o dentro de una transacción (bloqueando de forma efectiva otras cargas de trabajo).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). Esta operación requiere el permiso de CONTROL sobre tablas de historial y actuales.This operation requires CONTROL permission on current and history tables.

Para bloquear mínimamente las aplicaciones normales y las consultas de usuario, elimine los datos en fragmentos más pequeños con un retraso al realizar el script de limpieza dentro de una transacción.To minimally block regular applications and user queries, delete data in smaller chunks with a delay when performing the cleanup script inside a transaction. Aunque no hay ningún tamaño óptimo para la eliminación de cada fragmento de datos en todos los escenarios, la eliminación de más de 10.000 filas en una sola transacción puede suponer un impacto 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 lógica de limpieza es la misma para todas las tablas temporales, por lo que se puede automatizar de forma relativamente sencilla a través de un procedimiento almacenado genérico que puede programar para que se ejecute periódicamente para cada tabla temporal para la que desee limitar el historial de datos.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.

El siguiente diagrama muestra cómo debe organizarse la lógica de limpieza para una tabla única para reducir el impacto en las cargas de trabajo en ejecución.The following diagram illustrates how your cleanup logic should be organized for a single table to reduce impact on the running workloads.

CustomCleanUpScriptDiagramCustomCleanUpScriptDiagram

Estas son algunas directrices de alto nivel para implementar el proceso.Here are some high-level guidelines for implementing the process. Programe la lógica de limpieza para que se ejecute todos los días y realice la iteración sobre todas las tablas temporales que necesitan la limpieza de datos.Schedule cleanup logic to run every day and iterate over all temporal tables that need data cleanup. Use el Agente SQL Server u otra herramienta para programar este proceso:Use SQL Server Agent or different tool to schedule this process:

  • Elimine los datos históricos en cada tabla temporal empezando por las filas más antiguas hasta las más recientes en varias iteraciones en pequeños fragmentos y evite la eliminación de todas las filas en una sola transacción tal como se muestra en la imagen anterior.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.

  • Implemente cada iteración como una invocación del procedimiento almacenado genérico que quita una parte de datos de la tabla de historial (vea el ejemplo de código siguiente para este procedimiento).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).

  • Calcule el número de filas que debe eliminar para una tabla temporal individual cada vez que se invoca el proceso.Calculate how many rows you need to delete for an individual temporal table every time you invoke the process. Según esto y el número de iteraciones que desee tener, determine dinámicamente los puntos de división para cada invocación del procedimiento.Based on that and number of number of iterations you want to have determine dynamically split points for every procedure invocation.

  • Planifique un período de retraso entre las iteraciones para una tabla única para reducir el impacto en las aplicaciones que dispongan de acceso a la tabla temporal.Plan to have a period of delay between iterations for a single table to reduce impact on applications that access the temporal table.

Un procedimiento almacenado que permita eliminar los datos de una tabla temporal única podría ser similar al fragmento de código siguiente (revise este código con cuidado y ajústelo antes de aplicarlo a su entorno):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:
  (1) SET SYSTEM_VERSIONING = OFF,
  (2) DELETE FROM history_table,
  (3) SET SYSTEM_VERSIONING = ON
  On SQL Server 2016, it is critical that (1) and (2) run in separate EXEC statements, or SQL Server will generate the following error: 
  Msg 13560, Level 16, State 1, Line XXX
  Cannot delete rows from a temporal history table '<database_name>.<history_table_schema_name>.<history_table_name>'.
*/  
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;  

Uso del enfoque de la directiva de retención de historial temporalUsing Temporal History Retention Policy Approach

NOTA: El uso de la directiva de retención de historial temporal se aplica a SQL DatabaseSQL Database y SQL Server 2017 a partir de CTP 1.3.NOTE: Using the Temporal History Retention Policy approach applies to SQL DatabaseSQL Database and SQL Server 2017 starting from CTP 1.3.

La retención de historial temporal se puede configurar en el nivel de tabla individual, lo que permite a los usuarios crear directivas de vencimiento flexibles.Temporal history retention can be configured at the individual table level, which allows users to create flexible aging polices. Aplicar la retención temporal es muy sencillo: solo requiere establecer un parámetro al cambiar el esquema o al crear la tabla.Applying temporal retention is simple: it requires only one parameter to be set during table creation or schema change.

Después de definir la directiva de retención, si hay filas de historial que sean aptas para la limpieza de datos automática, Azure SQL Database inicia la comprobación periódicamente.After you define retention policy, Azure SQL Database starts checking regularly if there are historical rows that are eligible for automatic data cleanup. La identificación de las filas coincidentes y su eliminación de la tabla de historial se producen de forma transparente, en la tarea en segundo plano que programa y ejecuta el 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. Se comprueba la condición de vencimiento para las filas de la tabla de historial en función de la columna que representa el final del período SYSTEM_TIME.Age condition for the history table rows is checked based on the column representing end of SYSTEM_TIME period. Si el período de retención se establece, por ejemplo, en seis meses, las filas aptas para la limpieza de la tabla cumplen la condición siguiente:If retention period, for example, is set to six months, table rows eligible for cleanup satisfy the following condition:

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

En el ejemplo anterior, se supone que la columna ValidTo corresponde al final del período SYSTEM_TIME.In the preceding example, we assumed that ValidTo column corresponds to the end of SYSTEM_TIME period.

¿Cómo configurar la directiva de retención?How to configure retention policy?

Antes de configurar la directiva de retención para una tabla temporal, compruebe si la retención de historial temporal está habilitada en el nivel de base de datos: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

La marca de base de datos is_temporal_history_retention_enabled se establece en ON de forma predeterminada, pero los usuarios pueden cambiarla con la instrucción ALTER DATABASE.Database flag is_temporal_history_retention_enabled is set to ON by default, but users can change it with ALTER DATABASE statement. También se establece automáticamente en OFF después de la operación de restauración a un momento dado.It is also automatically set to OFF after point in time restore operation. Para habilitar la limpieza de la retención de historial temporal de la base de datos, ejecute la instrucción siguiente:To enable temporal history retention cleanup for your database, execute the following statement:

ALTER DATABASE <myDB>
SET TEMPORAL_HISTORY_RETENTION  ON

La directiva de retención se configura al crear la tabla especificando el valor del parámetro 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
     )
 );

Puede especificar el período de retención mediante distintas unidades de tiempo: DAYS, WEEKS, MONTHS y YEARS.You can specify retention period by using different time units: DAYS, WEEKS, MONTHS, and YEARS. Si se omite HISTORY_RETENTION_PERIOD, se asume la retención INFINITE.If HISTORY_RETENTION_PERIOD is omitted, INFINITE retention is assumed. También puede usar explícitamente la palabra clave INFINITE.You can also use INFINITE keyword explicitly. En algunos escenarios, es posible que quiera configurar la retención tras crear la tabla o cambiar a un valor previamente configurado.In some scenarios, you may want to configure retention after table creation, or to change previously configured value. En ese caso, use la instrucción ALTER TABLE:In that case use ALTER TABLE statement:

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

Para revisar el estado actual de la directiva de retención, use la siguiente consulta, que combina la marca de habilitación de retención temporal en el nivel de base de datos con períodos de retención para tablas individuales: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

¿Cómo elimina SQL Database filas antiguas?How SQL Database deletes aged rows?

El proceso de limpieza depende del diseño del índice de la tabla de historial.The cleanup process depends on the index layout of the history table. Es importante tener en cuenta que solo las tablas de historial con un índice agrupado (árbol B o almacén de columnas) pueden tener una directiva de retención finita configurada.It is important to notice that only history tables with a clustered index (B-tree or columnstore) can have finite retention policy configured. Se crea una tarea en segundo plano para realizar la limpieza de datos antiguos de todas las tablas temporales con el período de retención finito.A background task is created to perform aged data cleanup for all temporal tables with finite retention period. La lógica de limpieza del índice agrupado de almacén de filas (árbol B) elimina las filas antiguas en fragmentos más pequeños (hasta 10 000), lo cual minimiza la presión en el registro de base de datos y el subsistema de E/S.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. A pesar de que la lógica de limpieza usa el índice de árbol B necesario, no se puede garantizar el orden de las eliminaciones de las filas más antiguas en relación con el período de retención.Although cleanup logic utilizes required B-tree index, order of deletions for the rows older than retention period cannot be firmly guaranteed. Por tanto, no hay ninguna dependencia en el orden de limpieza en sus aplicaciones.Hence, do not take any dependency on the cleanup order in your applications.

La tarea de limpieza del almacén de columnas agrupadas quita los grupos de filas completos a la vez (normalmente contiene 1 millón de filas cada uno), lo que es muy eficaz, especialmente cuando los datos de historial se generan a un ritmo alto.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.

Retención de almacén de columnas agrupadoClustered columnstore retention

La excelente compresión de datos y la limpieza eficaz de la retención hacen que el índice de almacén de columnas agrupadas sea una elección perfecta en escenarios en los que la carga de trabajo genera rápidamente una gran cantidad de datos de historial.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. Este patrón es típico de las cargas de trabajo de procesamiento intensivo de transacciones que usan tablas temporales para el seguimiento de cambios y la auditoría, el análisis de tendencias o la ingesta de datos de IoT.That pattern is typical for intensive transactional processing workloads that use temporal tables for change tracking and auditing, trend analysis, or IoT data ingestion.

Para obtener más información, consulte Administración de datos históricos en tablas temporales con directivas de retención.Please check Manage historical data in Temporal Tables with retention policy for more details.

Consulte tambiénSee Also

Tablas temporales Temporal Tables
Introducción a las tablas temporales con versión del sistema Getting Started with System-Versioned Temporal Tables
Comprobaciones de coherencia del sistema de la tabla temporal Temporal Table System Consistency Checks
Creación de particiones con tablas temporales Partitioning with Temporal Tables
Limitaciones y consideraciones de las tablas temporales Temporal Table Considerations and Limitations
Seguridad de la tabla temporal Temporal Table Security
Tablas temporales con control de versiones del sistema con tablas con optimización para memoria System-Versioned Temporal Tables with Memory-Optimized Tables
Funciones y vistas de metadatos de la tabla temporalTemporal Table Metadata Views and Functions