Creación de una tabla temporal con versión del sistema

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

Puedes crear una tabla temporal con control de versiones del sistema de tres maneras, dependiendo de cómo especifiques la tabla de historial:

  • Tabla temporal con una tabla de historial anónima: especificas el esquema de la tabla actual y dejas que el sistema cree una tabla de historial correspondiente con un nombre autogenerado.

  • Tabla temporal con una tabla de historial predeterminada: especifique el nombre del esquema de la tabla de historial y el nombre de tabla, y deje que el sistema cree una tabla de historial en ese esquema.

  • Tabla temporal con una tabla de historial definida por el usuario y creada con antelación: cree una tabla de historial que se mejor adapte a la necesidades y, después, haga referencia a ella durante la creación de la tabla temporal.

Creación de una tabla temporal con una tabla de historial anónima

La creación de una tabla temporal con una tabla de historial anónima es una opción práctica para la generación rápida de objetos, especialmente en entornos de prueba y prototipos. También es la forma más sencilla de crear una tabla temporal porque no requiere ningún parámetro en la SYSTEM_VERSIONING cláusula. El siguiente ejemplo crea una nueva tabla con la versión del sistema activada, sin definir el nombre de la tabla de historial.

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

Remarks

Una tabla temporal con versiones del sistema debe tener definida una clave principal y tener especificado exactamente un parámetro PERIOD FOR SYSTEM_TIME con dos columnas datetime2, declaradas como GENERATED ALWAYS AS ROW START o GENERATED ALWAYS AS ROW END.

Siempre se supone que las columnas PERIOD no aceptan valores NULL, aunque no se especifique la nulabilidad. Si se define explícitamente que las columnas PERIOD aceptan valores NULL, la instrucción CREATE TABLE generará un error.

La tabla de historial siempre debe tener el mismo esquema que la tabla temporal o actual, en lo que respecta al número y nombres de columnas, orden, y tipos de datos.

El Motor de base de datos crea automáticamente una tabla de historial anónima en el mismo esquema que la tabla actual o temporal.

El nombre de la tabla de historial anónima tiene el formato siguiente: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_<suffix>. El sufijo es opcional y únicamente se agrega si la primera parte del nombre de la tabla no es único.

La tabla de historial se crea como una tabla de almacenamiento por filas. Si es posible, se aplica la compresión PAGE; en caso contrario, la tabla de historial está descomprimida. Por ejemplo, algunas configuraciones de tabla, como las columnas SPARSE, no permiten la compresión.

Se crea un índice agrupado por defecto para la tabla de historial con un nombre autogenerado en el formato IX_<history_table_name>. El índice agrupado contiene las columnas PERIOD (finalización, inicio).

En la base de datos SQL de Fabric, la tabla de historial creada no se refleja en Fabric OneLake.

Para crear la tabla actual como tabla optimizada para memoria, consulte Tablas temporales con control de versiones del sistema con tablas optimizadas para memoria.

Crear una tabla temporal con una tabla de historial predeterminada

La creación de una tabla temporal con una tabla de historial predeterminada es una opción práctica cuando se quiere controlar la nomenclatura y, aun así, delegar en el sistema la generación de la tabla de historial con la configuración predeterminada. El siguiente ejemplo crea una nueva tabla con el sistema de versiones activado, con el nombre de la tabla de historial explícitamente definido.

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

Remarks

La tabla de historial se crea utilizando las mismas reglas que se aplican a la creación de una tabla de historial "anónima", con las siguientes reglas que se aplican específicamente a la tabla de historial con nombre.

  • El nombre de esquema es obligatorio para el parámetro HISTORY_TABLE.

  • Si el esquema especificado no existe, la instrucción CREATE TABLE genera un error.

  • Si la tabla que especifica el parámetro HISTORY_TABLE ya existe, se valida con la tabla temporal recién creada en lo que respecta a la coherencia del esquema y de los datos temporales. Si especifica una tabla de historial no válida, la instrucción CREATE TABLE genera un error.

Crear una tabla temporal con una tabla de historial definida por el usuario

Crear una tabla temporal con una tabla de historial definida por el usuario es una opción conveniente cuando quieres especificar una tabla de historial con opciones de almacenamiento específicas y diferentes índices ajustados a consultas históricas. En el siguiente ejemplo, creas una tabla de historial definida por el usuario con un esquema alineado con la tabla temporal. Esta tabla de historial tiene un índice de almacén de columnas agrupado y un índice adicional de almacén de filas no agrupado (B-tree) para búsquedas puntuales. Después de crear la tabla de historiales, creas la tabla temporal y especificas la tabla de historial definida por el usuario como la tabla de historial predeterminada.

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.

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

CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory
    ON DepartmentHistory;

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

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

Remarks

Si planeas ejecutar consultas analíticas sobre los datos históricos que utilizan agregados o funciones de ventana, crear un almacén de columnas agrupado como índice principal es muy recomendable para la compresión y el rendimiento de consultas.

Si tiene pensado usar tablas temporales para la auditoría de datos (es decir, buscar cambios históricos de una única fila de la tabla actual), debe crear una tabla de historial del almacén de filas con un índice agrupado.

La tabla de historial no puede tener una clave principal, claves externas, índices únicos, restricciones de tabla ni desencadenadores. No se puede configurar para la captura de datos de cambios, el seguimiento de cambios, ni la replicación transaccional o de mezcla.

En la base de datos SQL de Fabric y en Azure SQL Database con la creación de reflejo de Fabric configurada, cuando se utiliza una tabla existente como tabla de historial durante la creación de una tabla temporal, la tabla existente deja de reflejarse.

Modificar una tabla no temporal para convertirla en una tabla temporal con control de versiones del sistema

Puedes habilitar el sistema de versiones en una tabla no temporal existente, como cuando quieres migrar una solución temporal personalizada a soporte integrado.

Por ejemplo, es posible que tenga un conjunto de tablas en las que el control de versiones está implementado con desencadenadores. El uso del control de versiones del sistema temporal es más simple y ofrece otras ventajas, como las siguientes:

  • Historial inmutable
  • Nueva sintaxis para las consultas de viaje en el tiempo
  • Mejor rendimiento de DML
  • Costos de mantenimiento mínimos

Al convertir una tabla existente, considera usar la HIDDEN cláusula para ocultar las nuevas PERIOD columnas (las columnas datetime2ValidFrom y ValidTo) para evitar afectar a aplicaciones existentes que no especifican explícitamente nombres de columna (por ejemplo, SELECT * o INSERT sin lista de columnas) y que no están diseñadas para manejar nuevas columnas.

Agregar el control de versiones a tablas no temporales

Si quiere iniciar el seguimiento de cambios de una tabla no temporal que contenga datos, debe agregar la definición PERIOD y, opcionalmente, especificar un nombre para la tabla de historial vacía que SQL Server crea automáticamente:

CREATE SCHEMA History;
GO

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

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

Importante

La precisión de DATETIME2 debe alinearse con la precisión de la tabla subyacente.

Remarks

La acción de agregar columnas que no admiten valores NULL con valores predeterminados en una tabla existente con datos es una operación de tamaño de datos en todas las ediciones, excepto SQL Server Enterprise Edition (donde constituye una operación de metadatos). Si cuenta con una gran tabla de historial existente con datos en SQL Server Standard Edition, la adición de una columna que no acepte valores NULL puede constituir una operación costosa.

Las restricciones de las columnas de inicio y finalización del periodo se deben elegir con cautela:

  • El valor predeterminado de la columna de inicio especifica desde qué momento concreto considera que las filas existentes son válidas. No se puede especificar como una fecha y hora futura.

  • La hora de finalización se debe especificar como el valor máximo para una precisión de datetime2 determinada, por ejemplo, 9999-12-31 23:59:59 o 9999-12-31 23:59:59.9999999.

Añadir PERIOD realiza una comprobación de consistencia de datos en la tabla actual para asegurarse de que los valores existentes para las columnas de periodo son válidos.

Cuando se especifica una tabla de historial existente al habilitar SYSTEM_VERSIONING, se realiza una comprobación de coherencia de datos en la tabla actual y en la de historial. Se puede omitir si se especifica DATA_CONSISTENCY_CHECK = OFF como un parámetro adicional.

Migrar las tablas existentes al soporte integrado

En este ejemplo se muestra cómo migrar desde una solución existente basada en desencadenadores a una compatibilidad temporal integrada. Este ejemplo asume que la solución personalizada actual divide los datos actuales e históricos en dos tablas de usuario separadas (ProjectTaskCurrent y ProjectTaskHistory).

Si tu solución existente utiliza una única tabla para almacenar filas reales e históricas, entonces deberías dividir los datos en dos tablas antes de los pasos de migración que se muestran en el siguiente ejemplo. En primer lugar, elimine el desencadenador de la tabla temporal futura. A continuación, asegúrese de que las columnas PERIOD no aceptan valores NULL.

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

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

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

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

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

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

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

Remarks

Si se hace referencia a columnas existentes en la definición PERIOD, se cambiará de forma implícita a generated_always_type, AS_ROW_START y AS_ROW_END para dichas columnas.

Añadir PERIOD realiza una comprobación de consistencia de datos en la tabla actual para asegurarse de que los valores existentes para las columnas de periodo son válidos.

Se recomienda encarecidamente establecer SYSTEM_VERSIONING con DATA_CONSISTENCY_CHECK = ON, para aplicar comprobaciones de coherencia de datos en los datos existentes.

Si se prefieren columnas ocultas, utiliza el siguiente comando:

ALTER TABLE [tableName]
    ALTER COLUMN [columnName] ADD HIDDEN;

Contenido relacionado

  • Last updated on