Tablas temporalesTemporal Tables

SE APLICA A: síSQL Server síAzure SQL Database noAzure Synapse Analytics (SQL DW) noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

En SQL Server 2016 se presentó la compatibilidad con las tablas temporales (también denominadas tablas temporales con versión del sistema) como función de base de datos que incorpora compatibilidad integrada para proporcionar información sobre los datos almacenados en la tabla en cualquier momento, en vez de solo los datos que son correctos en la actualidad.SQL Server 2016 introduced support for temporal tables (also known as system-versioned temporal tables) as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. La característica temporal de las bases de datos surgió con ANSI SQL 2011.Temporal is a database feature that was introduced in ANSI SQL 2011.

Inicio rápidoQuick Start

¿Qué es una tabla temporal con versión del sistema?What is a system-versioned temporal table?

Una tabla temporal con versión del sistema es un tipo de tabla de usuario pensada para conservar un historial completo de los cambios de datos y para facilitar los análisis en un momento específico.A system-versioned temporal table is a type of user table designed to keep a full history of data changes and allow easy point in time analysis. Este tipo de tabla temporal se conoce como tabla temporal con versión del sistema, porque el período de validez de cada fila se administra por medio del sistema (es decir, del motor de base de datos).This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine).

Cada tabla temporal tiene dos columnas definidas explícitamente, cada una con un tipo de datos datetime2 .Every temporal table has two explicitly defined columns, each with a datetime2 data type. Estas columnas se conocen como columnas de periodo.These columns are referred to as period columns. 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.These period columns are used exclusively by the system to record period of validity for each row whenever a row is modified.

Aparte de estas columnas, una tabla temporal contiene también una referencia a otra tabla con un esquema reflejado.In addition to these period columns, a temporal table also contains a reference to another table with a mirrored schema. El sistema usa esta tabla para almacenar automáticamente la versión anterior de una fila de la tabla temporal cada vez que dicha fila se actualiza o elimina.The system uses this table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted. Esta tabla adicional se conoce como tabla de historial, mientras que la tabla principal que almacena las versiones de fila actuales (reales) se conoce como tabla actual o, simplemente, como tabla temporal.This additional table is referred to as the history table, while the main table that stores current (actual) row versions is referred to as the current table or simply as the temporal table. Durante la creación de una tabla temporal, los usuarios pueden especificar una tabla de historial existente (debe admitir esquemas) o dejar que el sistema cree una tabla de historial predeterminada.During temporal table creation users can specify existing history table (must be schema compliant) or let system create default history table.

¿Por qué temporal?Why 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.Real data sources are dynamic and more often than not business decisions rely on insights that analysts can get from data evolution. Estos son los casos de uso de tablas temporales:Use cases for temporal tables include:

  • Realizar una auditoría de todos los cambios de datos y realizar análisis forenses de datos cuando sea necesarioAuditing all data changes and performing data forensics when necessary

  • Reconstruir el estado de los datos a partir de cualquier momento en el pasadoReconstructing state of the data as of any time in the past

  • Calcular las tendencias en el tiempoCalculating trends over time

  • Mantener una dimensión de variación lenta para aplicaciones de apoyo de decisionesMaintaining a slowly changing dimension for decision support applications

  • Recuperarse de cambios accidentales de datos y errores de aplicaciónRecovering from accidental data changes and application errors

¿Cómo funciona la característica temporal?How does temporal work?

La versión del sistema de una tabla se implementa como un par de tablas: una tabla actual y una tabla de historial.System-versioning for a table is implemented as a pair of tables, a current table and a history table. Dentro de cada una de estas tablas, se usan las dos columnas datetime2 adicionales siguientes para definir el período de validez de cada fila:Within each of these tables, the following two additional datetime2 columns are used to define the period of validity for each row:

  • Columna de inicio del período: el sistema registra la hora de inicio de la fila en esta columna, que normalmente se denomina SysStartTime.Period start column: The system records the start time for the row in this column, typically denoted as the SysStartTime column.

  • Columna de fin del período: El sistema registra la hora de fin de la fila en esta columna, que normalmente se denomina SysEndTime.Period end column: The system records the end time for the row in this column, typically denoted as the SysEndTime column.

La tabla actual contiene el valor actual de cada fila.The current table contains the current value for each row. La tabla de historial contiene cada valor anterior de cada fila, si existe, y las horas de inicio y fin del periodo de validez.The history table contains each previous value for each row, if any, and the start time and end time for the period for which it was valid.

Temporal: cómo funcionaTemporal-HowWorks

En este sencillo ejemplo se ilustra un escenario con información sobre empleados en una hipotética base de datos de recursos humanos:The following simple example illustrates a scenario with Employee information in hypothetical HR database:

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 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END  
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)  
 )    
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));  

Instrucciones INSERT: en una instrucción INSERT, el sistema establece el valor de la columna SysStartTime 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 columna SysEndTime al valor máximo de 31-12-9999.INSERTS: On an INSERT, the system sets the value for the SysStartTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock and assigns the value for the SysEndTime column to the maximum value of 9999-12-31. Esto marca la fila como abierta.This marks the row as open.

Instrucciones UPDATE: en una instrucción UPDATE, el sistema almacena el valor anterior de la fila en la tabla de historial y establece el valor de la columna SysEndTime en la hora de inicio de la transacción actual (en la zona horaria UTC) según el reloj del sistema.UPDATES: On an UPDATE, the system stores the previous value of the row in the history table and sets the value for the SysEndTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock. Esto marca la fila como cerrada, con un periodo registrado durante el que la fila fue válida.This marks the row as closed, with a period recorded for which the row was valid. En la tabla actual, la fila se actualiza con su nuevo valor y el sistema establece el valor de la columna SysStartTime en la hora de inicio de la transacción actual (en la zona horaria UTC) según el reloj del sistema.In the current table, the row is updated with its new value and the system sets the value for the SysStartTime column to the begin time for the transaction (in the UTC time zone) based on the system clock. El valor de la fila actualizada en la tabla actual para la columna SysEndTime sigue siendo el valor máximo de 9999-12-31.The value for the updated row in the current table for the SysEndTime column remains the maximum value of 9999-12-31.

Operaciones DELETE: en una operación DELETE, el sistema almacena el valor anterior de la fila en la tabla de historial y establece el valor de la columna SysEndTime en la hora de inicio de la transacción actual (en la zona horaria UTC) según el reloj del sistema.DELETES: On a DELETE, the system stores the previous value of the row in the history table and sets the value for the SysEndTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock. Esto marca la fila como cerrada, con un periodo registrado durante el que la fila anterior fue válida.This marks the row as closed, with a period recorded for which the previous row was valid. En la tabla actual, la fila se quita.In the current table, the row is removed. Las consultas de la tabla actual no devolverán esa fila.Queries of the current table will not return this row. Solo las consultas que tengan que ver con los datos de historial devolverán datos relativos a una fila cerrada.Only queries that deal with history data return data for which a row is closed.

Instrucción MERGE: en una instrucción MERGE, la operación se comporta exactamente igual que si se ejecutaran hasta tres instrucciones (INSERT, UPDATE y/o DELETE), en función de lo que se haya especificado como acción en la instrucción MERGE.MERGE: On a MERGE, the operation behaves exactly as if up to three statements (an INSERT, an UPDATE, and/or a DELETE) executed, depending on what is specified as actions in the MERGE statement.

Importante

Las horas registradas en las columnas datetime2 del sistema se basan en la hora de inicio de la propia transacción.The times recorded in the system datetime2 columns are based on the begin time of the transaction itself. Por ejemplo, todas las filas insertadas en una única transacción tendrán la misma hora UTC registrada en la columna correspondiente al inicio del período SYSTEM_TIME .For example, all rows inserted within a single transaction will have the same UTC time recorded in the column corresponding to the start of the SYSTEM_TIME period.

¿Cómo se consultan los datos temporales?How do I query temporal data?

La cláusula FROM <tabla> de la instrucción SELECT tiene una nueva cláusula FOR SYSTEM_TIME con cinco subcláusulas temporales específicas con las que se pueden consultar datos de la tabla actual y las tablas históricas.The SELECT statement FROM<table> clause has a new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses to query data across the current and history tables. 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.This new SELECT statement syntax is supported directly on a single table, propagated through multiple joins, and through views on top of multiple temporal tables.

Temporal: consultarTemporal-Querying

La siguiente consulta busca versiones de fila para la fila Employee con EmployeeID = 1000 que estaban activas al menos durante una parte del período comprendido entre el 1 de enero de 2014 y el 1 de enero de 2015 (incluido el límite superior):The following query searches for row versions for Employee row with EmployeeID = 1000 that were active at least for a portion of period between 1st January of 2014 and 1st January 2015 (including the upper boundary):

SELECT * FROM Employee   
    FOR SYSTEM_TIME    
        BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'   
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;  

Nota

FOR SYSTEM_TIME filtra las filas que tienen un período de validez con una duración cero (SysStartTime = SysEndTime).FOR SYSTEM_TIME filters out rows that have period of validity with zero duration (SysStartTime = SysEndTime).
Estas filas se generarán si realiza varias actualizaciones en la misma clave principal en la misma transacción.Those rows will be generated if you perform multiple updates on the same primary key within the same transaction.
En tal caso, las consultas temporales muestran solo las versiones de fila antes de las transacciones y las que pasaron a ser reales después de las transacciones.In that case, temporal querying surfaces only row versions before the transactions and ones that became actual after the transactions.
Si necesita incluir esas filas en el análisis, consulte directamente la tabla de historial.If you need to include those rows in the analysis, query the history table directly.

En la siguiente tabla, SysStartTime en la columna Filas certificadas representa el valor reflejado en la columna SysStartTime de la tabla que se está consultando y SysEndTime , el valor reflejado en la columna SysEndTime de la tabla que se está consultando.In the table below, SysStartTime in the Qualifying Rows column represents the value in the SysStartTime column in the table being queried and SysEndTime represents the value in the SysEndTime column in the table being queried. Para obtener la sintaxis completa y ejemplos, vea FROM (Transact-SQL) y Consulta de los datos de una tabla temporal con control de versiones del sistema.For the full syntax and for examples, see FROM (Transact-SQL) and Querying Data in a System-Versioned Temporal Table.

ExpresiónExpression Filas certificadasQualifying Rows DescripciónDescription
AS OF<date_time>AS OF<date_time> SysStartTime <= date_time AND SysEndTime > date_timeSysStartTime <= date_time AND SysEndTime > date_time Devuelve una tabla con filas que contienen los valores que fueron reales (actuales) en el momento determinado especificado en el pasado.Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past. Internamente, se realiza una unión entre la tabla temporal y su tabla de historial y 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> .Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values in the row that was valid at the point in time specified by the <date_time> parameter. 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> .The value for a row is deemed valid if the system_start_time_column_name value is less than or equal to the <date_time> parameter value and the system_end_time_column_name value is greater than the <date_time> parameter value.
FROM<start_date_time>TO<end_date_time>FROM<start_date_time>TO<end_date_time> SysStartTime < end_date_time AND SysEndTime > start_date_timeSysStartTime < end_date_time AND SysEndTime > 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 ser activas antes del valor del parámetro <start_date_time> en el argumento FROM o si dejaron de serlo después del valor del parámetro <end_date_time> en el argumento TO.Returns a table with the values for all row versions that were active within the specified time range, regardless of whether they started being active before the <start_date_time> parameter value for the FROM argument or ceased being active after the <end_date_time> parameter value for the TO argument. Internamente, se realiza una unión entre la tabla temporal y su tabla de historial y 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.Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values for all row versions that were active at any time during the time range specified. No se incluyen las filas que han dejado de ser activas justamente en el límite inferior definido por el punto de conexión FROM ni tampoco aquellas que se han activado exactamente en el límite superior definido por el punto de conexión TO.Rows that ceased being active exactly on the lower boundary defined by the FROM endpoint are not included and records that became active exactly on the upper boundary defined by the TO endpoint are not included also.
BETWEEN<start_date_time>AND<end_date_time>BETWEEN<start_date_time>AND<end_date_time> SysStartTime <= end_date_time AND SysEndTime > start_date_timeSysStartTime <= end_date_time AND SysEndTime > start_date_time Igual que la descripción anterior de FOR SYSTEM_TIME FROM <start_date_time>TO <end_date_time>, salvo que la tabla de filas devuelta incluye las filas que se activaron en el límite superior definido por el punto de conexión <end_date_time>.Same as above in the FOR SYSTEM_TIME FROM <start_date_time>TO <end_date_time> description, except the table of rows returned includes rows that became active on the upper boundary defined by the <end_date_time> endpoint.
CONTAINED IN (<start_date_time> , <end_date_time>)CONTAINED IN (<start_date_time> , <end_date_time>) SysStartTime >= start_date_time AND SysEndTime <= end_date_timeSysStartTime >= start_date_time AND SysEndTime <= end_date_time Devuelve una tabla con los valores de todas las versiones de fila que se abrieron y cerraron dentro del rango de tiempo especificado definido por los dos valores de fecha y hora en el argumento CONTAINED IN.Returns a table with the values for all row versions that were opened and closed within the specified time range defined by the two datetime values for the CONTAINED IN argument. Se incluyen las filas que se activaron justamente en el límite inferior o que dejaron de estarlo exactamente en el límite superior.Rows that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included.
ALLALL Todas las filasAll rows Devuelve la unión de las filas pertenecientes a la tabla actual y a la tabla de historial.Returns the union of rows that belong to the current and the history table.

Nota

Si quiere, puede ocultar estas columnas de período, de forma que las consultas que no hagan referencia explícitamente a estas columnas de período no devuelvan esas columnas (el escenario de SELECT * FROM <tabla> ).Optionally, you can choose to hide these period columns such that queries that do not explicitly reference these period columns do not return these columns (the SELECT * FROM<table> scenario). Para devolver una columna oculta, basta con hacer referencia explícita a dicha columna en la consulta.To return a hidden column, simply explicitly refer to the hidden column in the query. 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 rellenarán automáticamente).Similarly INSERT and BULK INSERT statements will continue as if these new period columns were not present (and the column values will be auto-populated). Para obtener más información sobre cómo usar la cláusula HIDDEN , vea CREATE TABLE (Transact-SQL) y ALTER TABLE (Transact-SQL).For details on using the HIDDEN clause, see CREATE TABLE (Transact-SQL) and ALTER TABLE (Transact-SQL).

Consulte tambiénSee Also

Introducción a las tablas temporales con versión del sistema Getting Started with System-Versioned Temporal Tables
Tablas temporales con control de versiones del sistema con tablas con optimización para memoria System-Versioned Temporal Tables with Memory-Optimized Tables
Escenarios de uso de tablas temporales Temporal Table Usage Scenarios
Limitaciones y consideraciones de las tablas temporales Temporal Table Considerations and Limitations
Administración de la retención de datos históricos en las tablas temporales con versiones del sistema Manage Retention of Historical Data in System-Versioned Temporal Tables
Creación de particiones con tablas temporales Partitioning with Temporal Tables
Comprobaciones de coherencia del sistema de la tabla temporal Temporal Table System Consistency Checks
Seguridad de la tabla temporal Temporal Table Security
Funciones y vistas de metadatos de la tabla temporalTemporal Table Metadata Views and Functions