El 28 de octubre de 2017 voy a estar exponiendo junto con Maristel Castro en el SQL Saturday BI-Costa Rica sobre la implementación de Tablas Temporales en Almacenes de Datos, Este artículo cubre los elementos más fundamentales y la sintaxis para crear tablas temporales en SQL 2016 y versiones siguientes.

Pero antes de entrar a la sintaxis permítame una pequeña rabieta: Las tablas temporales tienen uno de los peores nombres que se le pueden asignar. Incluso en ingles “temporal” y “temporary” me parecen inapropiados, ya que, aunque estos términos no son sinónimos si son muy parecidos. Para complicar más el tema en español no tenemos una forma de distinguir un término de otro ya que temporario es sinónimo de temporal, y durante años hemos en castellano hemos usado el término tablas temporales para tablas “temporary”, con lo cual es fácil confundir un concepto con otro. Solo para aclarar cualquier el error que este nombre puede producir, este posteo trata sobre las tablas que llevan una versión histórica de las filas y que permiten hacer consultas relativas al tiempo de vigencia de los datos, no sobre las tablas que se desaparecen cuando termina el procedimiento almacenado o se cierra la conexión.

Como crear una Tabla Temporal

La sintaxis de la creación de tablas temporales fue introducida en SQL 2016 y tiene tres condiciones:

1. La tabla debe tener llave primaria.
2. Tres columnas adicionales con las siguientes características:
   1. Dos columnas con tipo de dato DATETIME2. Para los que no están familiarizados el DATETIME2, fue introducido en SQL 2008 y es un tipo de dato que extiende la funcionalidad del DATETIME tradicional de tres formas: extiende el rango de fechas, (Inicio en año 1 para DATETIME2 en comparación contra 1753 del DATETIME);  cambia la precisión ya que de forma predeterminada DATETIME2 tiene una precisión de 100 nanosegundos en comparación con los 00333 segundos de DATETIME; y finalmente la precisión puede ser controlada de forma directa como parámetro del tipo de dato.
   2. Las dos columnas DATETIME debe agregárseles la propiedad GENERATED ALWAYS AS ROW START o GENERATED ALWAYS AS ROW END. Esto señala a la plataforma de bases de datos cual columna lleva control del inicio y final de la validez de la fila. Optativamente pueden llevar HIDDEN, que en general me parece una buena práctica, ya que oculta estas columnas de la tabla original. Es importante recalcar que en estas columnas van a capturar el valor UTC (Universal Time Coordinated) durante las cuales es válida la fila. Finalmente es optativo que se les agregue la restricción de NOT NULL, porque igualmente la plataforma interpreta que estas columnas no soportan nulos.
   3. Finalmente, la declaración de PERIOD que usa la función FOR SYSTEM_TIME (, ).s
3. La tabla debe estar marcada con la propiedad SYSTEM_VERSIONING=ON. Optativamente pueden especificar la tabla histórica (HISTORY_TABLE=<>), que es una buena práctica ya que en caso contrario la plataforma usara un nombre para la tabla histórica que usa el nombre MSSQL_TemporalHistoryFor_.

Un ejempo del CREATE TABLE para una tabla temporal es:

Este código puede ser bajado de Github del archivo 001 CreateSalesPersonWithHistory.sql

Como agregar Temporalidad a una tabla existente

Si ya las tablas existen y queremos agregarle la funcionalidad de temporalidad entonces la tarea consiste en realizar dos ALTER: el primero que agregue las columnas anteriormente descritas; y el segundo que habilite las versiones del sistema. El código de ejemplo es:

Este código refleja que internamente las tablas estan usando valores predeterminados (DEFAULT Constraints) que cuando creamos la tabla se generan automáticamente  pero que tenemos que declarar cuando hacemos el ALTER.

Puede bajar el codigo del archivo 002 AlterSalesPersonWithHistory.sql

Como borrar una tabla temporal

Una vez que la tabla está configurada como tabla temporal, la plataforma de base de datos no permite que se borre la tabla si antes no deshabilitamos la opción de versionado. El código para eliminar las tablas es:

ALTER TABLE Sales.SalesPerson SET (SYSTEM_VERSIONING = OFF);
GO
DROP TABLE IF EXISTS Sales.SalesPerson;
DROP TABLE IF EXISTS Sales.SalesPersonHistory;
GO

El codigo que borra la tabla puede obtenerlo del 003 CleanUPSalesPerson.sql

Conclusión

Espero que esta entrada les sirva como punto de partida para desmifiticar las tablas temporales/historicas y los motive al SQL Saturday BI-Costa Rica donde vamos a explorar como usar esta herramienta en ambientes de Inteligencia de Negocios y mas especificamente de Bodegas de Datos.