Administrar la retención de datos históricos en tablas temporales versionadas por el sistema

Aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed InstanceBase de datos SQL en Microsoft Fabric

Una tabla temporal versionada por sistema conserva todas las versiones anteriores de cada fila en su tabla de historial. La tabla de historial podría aumentar el tamaño de tu base de datos más que las tablas normales bajo las siguientes condiciones:

• Conservas datos históricos durante un largo periodo de tiempo.
• Tienes un patrón de modificación de datos con muchas actualizaciones o eliminaciones.

Una tabla de historia grande y en constante crecimiento podría convertirse en un problema, tanto por los costes de almacenamiento como por el impuesto al rendimiento que impone a las consultas temporales. Desarrollar una política de retención de datos para la tabla de historia es una parte importante de la planificación y gestión del ciclo de vida de cada tabla temporal.

Planifica una política de retención de datos

Para gestionar la retención de datos de tablas temporales, primero determina el periodo de retención requerido para cada tabla temporal. Tu política de retención, en la mayoría de los casos, debería formar parte de la lógica de negocio de la aplicación que utiliza las tablas temporales. Por ejemplo, las aplicaciones en auditoría de datos y escenarios de viajes en el tiempo tienen requisitos estrictos sobre cuánto tiempo deben estar disponibles los datos históricos para consultas en línea.

Después de determinar tu periodo de retención de datos, desarrolla un plan para gestionar los datos históricos. Decida cómo y dónde almacenar los datos históricos, y cómo eliminar los datos históricos que son más antiguos que los requisitos de retención.

Cada enfoque en este artículo actúa sobre la columna que corresponde al final del periodo en la tabla actual, que es la ValidTo columna en los ejemplos que siguen. 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. Por ejemplo, la condición ValidTo < DATEADD (DAY, -30, SYSUTCDATETIME()) coincide con datos históricos de más de 30 días.

Elige una de las siguientes opciones para realizar acciones en esas filas:

Approach	Cómo funciona	Cuándo usarlo
Política de retención de historial temporal	Estableces un periodo de retención para cada tabla y una tarea en segundo plano elimina automáticamente las filas antiguas.	La opción más sencilla, cuando puedes borrar el historial envejecido por completo.
Particionamiento de tablas	Una ventana deslizante cambia la partición más antigua fuera de la tabla de historial, así que puedes archivarla o descartarla.	Cuando quieres archivar datos históricos antes de eliminarlos, o quieres eliminar particiones para consultas temporales.
Script de limpieza personalizado	Un script programado desactiva la versionación del sistema, elimina filas antiguas en pequeños fragmentos y luego vuelve a activar la versionación del sistema.	Cuando no hay una política de retención disponible para tu tabla y la partición no es viable.

Los ejemplos de partición y limpieza personalizada en este artículo utilizan los ejemplos del artículo Crear una tabla temporal versionada para sistema .

Utiliza una política de retención del historial temporal

Aplica a: SQL Server 2017 (14.x) y versiones posteriores, Azure SQL Database, Azure SQL Managed Instance y base de datos SQL en Microsoft Fabric.

Puedes configurar la retención temporal del historial a nivel de tabla individual, lo que te permite crear políticas flexibles de envejecimiento. Para permitir la retención temporal, se configura HISTORY_RETENTION_PERIOD durante la creación de la tabla o un cambio de esquema.

Después de definir la política de retención, el Motor de base de datos ejecuta una tarea programada en segundo plano que encuentra y elimina de forma transparente las filas históricas cuyo valor de fin de periodo es anterior al periodo de retención.

Cómo configurar la directiva de retención

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:

SELECT is_temporal_history_retention_enabled,
       name
FROM sys.databases;

La marca de la base de datos tiene como valor predeterminado ON, pero puedes cambiarla mediante la instrucción ALTER DATABASE. El motor de base de datos también lo establece en OFF automáticamente tras una operación de restauración a un momento dado (PITR), como se describe en Consideraciones sobre la restauración a un momento dado. Para habilitar la limpieza de la retención de historial temporal de la base de datos, ejecute la instrucción siguiente. Sustituye <myDB> por la base de datos que quieras modificar:

ALTER DATABASE [<myDB>]
    SET TEMPORAL_HISTORY_RETENTION ON;

Importante

Puedes configurar la retención de tablas temporales incluso si is_temporal_history_retention_enabled es OFF, pero en ese caso el Motor de base de datos no activa la limpieza automática de filas antiguas.

Puedes configurar la política de retención durante la creación de la tabla especificando un valor para el HISTORY_RETENTION_PERIOD parámetro:

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

Con esa política en vigor, las filas de dbo.WebsiteUserInfoHistory pasan a ser aptas para su limpieza cuando cumplen la siguiente condición:

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

Puedes especificar el periodo de retención en DAYS, WEEKS, MONTHS, o YEARS. Si dejas fuera HISTORY_RETENTION_PERIOD, la retención por defecto es INFINITE. También puede usar explícitamente la palabra clave INFINITE.

En algunos casos, podrías querer configurar la retención después de la creación de la tabla o cambiar el valor previamente configurado. En ese caso, usa la instrucción ALTER TABLE:

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

Importante

Poner SYSTEM_VERSIONING en OFF no conserva el valor del periodo de retención. Establecer SYSTEM_VERSIONING en ON sin un HISTORY_RETENTION_PERIOD explícito provoca la conservación de INFINITE.

Para revisar el estado actual de la directiva de retención, use el ejemplo siguiente. Esta consulta combina la marca de habilitación de retención temporal en el nivel de base de datos con periodos de retención para tablas individuales:

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 AS T1
    OUTER APPLY (
        SELECT is_temporal_history_retention_enabled
        FROM sys.databases
        WHERE name = DB_NAME()
) AS DB
    LEFT OUTER JOIN sys.tables AS T2
        ON T1.history_table_id = T2.object_id
WHERE T1.temporal_type = 2;

Cómo elimina el motor de base de datos las filas antiguas

El proceso de limpieza depende del diseño del índice de la tabla de historial. Puedes configurar una política de retención finita solo en tablas de historial con un índice rowstore agrupado (B-tree) o un índice columnstore agrupado. Una tarea en segundo plano realiza la limpieza de datos envejecidos para todas las tablas temporales con un periodo de retención finito.

Note

La documentación utiliza el término árbol B generalmente en referencia a los índices. En los índices rowstore, el Motor de base de datos implementa un árbol B+. Esto no se aplica a los índices de almacén de columnas ni a los índices de tablas optimizadas para memoria. Para obtener más información, consulte la guía de diseño y arquitectura de índices de SQL Server y Azure SQL.

Índice de almacenamiento por filas de árbol B

El índice agrupado de rowstore debe comenzar con la columna correspondiente al final del SYSTEM_TIME periodo. Si tal índice no existe, no se puede configurar un periodo de retención finito:

Msg 13765, Level 16, State 1
Setting finite retention period failed on system-versioned temporal table
'dbo.WebsiteUserInfo' because the history table 'dbo.WebsiteUserInfoHistory'
does not contain required clustered index. Consider creating a clustered
columnstore or B-tree index starting with the column that matches end of
SYSTEM_TIME period, on the history table.

La tabla de historial por defecto ya tiene un índice agrupado compatible. Si intentas eliminar ese índice en una tabla de historial con un periodo de retención finito, la operación falla con el siguiente error:

Msg 13766, Level 16, State 1
Cannot drop the clustered index 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory'
because it is being used for automatic cleanup of aged data. Consider setting HISTORY_RETENTION_PERIOD to INFINITE on the corresponding system-versioned
temporal table if you need to drop this index.

La lógica de limpieza del índice agrupado de rowstore elimina filas antiguas en fragmentos más pequeños (hasta 10.000), minimizando la presión sobre el registro de la base de datos y el subsistema de E/S. Aunque la lógica de limpieza utiliza el índice B-tree requerido, no puede garantizar el orden en que se eliminan las filas cuya antigüedad supera el periodo de retención. No dependas del orden de limpieza en tus aplicaciones.

Índice de almacén de columnas agrupado

La tarea de limpieza del almacén de columnas agrupado elimina grupos completos de filas a la vez. Cada grupo de filas suele contener un millón de filas. Este método es más eficiente, especialmente cuando tu carga de trabajo genera datos históricos a un ritmo acelerado.

La compresión de datos y la depuración por retención hacen que el índice clúster de almacén de columnas sea una buena opción para escenarios en los que la carga de trabajo genera rápidamente una gran cantidad de datos históricos. Ese patrón es típico de cargas de trabajo de procesamiento transaccional intensivo que utilizan tablas temporales para seguimiento y auditoría de cambios, análisis de tendencias o ingesta de datos del Internet de las Cosas (IoT).

La limpieza del índice clúster de almacén de columnas funciona de forma óptima cuando las filas históricas llegan en orden ascendente (ordenadas por la columna de fin de período). Esta condición siempre ocurre cuando solo el SYSTEM_VERSIONING mecanismo llena la tabla de historial. Si las filas de la tabla de historial no están ordenadas por la columna de fin de período (lo que puede ocurrir al migrar datos históricos existentes), vuelva a crear el índice clúster de almacén de columnas encima de un índice de almacenamiento por filas basado en árbol B y correctamente ordenado para lograr un rendimiento óptimo.

Evita reconstruir el índice de almacén de columnas agrupado en una tabla de historial que tenga un periodo de retención finito, porque la reconstrucción podría modificar la ordenación de los grupos de filas que la operación de control de versiones del sistema impone de forma natural. Si necesitas reconstruir el índice agrupado de columnstore en la tabla de historial, recréalo sobre un índice de árbol B compatible para preservar el orden de grupos de filas necesario para la limpieza regular de datos. Adopta el mismo enfoque si creas una tabla temporal con una tabla de historial existente que tiene un índice de almacén de columnas agrupado sin orden garantizado de los datos:

/* Create B-tree ordered by the end-of-period column */
CREATE CLUSTERED INDEX IX_WebsiteUserInfoHistory
    ON WebsiteUserInfoHistory(ValidTo) WITH (DROP_EXISTING = ON);
GO

/* Re-create the clustered columnstore index */
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
    ON WebsiteUserInfoHistory WITH (DROP_EXISTING = ON);

Cuando configuras un periodo de retención finito para una tabla de historial con un índice clustered columnstore, no puedes crear índices adicionales de árbol B no agrupados en esa tabla:

CREATE NONCLUSTERED INDEX IX_WebHistNCI
    ON WebsiteUserInfoHistory(UserName);

La afirmación anterior falla con el siguiente error:

Msg 13772, Level 16, State 1
Cannot create non-clustered index on a temporal history table 'WebsiteUserInfoHistory' since it has finite retention period and clustered columnstore index defined.

Consulta de tablas con directiva de retención

Todas las consultas en la tabla temporal filtran automáticamente las filas históricas que coinciden con la política de retención finita, para evitar resultados impredecibles e inconsistentes. La tarea de limpieza elimina filas antiguas en cualquier momento y en orden arbitrario.

La siguiente captura de pantalla muestra el plan de consulta para una consulta básica. Este ejemplo supone un período de retención de MONTH una unidad en la tabla WebsiteUserInfo:

SELECT *
FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

El plan de consulta incluye un filtro adicional en la columna de fin de periodo (ValidTo) en el operador Escaneo de Índice Agrupado (resaltado en la imagen siguiente) en la tabla de historial.

Si consultas directamente la tabla de historial, podrías ver filas más antiguas que el periodo de retención especificado, pero sin ninguna garantía de resultados repetibles. La siguiente captura de pantalla muestra el plan de consulta para una consulta en la tabla de historial sin filtros adicionales:

No te fíes de la lógica de negocio que lee la tabla de historial más allá del periodo de retención, porque podrías obtener resultados inconsistentes o inesperados. Utiliza consultas temporales con la FOR SYSTEM_TIME cláusula para analizar datos en tablas temporales.

Consideraciones de restauración en un momento específico

Cuando restauras una base de datos a un punto específico en el tiempo, la nueva base de datos tiene la retención temporal deshabilitada a nivel de base de datos (is_temporal_history_retention_enabled configurado en OFF). Este comportamiento te permite inspeccionar filas históricas anteriores al periodo de retención antes de que la tarea de limpieza las elimine. Para reanudar la limpieza automática en la base de datos restaurada, vuelve TEMPORAL_HISTORY_RETENTION a ON.

Note

Una base de datos creada en el nivel Premium en Azure SQL Database conserva copias de seguridad hasta 35 días, así que puedes restaurarla a un momento determinado en cualquier momento de esa ventana. Para una tabla temporal con un periodo de retención de un mes, eso te permite inspeccionar filas históricas de hasta 65 días consultando la tabla de historial directamente en la base de datos restaurada.

Utilice el particionamiento de tablas

Las tablas con particiones y los índices pueden hacer que las tablas sean más escalables y fáciles de administrar. Mediante el enfoque de partición de tablas, puedes implementar una limpieza de datos personalizada o un archivado sin conexión en función de un criterio temporal. La partición de tablas también ofrece ventajas de rendimiento al consultar tablas temporales sobre un subconjunto del historial de datos, mediante la eliminación de particiones.

Use la partición de la tabla para implementar una ventana deslizante que retire de la tabla de historial la parte más antigua de los datos históricos y mantenga constante, en función de la antigüedad, el tamaño de la parte conservada. Una ventana deslizante mantiene los datos en la tabla de historial equivalentes al periodo de retención requerido. La tabla de historial permite sustituir datos mientras SYSTEM_VERSIONING está ON, lo que significa que puedes depurar una parte de los datos del historial sin introducir una ventana de mantenimiento ni bloquear tus cargas de trabajo habituales.

Note

Para realizar el cambio de particiones, tu índice agrupado en la tabla de historial debe estar alineado con el esquema de particionamiento (debe contener ValidTo). La tabla de historial predeterminada contiene un índice agrupado que incluye las columnas ValidTo y ValidFrom, lo cual es óptimo para la partición, la inserción de nuevos datos históricos y las consultas temporales habituales. Para más información, consulte Tablas temporales.

Una ventana deslizante requiere dos conjuntos de tareas:

• Una tarea de configuración de partición
• Tareas periódicas de mantenimiento de partición

Para esta ilustración, suponga que quieres conservar los datos históricos durante seis meses y que quieres conservar cada mes de datos en una partición separada. Además, suponga que activaste el sistema de versiones en septiembre de 2023.

Una tarea de configuración de particiones crea la configuración inicial de partición de la tabla de historial. Para este ejemplo, creas el mismo número de particiones que el tamaño de la ventana deslizante, en meses, más una partición vacía extra. Esta configuración garantiza que el sistema pueda almacenar los nuevos datos correctamente cuando inicias la tarea recurrente de mantenimiento de particiones. También garantiza que nunca se dividan particiones que contienen datos, lo que evita movimientos costosos de datos. Definamos la función de partición con RANGE LEFT en lugar de RANGE RIGHT. Para más información, véase consideraciones sobre rendimiento con particionamiento de tablas más adelante en este artículo.

La siguiente imagen muestra la configuración inicial de particionamiento para conservar seis meses de datos.

La primera y la última partición están abiertas en los límites inferior y superior, respectivamente, para asegurar que cada nueva fila tenga una partición de destino independientemente del valor en la columna de partición. Con el tiempo, las nuevas filas de la tabla de historial se incorporan a particiones más altas. Cuando se llena la sexta partición, se alcanza el periodo de retención previsto. En este punto, inicia la tarea recurrente de mantenimiento de la partición por primera vez. Programalo para que se ejecute periódicamente, una vez al mes en este ejemplo.

La siguiente imagen ilustra las tareas recurrentes de mantenimiento de particiones.

Cada ejecución de la tarea de mantenimiento recurrente realiza los siguientes pasos:

1. SWITCH OUT: Crea una tabla de staging y luego cambia una partición entre la tabla de historial y la tabla de staging usando la ALTER TABLE sentencia con el SWITCH PARTITION argumento.

   ALTER TABLE [<history table>]
       SWITCH PARTITION 1 TO [<staging table>];
   

   Después del cambio de partición, puedes archivar opcionalmente los datos de la tabla de staging y luego eliminar o truncar la tabla staging para prepararte para el siguiente ciclo de mantenimiento.

2. MERGE RANGE: Fusiona la partición 1 vacía con partición 2 usando la ALTER PARTITION FUNCTION sentencia con MERGE RANGE. Cuando usas esta función para eliminar el límite más bajo, efectivamente fusionas la partición 1 vacía con la anterior 2 para formar una nueva 1partición . Las demás particiones también cambian de forma efectiva sus ordinales.

3. SPLIT RANGE: Crea una nueva partición 7 vacía usando la ALTER PARTITION FUNCTION sentencia con SPLIT RANGE. Cuando usas esta función para añadir un nuevo límite superior, creas efectivamente una partición separada para el mes siguiente.

Uso de Transact-SQL para crear particiones en la tabla de historial

Utiliza el siguiente script Transact-SQL para crear la función de partición, el esquema de partición y volver a crear el índice clúster para que quede alineado con el esquema de partición. En este ejemplo, crearás una ventana deslizante de seis meses con particiones mensuales a partir de septiembre de 2023.

BEGIN TRANSACTION;

/*Create partition function*/
CREATE PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo](DATETIME2 (7))
    AS RANGE LEFT FOR VALUES (
        N'2023-09-30T23:59:59.999',
        N'2023-10-31T23:59:59.999',
        N'2023-11-30T23:59:59.999',
        N'2023-12-31T23:59:59.999',
        N'2024-01-31T23:59:59.999',
        N'2024-02-29T23:59:59.999'
    );

/*Create partition scheme*/
CREATE PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo]
    AS PARTITION [fn_Partition_DepartmentHistory_By_ValidTo]
    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] (
    ValidTo ASC,
    ValidFrom 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_ValidTo] (ValidTo);

COMMIT TRANSACTION;

Utilizar Transact-SQL para mantener particiones en el escenario de ventana deslizante

Utiliza el siguiente script de Transact-SQL para mantener las particiones en el escenario de ventana deslizante. Para este ejemplo, cambias la partición de septiembre de 2023 usando MERGE RANGE, y luego añades una nueva partición para marzo de 2024 usando SPLIT RANGE.

BEGIN TRANSACTION;

/* (1) Create staging table */
CREATE TABLE [dbo].[staging_DepartmentHistory_September_2023]
(
    DeptID INT NOT NULL,
    DeptName VARCHAR (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 (7) NOT NULL,
    ValidTo DATETIME2 (7) NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

/* (2) Create index on the same filegroups as the partition to switch out */
CREATE CLUSTERED INDEX [ix_staging_DepartmentHistory_September_2023]
ON [dbo].[staging_DepartmentHistory_September_2023](
    ValidTo ASC,
    ValidFrom 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 to switch out */
ALTER TABLE [dbo].[staging_DepartmentHistory_September_2023] WITH CHECK
    ADD CONSTRAINT [chk_staging_DepartmentHistory_September_2023_partition_1]
            CHECK (ValidTo <= N'2023-09-30T23:59:59.999');

ALTER TABLE [dbo].[staging_DepartmentHistory_September_2023]
    CHECK CONSTRAINT [chk_staging_DepartmentHistory_September_2023_partition_1];

/* (4) Switch partition to staging table */
ALTER TABLE [dbo].[DepartmentHistory]
    SWITCH PARTITION 1 TO [dbo].[staging_DepartmentHistory_September_2023]
    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_2023];
      DROP TABLE [dbo].[staging_DepartmentHIstory_September_2023];
*/

/* (6) merge range to move lower boundary one month ahead */
ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
    MERGE RANGE (N'2023-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_ValidTo]
    NEXT USED [PRIMARY];

ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
    SPLIT RANGE (N'2024-03-31T23:59:59.999');

COMMIT TRANSACTION;

Sin embargo, la solución óptima es ejecutar regularmente un guion genérico de Transact-SQL cada mes sin modificaciones. Puedes generalizar el script anterior para que actúe según los parámetros proporcionados (el límite inferior que debe fusionarse y el nuevo límite creado por la división de particiones). Para evitar crear una tabla de almacenamiento provisional cada mes, crea una de antemano y reutilízala cambiando la restricción de comprobación para que coincida con la partición que se conmuta hacia fuera. Para más información, consulta cómo automatizar completamente el escenario de ventana deslizante.

Consideraciones de rendimiento con las particiones de tabla

Realice las operaciones MERGE RANGE y SPLIT RANGE de forma que se evite el movimiento de datos, porque este puede causar una penalización significativa del rendimiento. Para obtener más información, consulta Modificación de una función de partición.

Cuando creas la función de partición como RANGE LEFT, los valores especificados son los límites superiores de las particiones. Cuando utilices la opción RANGE RIGHT, los valores especificados son los límites inferiores de las particiones. Cuando utilices 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. Si esa partición no está vacía, MERGE RANGE mueve los datos a la partición resultante.

En el diagrama siguiente se describen las opciones RANGE LEFT y RANGE RIGHT:

En un escenario de ventana deslizante, quite siempre el límite inferior de la partición.

• RANGE LEFT Caso: El límite más bajo de la partición pertenece a la partición 1, que está vacía (tras el cambio de partición), por lo que MERGE RANGE no causa ningún movimiento de datos.

• RANGE RIGHT caso: El límite de partición más bajo pertenece a la partición 2, que no está vacía porque al cambiar solo se vacía la partición 1. En este caso, MERGE RANGE provoca el movimiento de datos, moviendo datos de 2 partición en partición 1. Para evitar este movimiento de datos, RANGE RIGHT en el escenario de la ventana deslizante se necesita tener partición 1, que siempre está vacía. Este requisito significa que si usas RANGE RIGHT, deberías crear y mantener una partición extra en comparación con el RANGE LEFT caso.

Conclusión: La gestión de particiones es más sencilla cuando se usa RANGE LEFT en una partición deslizante y evita el movimiento de datos. Pero la definición de los límites de partición con RANGE RIGHT es ligeramente más sencilla, ya que no tiene que tratar con problemas de comprobación de fecha y hora.

Utiliza un script de limpieza personalizado

Cuando no hay una política de retención disponible para tu tabla y la partición de tablas no es viable, puedes eliminar los datos de la tabla de historial usando un script de limpieza personalizado. Este proceso solo es posible cuando SYSTEM_VERSIONING = OFF. Para evitar inconsistencias en los datos, realiza la limpieza durante una ventana de mantenimiento (cuando las cargas de trabajo que modifican datos no están activas) o dentro de una transacción (bloqueando efectivamente otras cargas de trabajo). Esta operación requiere el permiso CONTROL en las tablas actuales y de historial.

La lógica de limpieza es la misma para todas las tablas temporales, así que puedes automatizarla mediante un procedimiento almacenado genérico. Usa Agente SQL Server o otra herramienta diferente para programar ese procedimiento para que se ejecute cada día, iterando sobre cada tabla temporal para la que quieras limitar el historial de datos.

El siguiente diagrama ilustra cómo organizar la lógica de limpieza para una sola tabla y así reducir el efecto sobre las cargas de trabajo en ejecución.

Aquí tienes algunas directrices generales para implementar el proceso:

• Elimina los datos históricos de cada tabla temporal en varias iteraciones de pequeños fragmentos. Empieza por las filas más antiguas y pasa a la más reciente. Evita eliminar todas las filas de una sola transacción, como muestra el diagrama anterior. Aunque ningún tamaño de fragmento único funciona para todos los escenarios, eliminar más de 10.000 filas en una sola transacción podría suponer una penalización significativa.

• Implementa cada iteración como una invocación de un procedimiento almacenado genérico, que elimina una parte de los datos de la tabla de historial.

• Calcule el número de filas que debe eliminar para una tabla temporal individual cada vez que se invoca el proceso. En función del resultado y del número de iteraciones que quieras, determina puntos de división dinámicos para cada invocación de procedimiento.

• Planifica un retraso entre iteraciones para una sola tabla, para reducir el efecto en las aplicaciones que acceden a la tabla temporal.

El siguiente procedimiento almacenado elimina los datos de una sola tabla temporal. Descubre la tabla de historial y la columna de fin de periodo a partir de las vistas de catálogo, y luego ejecuta tres sentencias dentro de una transacción: SET SYSTEM_VERSIONING = OFF, DELETE FROM <history_table>, y SET SYSTEM_VERSIONING = ON. Revisa este código detenidamente y agítalo antes de aplicarlo en tu entorno.

En SQL Server 2016 (13.x), los dos primeros pasos se deben ejecutar en instrucciones EXECUTE independientes o SQL Server genera un error similar al del ejemplo siguiente:

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>'.

DROP PROCEDURE IF EXISTS usp_CleanupHistoryData;
GO

CREATE PROCEDURE usp_CleanupHistoryData (
    @temporalTableSchema SYSNAME,
    @temporalTableName SYSNAME,
    @cleanupOlderThanDate DATETIME2
)
AS
DECLARE @disableVersioningScript AS NVARCHAR (MAX) = '';
DECLARE @deleteHistoryDataScript AS NVARCHAR (MAX) = '';
DECLARE @enableVersioningScript AS NVARCHAR (MAX) = '';
DECLARE @historyTableName AS SYSNAME;
DECLARE @historyTableSchema AS SYSNAME;
DECLARE @periodColumnName AS 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 = s2.name,
           @period_col_nm = c.name
    FROM sys.tables AS t1
         INNER JOIN sys.tables AS t2
             ON t1.history_table_id = t2.object_id
         INNER JOIN sys.schemas AS s1
             ON t1.schema_id = s1.schema_id
         INNER JOIN sys.schemas AS s2
             ON t2.schema_id = s2.schema_id
         INNER JOIN sys.periods AS p
             ON p.object_id = t1.object_id
         INNER JOIN sys.columns AS c
             ON p.end_column_id = c.column_id
            AND c.object_id = t1.object_id
    WHERE t1.name = @tblName
          AND s1.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;

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 TRANSACTION;
    EXECUTE (@disableVersioningScript);
    EXECUTE (@deleteHistoryDataScript);
    EXECUTE (@enableVersioningScript);
COMMIT TRANSACTION;

Contenido relacionado

• Tablas temporales
• Primeros pasos con las tablas temporales con control de versiones del sistema
• Comprobaciones de coherencia del sistema de la tabla temporal
• Creación de particiones con tablas temporales
• Consideraciones y limitaciones de las tablas temporales
• Seguridad de la tabla temporal
• Tablas temporales versionadas por el sistema con tablas optimizadas para la memoria
• Funciones y vistas de metadatos de la tabla temporal