Versionado de registros en SQL 2016 o superior

En algunas situaciones necesitamos hacer un versionado de registros, un ejemplo podría ser mantener una copia de las modificaciones que sufren los registros de una tabla a lo largo del tiempo.

Si bien esto se puede implementar de varias formas a partir de SQL Server 2016 disponemos de la funcionalidad System Versioned Tables el cual nos permitirá poder tener este control de una forma simple, eficiente y segura.

En este documento veremos cómo implementar esta nueva funcionalidad y que novedades trae SQL Server 2017 con respecto a la misma.

Esta funcionalidad la podemos aplicar a tablas ya existentes o bien a nuevas. Las tablas deben contener dos columnas nuevas las cuales marcaran el SysStarttime y SysEndTime usando como tipo de dato Datetime2

El siguiente grafico ilustra el funcionamiento de esta nueva funcionalidad de SQL Server

Ejemplo1: Creación de una tabla con soporte para versionado

En este primer ejemplo crearemos una tabla nueva donde le incluiremos las columnas para el versionado como así también lo habilitaremos, se creará una tabla de forma automática llamada CLIENTES_HISTORY la cual mantendrá las versiones de los registros.

Si luego vamos a nuestro SSMS vamos a poder observar que la table con versionado se distingue del resto y a su vez Podemos ver la tabla de historia como si fuera una tabla hija de la principal.

Ahora insertaremos dos registros en nuestra tabla Clientes para luego hacer el tracking de los mismos.

Si observamos la tabla clientes_history nos encontraremos que hasta el momento no hay registros ya que no se han modificado los existentes

Ahora lo que haremos es una simple modificación a un cliente , por ejemplo le cambiaremos la dirección

Observaremos que en la tabla clientes_history nos vamos a encontrar con un solo registro que es el valor que tenía el cliente TRIGGERDB Consutling antes de hacer el cambio

Si desearemos modificar o borrar la tabla history no podríamos hacerlo ya que la misma es parte de la integridad de la tabla origen, con lo cual si corremos el siguiente código nos dará un error

Msg 13560, Level 16, State 1, Line 27 Cannot delete rows from a temporal history table ‘TEST.dbo.CLIENTES_HISTORY’.

Ahora si deseamos consultar la historia de un registro o bien saber a una fecha cuál era su valor real, TSQL incorpora la cláusula FOR SYSTEM_TIME la cual se puede utilizar en nuestros comandos DML.

El siguiente ejemplo buscara el registro a la fecha 31-07-2017 a las 18:57 el cual para el caso del cliente TriggerDB la dirección es Av Cabildo 4100.

Este valor el SQL lo está buscando de forma automática en la tabla clientes_history y clientes, dependiendo de la fecha sabrá que registro mostrarme. Esto lo podemos ver claramente en su plan de ejecución

Pruebas de performance:

Para poder probar los impactos en performance se han realizado 3 pruebas sobre la tabla Clientes , la primera haciendo un INSERT de 100K registros, la segunda un UPDATE de esos 100K y la última prueba un DELETE de los registros. Esto se comparó teniendo en ON y en OFF el versionado, y los resultados están en la siguiente tabla.

Prueba Versionado OFF

Versionado ON

%

Insert 100K 21 Sec 23 Sec

10%

Update 100K 20 Sec 23 Sec

15%

Delete 100K 20 Sec 25 Sec

25%

Modificaciones de estructuras en la tabla

Si usted desea hacer modificaciones de estructuras en la tabla sin que eso requiera hacer un DROP de la misma, el cambio se propagara de forma automática a ambas tablas. Por ejemplo ahora crearemos una nueva columna en la tabla Clientes con el siguiente código TSQL

Si observamos la metadata de la tabla clientes_history podremos observar que se agregó la columna de forma transparente

Ahora si hacemos un SELECT sobre la tabla Clientes_History vamos a observar que para los registros anteriores tenemos nulos y a partir de este momento si hacemos modificaciones en la tabla empezaran recién ahí a aparecer valores.

Deshabilitar el versionado:

Para poder deshabilitar el versionado usaremos la siguiente instrucción TSQL

Al hacer esta des habilitación vamos a observar que la tabla history no se borrara, pero quedara como independiente y no asociada a la tabla padre.

Crear una tabla de versionado de forma customizada.

En el ejemplo anterior hemos visto como se crear de forma automática la tabla de versionado, pero podríamos tener la necesidad de hacerla de forma customizada, por ejemplo, esto nos permitiría:

  • Alojarla en un FG distinto.
  • Agregarles índices adicionales

En el siguiente ejemplo crearemos la tabla history de forma customizada en un FG distinto al PRIMARY y le agregaremos índices que no existen en la tabla origen

 

Depurar registros de la tabla history.

Si usted tiene SQL Server 2016 el procedimiento para depurar la tabla history sería el siguiente

  1. Deshabilitar el versionado de la tabla
  2. Depurar la tabla history (por ejemplo con un proceso de DELETE where fecha <= )
  3. Habilitar nuevamente el versionado

SQL 2017 trae algunas mejoras ya que podemos indicarle a nuestra tabla el periodo de retención, para poder usar esta nueva funcionalidad debemos seguir los siguientes pasos

Conclusiones finales

Temporal Versioned Tables es una de las features implementadas a partir de SQL Server 2016 que pueden darnos buenos frutos a la hora de pensar en tablas que necesitamos versionar o auditar los cambios de registros en nuestras tablas.

Se lo puede aplicar tanto a sistemas OLTP como así también a DW

Información adicional

Getting Started with System-Versioned Temporal Tables

Creating a System-Versioned Temporal Table

Manage Retention of Historical Data in System-Versioned Temporal Tables

System-Versioned Temporal Tables with Memory-Optimized Tables

Leave a Reply