Versionado de Tablas en SQL Server: ¿Qué es y cómo funciona?

La importancia de la trazabilidad de los cambios

Una de las peticiones que nos realizan frecuentemente en muchos de los proyectos que iniciamos, es tener una trazabilidad de los cambios que se realizan en cualquier tabla.

Para algunos de los proyectos, únicamente se requiere una trazabilidad simple y conocer la fecha, hora de creación y última actualización de la tabla, así como los usuarios que realizaron dichas acciones.

Pero en otros casos, se requiere un historial completo de todos los cambios realizados, para lo cual se recurría a generar una tabla de histórico de cambios, la implementación del rellenado de esta tabla normalmente tenía 2 posibles opciones:

• Utilizar los poco populares Triggers que se disparan en la base de datos a nivel de fila al realizarse una operación de Update/Insert/Delete
• Sobrecargar la lógica de negocio e implementarlo en los métodos dónde se realice cualquier cambio sobre la tabla

Sea cual sea la elección que realicemos entre las dos opciones anteriores, nos obliga a incurrir a un coste tanto económico como de rendimiento, que podríamos ahorrarnos en su gran mayoría, si aplicáramos una de las muchas características no tan conocidas de las que dispone SQL Server.

Veamos a continuación de que característica estamos hablando.

¿Qué es el System Versioning?

La característica de la que os queremos hablar y mostrar en esta ocasión es el System Versioning, una característica que automatiza de forma transparente a la aplicación la generación y mantenimiento de la Tabla Histórica.

Tras la activación de esta característica en SQL Server, se crea automáticamente la Tabla Histórica con el mismo Schema que la Tabla Principal, y se empiezan a registrar todos los cambios sobre la Tabla Principal, generando una fila en la Tabla Histórica de forma automática cada vez que se realiza cualquier tipo de actualización sobre la Tabla Principal.

¿Cómo habilitamos el System Versioning en una tabla existente?

Para habilitar el System Versioning en nuestra tabla existente, tan sólo tenemos que realizar los siguientes pasos:

Paso 1

Lo primero es modificar el schema de la Tabla Principal, añadiendo 3 columnas que serán utilizadas internamente para registrar las fechas de vigencia de cada fila:

alter table dbo.[Contacto]
add
    TimeStart datetime2 generated always as row start not null default getutcdate(),
    TimeEnd datetime2 generated always as row end not null default convert(datetime2, '9999-12-31 23:59:59.9999999'),
    period for system_time (TimeStart, TimeEnd);

Paso 2

El siguiente paso es activar la característica de Versionado en la Tabla Principal, indicando el nombre de la Tabla Histórica que se creará.

alter table dbo.[Contacto]  
set (system_versioning = on (HISTORY_TABLE = dbo.[Contacto_History]));

Una vez activada la característica, veremos en el SSMS (SQL Server Management Studio) un pequeño cambio visual en la forma en la que se muestra la Tabla Principal, como veis a continuación:

Sin Versionado	Con Versionado

Además, si desplegamos la Tabla Principal, también veremos el nombre y las propiedades de la Tabla Histórica, como podeis ver en la siguiente imagen:

Y de esta sencilla manera, ya tendríamos habilitado el System Versioning en nuestra Tabla.
Pero, ¿Qué acciones puede llevar a cabo esta característica de SQL Server?

System Versioning en acción

Sobre la Tabla Principal (Contacto) que ya hemos versionado, realizamos las siguientes operaciones:

Paso 1

Insertamos un nuevo Contacto, con nombre = ‘Contacto Versionado’:

insert into Contacto (Nombre, Telefono)  values ('Contacto Versionado',  '+34 666 66 66')

Obteniendo la siguiente fila:

Paso 2

Esperamos unos minutos, y a continuación realizamos un Update del teléfono:

update Contacto set Telefono = '+34 999 99 99' where Id = 1

Paso 3

Esperamos unos minutos más, y realizamos otro Update, en esta ocasión del Nombre:

update Contacto (Nombre, Telefono)  set Nombre = 'Contacto Versionado 2.0' where Id = 1

Paso 4

Por último, examinamos el contenido de ambas tablas:

select Id, Nombre, Telefono, TimeStart, TimeEnd from Contacto where Id = 1

select Id, Nombre, Telefono, TimeStart, TimeEnd from Contacto_History where Id = 1

Como era previsible en la Tabla Principal podemos observar que se conserva la información actualizada tras el último Update de la acción número 3, mientras que en la Tabla de History tenemos el contenido previo de la Tabla Principal a realizar cada uno de los Updates realizados en las acciones número 2 y 3.

¿Cómo extraer información de una fecha y hora dada en SQL Server?

Tras la activación de la característica System Versioning, una de las consultas muy útiles que también podemos realizar es obtener el estado de una fila a una determinada fecha y hora pasada.

Para la realización de esta consulta, utilizaremos la cláusula FOR SYSTEM_TIME al realizar un SELECT sobre la Tabla Principal.

Partiendo de la fecha y hora de ejecución de las 3 acciones, tendríamos la siguiente tabla:

Número de Acción	Descripción	Fecha y Hora Acción
1	Insert del Contacto	2022-11-28 12:34:36
2	Update del Teléfono	2022-11-28 12:36:42
3	Update del Nombre	2022-11-28 12:40:54

Veamos ahora una serie de ejemplos:

Ejemplo 1

Obtenemos la versión del Contacto, justo después de ejecutar la Acción 1 y antes de la Acción 2:

select Id, Nombre, Telefono, TimeStart, TimeEnd from Contacto for system_time as of '2022-11-28 12:35:00' where Id = 1

Ejemplo 2

Obtenemos la versión del Contacto justo después de la Acción 2 y antes de la Acción 3:

select Id, Nombre, Telefono, TimeStart, TimeEnd from Contacto for system_time as of '2022-11-28 12:37:00' where Id = 1

Ejemplo 3

Y, por último, obtenemos la información del Contacto justo después de la Acción 3:

select Id, Nombre, Telefono, TimeStart, TimeEnd from Contacto for system_time as of '2022-11-28 12:37:00' where Id = 1

¿Cómo extraer información histórica en SQL Server?

Además de la cláusula AS OF, también podríamos utilizar las siguientes cláusulas para consultar información histórica:

• FROM <start_date_time> TO <end_date_time>
• BETWEEN <start_date_time> AND <end_date_time>
• CONTAINED IN (<start_date_time>, <end_date_time>)
• ALL

FROM y BETWEEN

FROM y BETWEEN devuelven filas que se superponen con el periodo especificado.

CONTAINED IN

CONTAINED IN devuelven aquellas filas que existieron en su totalidad dentro del periodo.

ALL

Con ALL obtendríamos todas las filas históricas.

Y con esta sencilla característica de SQL Server, System Versioning, podremos tener una trazabilidad detallada de todos los cambios que se realizan en cualquier Tabla de nuestro proyecto.

Por si quieres desarrollar más en profundidad la creación de tablas, te dejo el siguiente enlace.

Francisco Valcarreras – Senior Product Owner at Itequia