[SQL SERVER 2016] System-Versioned Temporal Tables

A partir de Sql Server 2016 se ha incluido la opción System-Versioned Temporal Tables, la cual genera un historico completo de los cambios realizados a los datos de una tabla. Este característica no hay que confundirla con las tablas temporales (tablas usadas para almacenar datos de forma provisional).

Para poder aplicar System-Versioned Temporal Tables sobre una tabla es necesario:

  • Tener definida una clave primaria
  • Tener dos columnas de tipo datetime2.
  • Añadir la clausula GENERATED ALWAYS AS ROW [START/END] a las 2 columnas datetime2
  • Incluir la clausula PERIOD FOR SYSTEM_TIME
  • Incluir la opcion WITH (SYSTEM_VERSIONING = ON). Esta opción hace que Sql Server cree automaticamente la tabla de historico y le asigne un nombre siguiendo el formato MSSQL_TemporalHistoryFor_[VALOR ALEATORIO]

En el siguiente fragmento sql, se puede ver un ejemplo de creación

CREATE TABLE CLIENTES 
(
	Id int not null Identity(1,1) CONSTRAINT PK_CLIENTES PRIMARY KEY,
	Nombre nvarchar(200) not null,
	Apellido1 nvarchar(200) not null,
	Apellido2 nvarchar(200) not null,
	Telefono varchar(30) null,
	Mail varchar(200) null,
	FechaInicioValidez datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
	FechaFinValidez datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
	PERIOD FOR SYSTEM_TIME (FechaInicioValidez,FechaFinValidez)
) 
WITH (SYSTEM_VERSIONING = ON);

Nota: La opcion HIDDEN es opcional, pero para dar mayor transparencia a los usuarios, es mejor que dichas columnas no sean incluidas en los resultados a no ser que se especifique explicitamente.

Si ahora miramos la tabla creada en Management Studio, podemos ver como Sql Server usa un icono diferente para representar este tipo de tabla.
Si desplegamos las opciones de la tabla, vemos como ademas de incluir las opciones tipicas (Columns, Keys, Indexes, Triggers…), tenemos una entrada asociada a la tabla de historico. En nuestro caso, el nombre generado por Sql Server ha sido [MSSQL_TemporalHistoryFor_597577167].
Si observamos las columnas, vemos que esta tabla es una copia exacta de nuestra tabla de Clientes. La unica diferencia, es que SQL ha creado un indice clustered que contiene las 2 columnas datetime2.

Si queremos controlar el nombre de la tabla de histórico tenemos que usar la opción HISTORY_TABLE
Nota: En el nombre de la tabla hay que indicar de forma obligatoria el nombre del esquema

WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.HISTORIALCLIENTES))

CREATE TABLE CLIENTES2
(
	Id int not null Identity(1,1) CONSTRAINT PK_CLIENTES2 PRIMARY KEY,
	Nombre nvarchar(200) not null,
	Apellido1 nvarchar(200) not null,
	Apellido2 nvarchar(200) not null,
	Telefono varchar(30) null,
	Mail varchar(200) null,
	FechaInicioValidez datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
	FechaFinValidez datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
	PERIOD FOR SYSTEM_TIME (FechaInicioValidez,FechaFinValidez)
) 
WITH (
	SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.HISTORIALCLIENTES2)
);

Por último, si queremos usar una tabla existente podemos usar la misma consulta del caso anterior, ya que al especificar el nombre de la tabla de histórico, Sql Server comprueba si la estructura es compatible

CREATE TABLE HISTORIALCLIENTES3
(
	Id int not null,
	Nombre nvarchar(200) not null,
	Apellido1 nvarchar(200) not null,
	Apellido2 nvarchar(200) not null,
	Telefono varchar(30) null,
	Mail varchar(200) null,
	FechaInicioValidez datetime2 not null,
	FechaFinValidez datetime2 not null
);
go
CREATE NONCLUSTERED INDEX IX_HISTORIALCLIENTES3_COLUMNAS_TEMPORALES   
   ON HISTORIALCLIENTES3 (FechaInicioValidez, FechaFinValidez, Id);   
GO
CREATE TABLE CLIENTES3
(
	Id int not null Identity(1,1) CONSTRAINT PK_CLIENTES3 PRIMARY KEY,
	Nombre nvarchar(200) not null,
	Apellido1 nvarchar(200) not null,
	Apellido2 nvarchar(200) not null,
	Telefono varchar(30) null,
	Mail varchar(200) null,
	FechaInicioValidez datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
	FechaFinValidez datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
	PERIOD FOR SYSTEM_TIME (FechaInicioValidez,FechaFinValidez)
) 
WITH (
	SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.HISTORIALCLIENTES3)
);

Si ya existe la tabla sobre la que queremos aplicar el control de versiones, podemos ejecutar una sentencia tipo ALTER TABLE

CREATE TABLE CLIENTES4
(
	Id int not null Identity(1,1) CONSTRAINT PK_CLIENTES4 PRIMARY KEY,
	Nombre nvarchar(200) not null,
	Apellido1 nvarchar(200) not null,
	Apellido2 nvarchar(200) not null,
	Telefono varchar(30) null,
	Mail varchar(200) null,
);
GO   
ALTER TABLE CLIENTES4   
   ADD   
      FechaInicioValidez datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL   
           CONSTRAINT DF_FechaInicioValidez DEFAULT SYSUTCDATETIME(),
      FechaFinValidez datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
           CONSTRAINT DF_FechaFinValidez DEFAULT CONVERT(datetime2, '9999-12-31 23:59:59'),   
      PERIOD FOR SYSTEM_TIME (FechaInicioValidez, FechaFinValidez);   
GO   
ALTER TABLE CLIENTES4   
   SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.HISTORIALCLIENTES4));
GO

Una vez configurada la tabla de historico, podemos realizar insercciones, eliminaciones y actualizaciones de la forma habitual.

Si realizamos una inserccion, en la columna [ROW START] se graba el valor de la fecha/hora actual y en la columna [ROW END] se guarda la fecha ‘9999-12-31 23:59:59’

INSERT INTO CLIENTES4(Nombre,Apellido1,Apellido2,Telefono,Mail)
VALUES('Cliente 1','C1 Apellido 1','C1 Apellido 2','111111','cliente1@programmingapps.net')
go
INSERT INTO CLIENTES4(Nombre,Apellido1,Apellido2,Telefono,Mail)
VALUES('Cliente 2','C2 Apellido 1','C2 Apellido 2','222222','cliente2@programmingapps.net')
go
select *,FechaInicioValidez,FechaFinValidez from CLIENTES4

A continuación (realmente, unas horas después), vamos a actualizar uno de los registros

UPDATE CLIENTES4 set Nombre='Cliente2 Modificado' where Nombre='Cliente 2'
go

Esta modificación realiza las siguientes operaciones.

  • CLIENTES4: la columna [ROW START] se ha actualizado a la fecha actual
  • HISTORIALCLIENTES4: se ha añadido un nuevo registro con los valores originales. La columna [ROW END] se ha modificado con la fecha actual.

Y por último vamos a borrarlo

DELETE FROM CLIENTES4 where Nombre='Cliente2 Modificado'
go

El borrado guarda el registro en la tabla HISTORIALCLIENTES4 y actualiza la columna [ROW END]

Hasta ahora hemos consultado la trazabilidad del registro, usando una SELECT sobre la tabla de historial.
Pero SQL SERVER ha incluido la clausula FOR SYSTEM_TIME para facilitar la consulta de los datos históricos.

En el siguiente ejemplo se retornan todas las versiones

Select * from Clientes4
FOR SYSTEM_TIME ALL

Otra subclausula muy interesante es AS OF, la cual devuelve las filas que fueron actuales a partir un momento determinado

Select * from Clientes4
FOR SYSTEM_TIME AS OF '20/05/2017 14:00:00'
go
Select * from Clientes4 /*El registro se modifico a las 16:01*/
FOR SYSTEM_TIME AS OF '20/05/2017 16:05:00'
go
Select * from Clientes4 /*El registro se borro a las 16:12*/
FOR SYSTEM_TIME AS OF '20/05/2017 16:15:00'

Por último, vamos a ver la clausula FROM, la cual devuelve todas las versiones de fila que estaban activas en un perido de tiempo

Select *,FechaInicioValidez,FechaFinValidez from Clientes4 
FOR SYSTEM_TIME FROM '20/05/2017 16:00:00' TO '20/05/2017 16:15:00'

En el siguiente enlace del MSDN, podéis encontrar más detalles sobre las diferentes formas de consulta de los datos históricos

En el siguiente enlace se puede descargar el script sql.