Creación de una tabla temporal con versión del sistema
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 comoGENERATED ALWAYS AS ROW START
oGENERATED 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 columnasPERIOD
aceptan valores NULL, la instrucciónCREATE 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ónCREATE 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
o9999-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 especificaDATA_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 agenerated_always_type
,AS_ROW_START
yAS_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
conDATA_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
- Tablas temporales
- Introducción a las tablas temporales con versión del sistema
- Administración de la retención de datos históricos en las tablas temporales con versiones del sistema
- Tablas temporales con control de versiones del sistema con tablas con optimización para memoria
- CREATE TABLE (Transact-SQL)
- Modificación de los datos de una tabla temporal con control de versiones del sistema
- Consulta de los datos de una tabla temporal con control de versiones del sistema
- Cambio del esquema de una tabla temporal con control de versiones del sistema
- Detención del control de versiones en una tabla temporal con control de versiones del sistema
Comentaris
https://aka.ms/ContentUserFeedback.
Properament: al llarg del 2024 eliminarem gradualment GitHub Issues com a mecanisme de retroalimentació del contingut i el substituirem per un nou sistema de retroalimentació. Per obtenir més informació, consulteu:Envieu i consulteu els comentaris de