Tablas temporales
Se aplica a: SQL Server 2016 (13.x) y versiones posteriores
Azure SQL Database
Azure SQL Managed Instance
Las tablas temporales (también conocidas como tablas temporales con control de versiones del sistema) son una característica de base de datos que ofrece soporte integrado para proporcionar información sobre los datos almacenados en la tabla en cualquier momento en el tiempo, en vez de únicamente los datos que son correctos en el momento actual determinado.
Puede ver Introducción a las tablas temporales con versión del sistema y revisar Escenarios de uso de tablas temporales.
¿Qué es una tabla temporal con versión del sistema?
Una tabla temporal con versión del sistema es un tipo de tabla de usuario diseñada para conservar un historial completo de los cambios de datos y facilitar los análisis en un momento específico. Este tipo de tabla temporal se conoce como tabla temporal con control de versiones del sistema, porque el periodo de validez de cada fila lo administra el sistema (es decir, el motor de base de datos).
Cada tabla temporal tiene dos columnas definidas explícitamente, cada una con un tipo de datos datetime2 . Estas columnas se conocen como columnas de período. Estas columnas de periodo son de uso exclusivo por parte del sistema para registrar el período de validez de cada fila cada vez que una fila se modifica. La tabla principal en la que se almacenan los datos actuales se conoce como tabla actual o, simplemente, como tabla temporal.
Además de estas columnas de periodo, una tabla temporal contiene también una referencia a otra tabla con un esquema reflejado, denominada tabla de historial. El sistema usa la tabla de historial para almacenar automáticamente la versión anterior de una fila de la tabla temporal cada vez que esa fila se actualiza o elimina. Durante la creación de tablas temporales, puede especificar una tabla de historial existente (que debe admitir el esquema) o dejar que el sistema cree una predeterminada.
¿Por qué temporal?
Los orígenes de datos reales son dinámicos y, con más frecuencia que las decisiones no empresariales, se basan en la información que los analistas obtienen de la evolución de los datos. Estos son los casos de uso de tablas temporales:
- Realizar una auditoría de todos los cambios de datos y realizar análisis forenses de datos cuando sea necesario
- Reconstruir el estado de los datos a partir de cualquier momento en el pasado
- Calcular las tendencias en el tiempo
- Mantener una dimensión de variación lenta para aplicaciones de apoyo de decisiones
- Recuperarse de cambios accidentales de datos y errores de aplicación
¿Cómo funciona la característica temporal?
La creación de versiones del sistema para una tabla se implementa como un par de tablas: una tabla actual y una tabla de historial. Dentro de cada una de estas tablas, se usan dos columnas datetime2 adicionales para definir el periodo de validez de cada fila:
Columna de inicio del periodo: el sistema registra la hora de inicio de la fila en esta columna, que normalmente se denomina
ValidFrom
.Columna de fin del periodo: el sistema registra la hora de fin de la fila en esta columna, que normalmente se denomina
ValidTo
.
La tabla actual contiene el valor actual de cada fila. La tabla de historial contiene cada valor previo (la versión anterior) de cada fila, si existe, y las horas de inicio y fin del periodo de validez.
En el script siguiente se muestra un escenario con información de empleados:
CREATE TABLE dbo.Employee (
[EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
[Name] NVARCHAR(100) NOT NULL,
[Position] VARCHAR(100) NOT NULL,
[Department] VARCHAR(100) NOT NULL,
[Address] NVARCHAR(1024) NOT NULL,
[AnnualSalary] DECIMAL(10, 2) NOT NULL,
[ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START,
[ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
Para más información, vea Creación de una tabla temporal con versiones del sistema.
Inserta: el sistema establece el valor de la columna
ValidFrom
en la hora de inicio de la transacción actual (en la zona horaria UTC) según el reloj del sistema y asigna el valor de la columnaValidTo
al valor máximo de9999-12-31
. Esto marca la fila como abierta.Actualiza: el sistema almacena el valor anterior del registro en la tabla de historial y establece el valor de la columna
ValidTo
en la hora de inicio de la transacción actual (en la zona horaria UTC) según el reloj del sistema. Esto marca la fila como cerrada, con un periodo registrado durante el que la fila fue válida. En la tabla actual, la fila se actualiza con su nuevo valor y el sistema establece el valor de la columnaValidFrom
en la hora de inicio de la transacción actual (en la zona horaria UTC) según el reloj del sistema. El valor de la fila actualizada en la tabla actual para la columnaValidTo
sigue siendo el valor máximo de9999-12-31
.Elimina: el sistema almacena el valor anterior del registro en la tabla de historial y establece el valor de la columna
ValidTo
en la hora de inicio de la transacción actual (en la zona horaria UTC) según el reloj del sistema. Esto marca la fila como cerrada, con un periodo registrado durante el que la fila anterior fue válida. En la tabla actual, la fila se quita. Las consultas de la tabla actual no devuelven esa fila. Solo las consultas que tengan que ver con los datos de historial devolverán datos relativos a una fila cerrada.Combinar: la operación se comporta exactamente igual que si se ejecutaran hasta tres instrucciones (
INSERT
,UPDATE
, y/oDELETE
), en función de lo que se haya especificado como acción en la instrucciónMERGE
.
Las horas registradas en las columnas datetime2 del sistema se basan en la hora de inicio de la propia transacción. Por ejemplo, todas las filas insertadas en una única transacción tienen la misma hora UTC registrada en la columna correspondiente al inicio del período SYSTEM_TIME
.
Al ejecutar consultas de modificación de datos en una tabla temporal, el Motor de base de datos agrega una fila a la tabla de historial aunque no cambie ningún valor de columna.
¿Cómo se consultan los datos temporales?
La instrucción SELECT ... FROM <table>
tiene una nueva cláusula FOR SYSTEM_TIME
, con cinco subcláusulas temporales específicas para consultar datos de las tablas actual y de historial. Esta nueva sintaxis de la instrucción SELECT
se puede usar directamente en una sola tabla, propagarse por varias combinaciones y por las vistas de varias tablas temporales.
Cuando se realiza una consulta con la cláusula FOR SYSTEM_TIME
mediante una de las cinco subclases, se incluyen datos históricos de la tabla temporal, como se muestra en la imagen siguiente.
La consulta siguiente busca versiones de fila para un empleado con la condición de filtro WHERE EmployeeID = 1000
que hayan estado activas al menos durante una parte del período comprendido entre el 1 de enero de 2021 y el 1 de enero de 2022 (incluido el límite superior):
SELECT * FROM Employee
FOR SYSTEM_TIME
BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
WHERE EmployeeID = 1000 ORDER BY ValidFrom;
FOR SYSTEM_TIME
filtra las filas que tienen un período de validez con una duración cero (ValidFrom = ValidTo
).
Estas filas se generan si realiza varias actualizaciones en la misma clave principal en la misma transacción. En ese caso, las consultas temporales solo devuelven las versiones de fila antes de las transacciones y las filas actuales después de las transacciones.
Si necesita incluir esas filas en el análisis, consulte directamente la tabla de historial.
En la siguiente tabla, ValidFrom
en la columna Filas certificadas representa el valor de la columna ValidFrom
de la tabla que se consulta y ValidTo
el valor de la columna ValidTo
de la tabla que se consulta. Para obtener la sintaxis completa y ejemplos, vea Cláusula FROM más JOIN, APPLY, PIVOT y Consulta de datos en una tabla temporal con control de versiones del sistema.
Expression | Filas certificadas | Nota: |
---|---|---|
AS OF date_time |
ValidFrom <= date_time AND ValidTo > date_time |
Devuelve una tabla con filas que contienen los valores que eran actuales en el momento determinado especificado en el pasado. Internamente, se realiza una unión entre la tabla temporal y su tabla de historial. Los resultados se filtran para devolver los valores de la fila que era válida en el momento determinado especificado por el parámetro date_time. El valor de una fila se considera válido si el valor de system_start_time_column_name es menor o igual que el valor del parámetro date_time y el valor de system_end_time_column_name es mayor que el valor del parámetro date_time. |
FROM start_date_time TO end_date_time |
ValidFrom < end_date_time AND ValidTo > start_date_time |
Devuelve una tabla con los valores de todas las versiones de fila que estaban activas dentro del rango de tiempo especificado, independientemente de si empezaron a estar activas antes del valor del parámetro start_date_time del argumento FROM o si dejaron de estarlo después del valor del parámetro end_date_time del argumento TO . Internamente, se realiza una unión entre la tabla temporal y su tabla de historial. Los resultados se filtran para devolver los valores de todas las versiones de fila que estaban activas en cualquier momento dentro del intervalo de tiempo especificado. No se incluyen las filas que han dejado de estar activas justamente en el límite inferior definido por el punto de conexión FROM , ni tampoco las que se han activado exactamente en el límite superior definido por el punto de conexión TO . |
BETWEEN start_date_time AND end_date_time |
ValidFrom <= end_date_time AND ValidTo > start_date_time |
Igual que la descripción anterior de FOR SYSTEM_TIME FROM start_date_time TO end_date_time, salvo que incluye las filas que se activaron en el límite superior definido por el punto de conexión end_date_time. |
CONTAINED IN (start_date_time, end_date_time) |
ValidFrom >= start_date_time AND ValidTo <= end_date_time |
Devuelve una tabla con los valores de todas las versiones de fila que se han abierto y cerrado dentro del rango de tiempo especificado definido por los dos valores de periodo en el argumento CONTAINED IN . Se incluyen las filas que se activaron justamente en el límite inferior o que dejaron de estarlo exactamente en el límite superior. |
ALL |
Todas las filas | Devuelve la unión de las filas pertenecientes a la tabla actual y a la tabla de historial. |
Ocultación de las columnas de periodo
Puede optar por ocultar las columnas de periodo, de modo que las consultas que no hacen referencia explícitamente a ellas no las devuelvan (por ejemplo, al ejecutar SELECT * FROM <table>
).
Para devolver una columna oculta, debe hacer referencia explícita a esa columna en la consulta. Del mismo modo, las instrucciones INSERT
y BULK INSERT
continuarán como si estas nuevas columnas de periodo no estuvieran presentes (y los valores de columna se rellenan automáticamente).
Para obtener más información sobre cómo usar la cláusula HIDDEN
, consulte CREATE TABLE y ALTER TABLE.
Ejemplos
ASP.NET: vea la aplicación web de ASP.NET Core para obtener información sobre cómo compilar una aplicación temporal mediante tablas temporales.
Base de datos de ejemplo AdventureWorks: descargue la base de datos AdventureWorks para SQL Server, que incluye características de tabla temporal.
Contenido relacionado
- Consideraciones y limitaciones de las tablas temporales
- Administración de la retención de datos históricos en las tablas temporales con versiones del sistema
- Creación de particiones con tablas temporales
- Comprobaciones de coherencia del sistema de la tabla temporal
- Seguridad de la tabla temporal
- Funciones y vistas de metadatos de la tabla temporal
- Trabajo con tablas temporales con control de versiones del sistema optimizadas para memoria
- Creación de una tabla temporal con versión del sistema
- 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
- Introducción a las tablas temporales con control de versiones del sistema
- Tablas temporales con control de versiones del sistema con tablas optimizadas para memoria
- Introducción a las tablas temporales en Azure SQL Database y Azure SQL Managed Instance
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