Compartir vía

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

  • Artículo

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL Database Azure SQL Managed Instance

Existen tres maneras de crear una tabla temporal con versión del sistema en función de cómo se especifica la tabla de historial:

  • Tabla temporal con una tabla de historial anónima: especifique el esquema de la tabla actual y deje que el sistema cree una tabla de historial correspondiente con el nombre generado automáticamente.

  • Tabla temporal con una tabla de historial predeterminada: especifique el nombre del esquema de 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 sus necesidades y, después, haga referencia a ella durante la creación de la tabla temporal.

Crear una tabla temporal con una tabla de historial anónima

Esta opción resulta práctica para la generación rápida de objetos, especialmente en entornos de prueba y prototipos. También es la manera más sencilla de crear una tabla temporal, ya que no necesita ningún parámetro en la cláusula SYSTEM_VERSIONING. En el ejemplo siguiente, se crea una nueva tabla con el control de versiones del sistema habilitado 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);

Comentarios

  • 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, por lo que respecta al número y nombres de columnas, orden, y tipos de datos.

  • Se crea automáticamente una tabla de historial anónima en el mismo esquema que la tabla temporal o actual.

  • El nombre de la tabla de historial anónima tiene el formato siguiente: MSSQL_TemporalHistoryFor_<id_de_objeto_de_tabla_temporal_actual>_[sufijo]. 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 almacén de filas. Si es posible, se aplicará la compresión de página; en caso lo contrario, la tabla de historial está descomprimida. Por ejemplo, algunas configuraciones de tabla, como las columnas dispersas, no permiten la compresión.

  • Se crea un índice agrupado predeterminado para la tabla de historial con un nombre generado automáticamente en formato IX_<nombre_de_tabla_de_historial>. El índice agrupado contiene las columnas PERIOD (finalización, inicio).

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

Crear una tabla temporal con una tabla de historial predeterminada

Esta opción es práctica en casos en los que quiera controlar la nomenclatura y, aun así, delegar en el sistema la generación de la tabla de historial con la configuración predeterminada. En el ejemplo siguiente, se crea una nueva tabla con el control de versiones del sistema habilitado y el nombre de la tabla de historial definido explícitamente.

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

Comentarios

La tabla de historial se crea con las mismas reglas que se aplican a la generación de una tabla de historial "anónima", pero con las siguientes variaciones específicas de esta modalidad.

  • 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 validará 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

Esta modalidad resulta práctica en casos en los que el usuario quiere especificar una tabla de historial con opciones de almacenamiento específicas y distintos índices ajustados a consultas históricas. En el ejemplo siguiente, se crea una tabla de historial definida por el usuario con un esquema acorde con el de la tabla temporal que genera. Para esta tabla de historial definida por el usuario, se crea un índice de almacén de columnas agrupado y un índice de almacén de filas (árbol B) no agrupado adicional orientados a búsquedas de puntos. Después de crear esta tabla de historial definida por el usuario, se genera la tabla temporal con control de versiones del sistema, en la que se especifica la tabla de historial definida por el usuario como la predeterminada.

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

Comentarios

  • Si planea ejecutar consultas analíticas en datos históricos que emplean agregados o funciones basadas en ventanas, se recomienda encarecidamente crear un almacén de columnas agrupado como índice principal para la compresión y el rendimiento de las consultas.
  • Si el caso de uso principal es la auditoría de datos (es decir, buscar cambios históricos de una única fila de la tabla actual), se aconseja 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.

Modificación de una tabla no temporal para convertirla en la tabla temporal con control de versiones del sistema

Puede habilitar el control de versiones del sistema con una tabla existente no temporal, como cuando quiera migrar una solución temporal personalizada a la compatibilidad integrada. Por ejemplo, es posible que cuente con 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 invariable
  • Nueva sintaxis para las consultas a versiones anteriores
  • Mejor rendimiento de DML
  • Costos de mantenimiento mínimos

Al convertir una tabla existente, considere la posibilidad de usar la cláusula HIDDEN para ocultar las nuevas columnas PERIOD (las columnas datetime2, ValidFrom y ValidTo) y, así, evitar repercutir en las aplicaciones existentes que no especifican explícitamente nombres de columna (por ejemplo, SELECT * o INSERT sin lista de columnas) que no estén diseñadas para controlarlas.

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. Consulte los comentarios siguientes.

Comentarios

  • 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 un punto datetime en el futuro.
    • 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.
  • Al agregar PERIOD, se efectuará una comprobación de coherencia de datos en la tabla actual para garantizar la validez de los valores existentes en las columnas de período.
  • 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. Puede omitirse si se especifica DATA_CONSISTENCY_CHECK = OFF como parámetro adicional.

Migración de tablas existentes a la compatibilidad integrada

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

Si su solución existente usa una sola tabla para almacenar las filas reales e históricas, debe dividir los datos en dos tablas antes de realizar los pasos de migración descritos en el siguiente ejemplo: En primer lugar, quite el desencadenador en 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 that future period columns are non-nullable */
ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

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

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

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

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

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

Comentarios

  • 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.
  • Al agregar PERIOD, se efectuará una comprobación de coherencia de datos en la tabla actual para garantizar la validez de los valores existentes en las columnas de período
  • 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 las columnas ocultas, use el comando ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.

Pasos siguientes