El pasado 28 de octubre de 2017, expuse con Ronald Hanna en en el #SQLSaturday de BI en Costa Rica sobre la implementación de Tablas Temporales en Almacenes de Datos  en #SQLServer, y un poco antes escribí un artículo introductorio sobre dichas tabla en SQL SERVER: Introducción a Tablas Temporales. Nota: Si cree que las tablas temporales son las que empiezan con #, como CREATE TABLE #FacturasEnProceso( … ), se va a decepcionar. Este artículo no es sobre estas tablas. Recomiendo que lea el artículo introductorio, mencionado anteriormente. Este artículo trata de una característica llamada “Tablas Temporales versionadas por sistema” o en inglés “system-versioned temporal tables”; funcionalidad agregada en SQL 2016 que permite tener tablas que tienen una versión histórica de las filas y usando el lenguaje T-SQL te permite extraer con facilidad consultas relativas al tiempo de vigencia de los datos.

Antes de entrar al tema de las consultas temporales quiero rescatar que las tablas temporales y sus copias históricas pueden ser usadas en consultas de la misma forma que cualquier otra tabla, así que, si se requiere alguna funcionalidad especial no incluida en las consultas temporales, es perfectamente factible hacer SELECTs y combinar la tabla temporal (que tiene los datos actuales) y la tabla histórica; combinándolas con JOINs o más probablemente con UNION, UNION ALL, INTERSECT o EXCEPT.

Consultas Temporales

Una de las ventajas más importantes de usar tablas temporales, es que Microsoft agrega en SQL 2016 la cláusula FOR SYSTEM_TIME y que es el centro de atención de este posteo. El FOR SYSTEM_TIME es una extensión del leguaje (incluida en el estándar ANSI SQL:2011) que permite hacer consultas que tienen un contexto temporal y que retornan los valores de la tabla en un momento determinado o los valores validos durante un periodo definido. Las formas que tomas esa extensión son:

• FOR SYSTEM_TIME ALL: Que retorna todas las filas actuales y todas las filas históricas de la fila. Esta sintaxis es equivalente a un UNION ALL entre la tabla temporal y la tabla histórica, on una notable excepción. El SYSTEM_TIME ALL automáticamente filtra las filas que tengan fecha de inicio y final iguales, mientras que el UNION ALL no.

SELECT SalesPersonCode, FirstName, MiddleName, LastName, FullName
	, SalesQuota, Bonus, CommissionPct, Starting, Ending
FROM Sales.SalesPerson 
FOR SYSTEM_TIME ALL;

La siguiente imagen presenta una comparación de los planes de ejecución:

Se puede ver que el plan de la izquierda, tiene un filtro adicional con un predicado Starting<>Ending.

FOR SYSTEM_TIME ALL me parece que es muy útil cuando uno quiere construir su propia lógica de tiempo y quiere entonces de forma personalizada manejar los filtros o predicados

• FOR SYSTEM_TIME AS OF {Tiempo1}: Que filtra las filas para retornar las filas que existían en ese momento en el tiempo. Esta sintaxis es como si viajáramos a ese preciso momento en el tiempo y consultáramos la tabla temporal. Otra forma de verlo es como si usáramos como predicado Start<= {Tiempo1} AND End>{Tiempo1}. Note que en el caso del primer predicado se incluye la igualdad, por lo que la consulta incluye registros cuya transacción inicio en ese momento.

SELECT SalesPersonCode, FirstName, MiddleName, LastName, FullName
      , SalesQuota, Bonus, CommissionPct, Starting, Ending
FROM Sales.SalesPerson 
FOR SYSTEM_TIME AS OF '20170402';

La opción de FOR SYTEM_TIME AS OF, me parece que es la versión más útil del comando, porque es la que permite con facilidad “viajar en el tiempo” y ver los datos tal y como estaban en un momento determinado. Es valiosa porque cuando se usa dinámica de sistemas, queremos hacer análisis de existencias (stocks) y esta habilidad nos permite con facilidad ver cuál era el stock en una fecha definida. Por ejemplo, cuantos empleados tenia a inicio de mes, o cuantas habitaciones libres tenía disponibles a inicio de semana.

• FOR SYSTEM_TIME BETWEEN {Tiempo1} AND {Tiempo2}: Que filtra las filas que tuvieron cambios durante un periodo señalado. Esta sintaxis permite estudiar el conjunto de datos como un flujo de versiones de filas, y sirve para Auditorias, para simular dimensiones que cambian lentamente (SDC por sus siglas en inglés: Slow Changing Dimensions) o para análisis de flujos en el contexto de dinámica de sistemas. Una forma de ver esta opción es como si usáramos como predicado: Start <= {Tiempo2} AND End > {Tiempo1}. Una limitación de los resultados de esta consulta es que no distingue la acción que produce el cambio, o sea en el conjunto de datos resultado debe uno basado en las fechas y en la relación con otras filas entender si fue el registro es nuevo, la versión anterior de una actualización, la nueva versión de una actualización o un registro borrado. Change Data Capture (Captura de Modificaciones de Datos) si captura este tipo de información.
• FOR SYSTEM_TIME FROM {Tiempo1} AND {Tiempo2}: Esta versión del comando es muy parecidas al BETWEEN pero que manejan diferente la igualdad, en este caso el FROM es excluyente y por ende elimina la igualdad de manera que es equivalente a que usáramos como predicado: Start < {Tiempo2} AND End > {Tiempo1}
• FOR SYSTEM_TIME CONTAINED IN {Tiempo1}, {Tiempo2}: Igual que la versión anterior, esta opción es muy parecidas al BETWEEN pero en este caso el CONTAINED es incluyente de manera que es equivalente a que usáramos como predicado: Start <= {Tiempo2} AND End => {Tiempo1}.

Espero que este articulo perfeccione los conocimientos adquiridos en el primer artículo de Tablas Temporales en Almacenes de Datos y veas lo útil que pueden ser este tipo de tablas en contextos de inteligencia de Negocios y Bodegas de Datos. Mas adelante espero escribir de optimización, consultas avanzadas de tablas temporales y por supuesto mejores prácticas.