Este artículo versara sobre cómo realizar consultas de tablas temporales en SQL Server utilizando FOR SYSTEM_TIME clause y sus cuatro subcláusulas AS OF, FROM TO, BETWEEN AND, CONTENED IN. Por otra parte, también se considerará la manera de limpiar la tabla del historial para lograr tener un tamaño manejable.
La cláusula FOR SYSTEM_TIME se utiliza para efectuar cualquier tipo de análisis basado en el tiempo con cuatro subcláusulas y puede emplearse para cada tabla en la consulta de forma independiente. Además, el mismo se puede utilizar dentro de funciones con valores de tabla y procedimientos almacenados.
Previamente para comenzar, vamos a crear una base de datos de SQL Server con una tabla temporal dbo.People. Al iniciar solo puedes copiar y ejecutar el siguiente código en la ventana de consulta:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE DATABASE test; GO USE test; GO CREATE TABLE People( PeopleID int PRIMARY KEY NOT NULL, Name varchar(50) Null, LastName varchar(100) NULL, NickName varchar(25) NULL, StartTime datetime2 (0) GENERATED ALWAYS AS ROW START NOT NULL DEFAULT GETUTCDATE(), EndTime datetime2(0) GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'), PERIOD FOR SYSTEM_TIME (StartTime,EndTime) ) WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory)); |
Esto creará la tabla dbo.People con versiones del sistema y la tabla histórica correspondiente dbo.HistoryPeople:
Para lograr tener más información sobre las tablas temporales de SQL Server, visite Tablas temporales en SQL Server.
Insertaremos y actualizaremos algunos datos:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
INSERT INTO dbo.People VALUES(2,'James','Smith', 'Jam',DEFAULT, DEFAULT) WAITFOR DELAY '00:01:00' UPDATE dbo.People SET dbo.People.Name = 'Thomas' WHERE dbo.People.PeopleID=2 WAITFOR DELAY '00:02:00' INSERT INTO dbo.People VALUES(3,'Joan','Johnson','Jon',DEFAULT, DEFAULT) WAITFOR DELAY '00:01:00' UPDATE dbo.People SET dbo.People.Name = 'Paul' WHERE dbo.People.PeopleID=3 WAITFOR DELAY '00:02:00' INSERT INTO dbo.People VALUES (4,'Robert','Davis', 'Rob',DEFAULT, DEFAULT) WAITFOR DELAY '00:01:00' UPDATE dbo.People SET dbo.People.Name = 'Nik' WHERE dbo.People.PeopleID=4 WAITFOR DELAY '00:02:00' UPDATE dbo.People SET dbo.People.Name = 'Brian' WHERE dbo.People.PeopleID=2 WAITFOR DELAY '00:01:00' UPDATE dbo.People SET dbo.People.Name = 'Mark' WHERE dbo.People.PeopleID=2 |
Tanto la versión del sistema como la tabla de historial se pueden analizar usando el estándar
SELECT * FROM <TableName> consulta de consulta.
Consultando la tabla dbo.People con versión del sistema
Código:
|
1 2 3 |
SELECT * FROM dbo.People |
Resultado:
Esto restituirá todos los datos actuales:
Consultando la tabla dbo.HistoryPeople del historial de SQL Server
Código:
|
1 2 3 |
SELECT * FROM dbo.PeopleHistory |
Resultado:
Esto repondrá todos los cambios de datos (Actualización, Eliminar, Fusionar) que se efectuaron en la tabla versionada del sistema dbo.People:
ALL subcláusula
Esta cláusula restituirá todas las filas de la tabla del historial de dbo.People system versioned y dbo.PeopleHistory.
Escriba el siguiente código:
|
1 2 3 4 |
SELECT * FROM dbo.People FOR SYSTEM_TIME ALL; |
Resultado:
El mismo efecto se puede lograr utilizando la siguiente consulta y omitir la cláusula FOR SYSTEM_TIME:
|
1 2 3 4 5 6 7 |
SELECT * FROM dbo.People p UNION ALL SELECT * FROM dbo.PeopleHistory ph; |
AS FOR sub cláusula
El uso de la subcláusula AS FOR puede restituir un estado de los datos para cada fila que incluye los valores actuales en el momento especificado en el pasado. La subcláusula AS FOR repone todos los registros de la versión del sistema de SQL Server y la tabla de historial que ejecuto con los siguientes criterios:
StartTime <= SpecifiedTime AND EndTime > SpecifiedTime
La siguiente consulta repondrá los registros que eran válidos en un momento específico en el pasado (‘2017-01-26 13:52:29’) para el PeopleID específico = 2:
|
1 2 3 4 5 |
SELECT * FROM dbo.People FOR SYSTEM_TIME AS OF '2017-01-26 13:52:29' WHERE PeopleID=2 |
Resultado:
El mismo resultado se puede obtener escribiendo el siguiente código y eludiendo el uso del FOR SYSTEM_TIME AS OF:
|
1 2 3 4 5 6 7 8 9 |
SELECT* FROM dbo.People p WHERE p.StartTime <= '2017-01-26 13:52:29' AND p.EndTime > '2017-01-26 13:52:29' UNION ALL SELECT ph.* FROM dbo.PeopleHistory ph WHERE ph.StartTime <='2017-01-26 13:52:29' AND ph.EndTime > '2017-01-26 13:52:29' |
Subcláusula DE <fecha_hora_inicio> A <fecha_hora_final>
Esta subcláusula temporal es conveniente cuando se necesita lograr cambios para todos los registros que estaban activos entre < fecha_hora_inicio > y < fecha_hora_final >. La subcláusula FROM … TO … restablece los datos de las dos versiones del sistema de las tablas y el historial.
Se deben acatar los siguientes criterios:
StartTime < <end_date_time> AND EndTime > <start_date_time>
Ejecuta el siguiente código:
|
1 2 3 4 5 6 7 8 9 |
DECLARE @StartDate DATETIME2 = '2017-01-26 13:52:29' DECLARE @EndDate DATETIME2 = '2017-01-26 14:01:29' SELECT * FROM dbo.People FOR SYSTEM_TIME FROM @StartDate TO @EndDate WHERE dbo.People.PeopleID=2 |
Resultado:
El mismo resultado se puede obtener al escribir el siguiente código, sin usar FOR SYSTEM_TIME FROM… TO…
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @StartDate DATETIME2 = '2017-01-26 13:52:29' DECLARE @EndDate DATETIME2 = '2017-01-26 14:01:29' SELECT * FROM dbo.People p WHERE p.StartTime < @EndDate AND p.EndTime > @StartDate AND p.PeopleID=2 UNION ALL SELECT * FROM dbo.PeopleHistory ph WHERE ph.StartTime < @EndDate AND ph.EndTime > @StartDate AND ph.PeopleID=2 |
ENTRE subcláusula <fecha_hora_inicio> AND <fecha_hora_final>
Esto es casi igual que la subcláusula FROM … TO … a excepción que BETWEEN … TO … incluye los registros que estaban activos en <fecha_hora_final> (es decir, StartTime = <fecha_hora_final>). La subcláusula BETWEEN … TO … restituye toda la fila que cumple los siguientes principios:
StartTime <= <fecha_hora_final> AND EndTime > <fecha_hora_inicio>
Ejecuta el siguiente código:
|
1 2 3 4 5 6 7 8 |
DECLARE @StartDate DATETIME2 = '2017-01-26 13:52:29' DECLARE @EndDate DATETIME2 = '2017-01-26 14:01:29' SELECT * FROM dbo.People FOR SYSTEM_TIME BETWEEN @StartDate AND @EndDate WHERE dbo.People.PeopleID=2 |
Resultado:
El mismo resultado se puede obtener usando este código, sin utilizar la subcláusula BETWEEN… TO…
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @StartDate DATETIME2 = '2017-01-26 13:52:29' DECLARE @EndDate DATETIME2 = '2017-01-26 14:01:29' SELECT * FROM dbo.People p WHERE p.StartTime <= @EndDate AND p.EndTime > @StartDate AND p.PeopleID=2 UNION ALL SELECT * FROM dbo.PeopleHistory ph WHERE ph.StartTime <= @EndDate AND ph.EndTime > @StartDate AND ph.PeopleID=2 |
CONTENIDO EN subcláusula <fecha_hora_inicio> <fecha_hora_final>
Esta subcláusula temporal repondrá solo los registros que existían dentro de los límites del período especificado. La subcláusula CONTAINED IN reintegra toda la fila que cumple los siguientes criterios:
StartTime >= <start_date_time> AND EndTime <= <end_date_time>
Ejecuta el siguiente código:
|
1 2 3 4 5 6 |
DECLARE @StartDate DATETIME2 = '2017-01-26 13:52:29' DECLARE @EndDate DATETIME2 = '2017-01-26 14:01:29' SELECT * FROM dbo.People FOR SYSTEM_TIME CONTAINED IN (@StartDate,@EndDate) WHERE dbo.People.PeopleID=2 |
Resultado:
El mismo resultado se puede obtener usando este código:
|
1 2 3 4 5 6 7 |
SELECT * FROM dbo.People p WHERE p.StartTime >= @StartDate AND p.EndTime <= @EndDate AND p.PeopleID=2 UNION ALL SELECT * FROM dbo.PeopleHistory ph WHERE ph.StartTime >= @StartDate AND ph.EndTime <= @EndDate AND ph.PeopleID=2 |
Limpiando la tabla de historial de SQL Server
Con el tiempo la tabla de historia se incrementa significativamente. Dado que no se permite insertar, actualizar o eliminar datos de la tabla de historial, la única forma de limpiar la tabla de historial es deshabilitar primero el versionamiento del sistema:
|
1 2 3 4 5 |
ALTER TABLE dbo.People SET (SYSTEM_VERSIONING = OFF); GO |
Suprimir datos innecesarios de la tabla de historial:
|
1 2 3 4 |
DELETE FROM dbo.PeopleHistory WHERE EndTime <= '2017-01-26 14:00:29'; |
y luego volver a habilitar el control de versiones del sistema:
|
1 2 3 4 |
ALTER TABLE dbo.People SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[PeopleHistory], DATA_CONSISTENCY_CHECK = ON)); |
La limpieza de la tabla de historial en las bases de datos de Azure SQL es distinto, ya que las bases de datos de Azure SQL tienen soporte incorporado para limpiar la tabla de historial. Primero, la limpieza del estancamiento del historial temporal debe habilitarse en un nivel de base de datos:
|
1 2 3 4 5 |
ALTER DATABASE CURRENT SET TEMPORAL_HISTORY_RETENTION ON GO |
A continuación, establezca el período de retención por tabla:
|
1 2 3 4 |
ALTER TABLE dbo.People SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 90 DAYS)); |
Esto eliminará todos los datos en la tabla de historial con más de 90 días.
Las bases de datos locales de SQL Server 2016 no son compatibles con TEMPORAL_HISTORY_RETENTION y HISTORY_RETENTION_PERIOD y cualquiera de estas dos consultas anteriores se ejecutan en las bases de datos locales de SQL Server 2016, se producirán los siguientes errores:
Para TEMPORAL_HISTORY_RETENTION el error será:
Msg 102, Nivel 15, Estado 6, Línea 34
Sintaxis incorrecta cerca de ‘TEMPORAL_HISTORY_RETENTION’.
Para HISTORY_RETENTION_PERIOD el error será:
Mensaje 102, Nivel 15, Estado 1, Línea 39
Sintaxis incorrecta cerca de “HISTORY_RETENTION_PERIOD”.
Actualmente trabajando para ApexSQL LLC como un Ingeniero de Ventas de Software, él está ayudando a los clientes con problemas técnicos y hace aseguramiento de calidad para los complementos ApexSQL Complete, ApexSQL Refactor y ApexSQL Search.
Ver todas las entradas de Marko Zivkovic