Crear una tabla temporal con versión del sistema
Se aplica a:
SQL Server 2016 (13.x) y versiones posteriores Azure SQL DatabaseAzure 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 requiere ningún parámetro en la SYSTEM_VERSIONING cláusula . 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);
Observaciones
Una tabla temporal con versión del sistema debe tener definida una clave principal y tener exactamente una
PERIOD FOR SYSTEM_TIMEdefinida con dos columnas datetime2 , declaradas comoGENERATED ALWAYS AS ROW STARToGENERATED ALWAYS AS ROW END.Siempre se supone que las
PERIODcolumnas no aceptan valores NULL, incluso si no se especifica la nulabilidad. Si lasPERIODcolumnas se definen explícitamente como que aceptan valores NULL, se produce un error en laCREATE TABLEinstrucción .La tabla de historial siempre debe estar alineada con el esquema con la tabla actual o temporal, con respecto al número de columnas, nombres de columna, ordenación 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 solo se agrega si la primera parte del nombre de la tabla no es única.
La tabla de historial se crea como una tabla de almacén de filas. La compresión PAGE se aplica si es posible; de 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
PERIODcolumnas (end, start).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 con el nombre de la tabla de historial definida 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));
Observaciones
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 del esquema es obligatorio para el
HISTORY_TABLEparámetro . - Si el esquema especificado no existe, se produce un error en la
CREATE TABLEinstrucción . - Si la tabla especificada por el
HISTORY_TABLEparámetro ya existe, se valida con la tabla temporal recién creada en términos de coherencia de esquema y coherencia de datos temporales. Si especifica una tabla de historial no válida, se produce un error en laCREATE TABLEinstrucción .
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 alineado con la tabla temporal que se crea. 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));
Observaciones
- 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 en una tabla no temporal existente, como cuando desee migrar una solución temporal personalizada a 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 HIDDEN cláusula para ocultar las nuevas PERIOD columnas (las columnas ValidFromdatetime2 y ValidTo) para evitar que afecten a las aplicaciones existentes que no especifican explícitamente nombres de columna (por ejemplo, SELECT * o INSERT sin lista de columnas) no están diseñados para controlar nuevas columnas.
Agregar el control de versiones a tablas no temporales
Si desea iniciar el seguimiento de los cambios de una tabla no temporal que contiene los datos, debe agregar la PERIOD definición y, opcionalmente, proporcionar 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. Vea los comentarios siguientes.
Observaciones
- Agregar columnas que no aceptan valores NULL con valores predeterminados a una tabla existente con datos es un tamaño de operación de datos en todas las ediciones distintas de SQL Server Enterprise edición (en la que se trata de una operación de metadatos). Con una tabla de historial existente grande con datos en SQL Server Standard edición, agregar una columna que no sea NULL puede ser 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:59o9999-12-31 23:59:59.9999999.
- Agregar
PERIODrealiza una comprobación de coherencia de datos en la tabla actual para asegurarse de que los valores existentes para las columnas de período son válidos. - Cuando se especifica una tabla de historial existente al habilitar
SYSTEM_VERSIONING, se realiza una comprobación de coherencia de datos tanto en la tabla actual como en la tabla de historial. Se puede omitir si especificaDATA_CONSISTENCY_CHECK = OFFcomo 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 PERIOD columnas 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
)
);
Observaciones
- Hacer referencia a las columnas existentes en la
PERIODdefinición cambiagenerated_always_typeimplícitamente aAS_ROW_STARTyAS_ROW_ENDpara esas columnas. - Agregar
PERIODrealiza una comprobación de coherencia de datos en la tabla actual para asegurarse de que los valores existentes para las columnas de período son válidos. - Se recomienda encarecidamente establecer
SYSTEM_VERSIONINGconDATA_CONSISTENCY_CHECK = ONpara 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 con tablas temporales con versiones del sistema
- Administración de la retención de datos históricos en 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 datos en una tabla temporal con versiones del sistema
- Consulta de datos en una tabla temporal con versiones del sistema
- Cambio del esquema de una tabla temporal con versiones del sistema
- Detener el control de versiones del sistema en una tabla temporal con versiones del sistema
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de