SQL Server 2016 introduce un nuevo tipo de tabla que es diseñado para contener el historial completo de cambios de datos, donde la validez de las filas es manejada por el sistema. Este nuevo tipo de tabla es llamado una Tabla Temporal con Versiones del Sistema. En versiones anteriores de SQL Server, las tablas de usuario le permitirían mantener sólo la copia más reciente de la fila, sin poder consultar el valor antes de las operaciones UPDATE o DELETE. Usando una Tabla Temporal, usted podrá consultar el estado reciente de la fila como usualmente se hace, en adición a la habilidad de consultar el historial completo de esa fila, lo cual es completamente manejado por el Motor de SQL Server, ya que usted no puede definir el periodo de validez de las columnas explícitamente.
Cada Tabla Temporal con Versiones del Sistema debería contener dos columnas del periodo de tiempo que son explícitamente definidas en la tabla; la columna Period Start que será usada por el sistema para almacenar el tiempo de inicio de la fila, y la columna Period End, en la cual el sistema almacenará el tiempo de finalización de la fila.
La tabla Temporal principal contendrá solamente la versión actual de las filas de la tabla, pero será enlazada automáticamente a otra tabla con el mismo esquema, en la cual las versiones previas de las filas modificadas serán almacenadas automáticamente, esta tabla es llamada una tabla de Historial (History, en inglés). Cuando usted crea una tabla Temporal, la tabla History será creada automáticamente. Si usted no especifica el nombre de la tabla historial como un parámetro en la cláusula SYSTEM_VERSIONING, será llamada con el patrón de nombre: MSSQL_TemporalHistoryFor_<id_de_objeto>. Especificar el nombre de la tabla historial mientras se crea la tabla Temporal permitirá al sistema crear la tabla historial con la configuración del sistema por defecto, con la habilidad de crear su propia tabla historial y proveer su nombre en la sentencia de creación de tabla Temporal. Tome en consideración que ningún cambio puede ser realizado en el historial de los datos de la tabla. Si usted trata de eliminar cualquier fila de la tabla historial, usted obtendrá el error: Cannotdeleterowsfrom a temporal historytable ‘<NombreDeTabla>’, ya que es manejada sólo por el sistema.
Cuando una nueva fila sea insertada, el sistema asignará la columna de tiempo PeriodStart con el tiempo de inicio de la transacción actual basado en el reloj del sistema, y assignará la columna de tiempo PeriodEnd con el valor máximo de datetime2 que sea igual a 999-12-31, y la fila será abierta. Si una fila existente es actualizada, e sistema copiará la versión previa de la fila en la tabla de historial y llenará la columna de tiempo PeriodEnd con el tiempo de inicio de la transacción actual basado en el reloj del sistema, y la columna será cerrada. El valor de la columna de tiempo PeriodEnd de la tabla temporal principal será 9999-12-31 sin ningún cambio.
Eliminando una fila existente, la fila será almacenada en la tabla historial, con el valor de la columna de tiempo PeriodEnd igual al tiempo de inicio de la transacción actual basado en el reloj del sistema, y la fila será cerrada después de eliminarla de la tabla temporal principal.
Las Tablas Temporales con Versiones del Sistema pueden ser habilitadas cuando una tabla es creada usando la sentencia CREATE TABLE o después de crear la tabla usando la sentencia ALTER DATABASE. Para habilitar las Versiones de Sistema en una tabla, esta debe tener una clave primaria con dos columnas de periodo datetime2 que no acepten valores null, definidas como GENERATED ALWAYS AS ROW START o END, pasadas como parámetros en PERIOD FOR SYSTEM_TIMEdentro de la definición de la tabla.
La siguiente sentencia CREATE TABLE es usada para crear la tabla temporal Temporal_Table_Demo, con la clave primaria DempID, las columnas two-period; TimeStart GENERATED ALWAYS AS ROW START y TimeEnd GENERATED ALWAYS AS ROW END, ambos designados comPERIOD FOR SYSTEM_TIME, la tabla temporal es enlazada a la tabla historial dbo.Temporal_Table_Demo_History, tomando en consideración que identificar el nombre del esquema de la tabla historial es mandatorio, y finalmente habilitando la característica SYSTEM_VERSIONING como sigue:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
USE SQLShackDemo GO CREATE TABLE dbo.Temporal_Table_Demo ( [DempID] int NOT NULL PRIMARY KEY CLUSTERED , [DepName] nvarchar(100) NOT NULL , [DepLocation] varchar(100) NOT NULL , [NumOfEmp] int NOT NULL , [TimeStart] datetime2 (2) GENERATED ALWAYS AS ROW START , [TimeEnd] datetime2 (2) GENERATED ALWAYS AS ROW END , PERIOD FOR SYSTEM_TIME (TimeStart, TimeEnd) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Temporal_Table_Demo_History)); |
Navegue a la nueva tabla temporal desde el Explorador de Objetos, donde usted verá un ícono especial con un pequeño reloj (vea la imagen abajo) para la tabla temporal con System-Versioned entre paréntesis. La tabla historial no tendrá un nodo de tabla separado; puede ser mostrado expandiendo la tabla temporal con un nodo debajo de la tabla principal identificada con History entre paréntesis. Un índice agrupado por defecto será creado para la tabla historial que contiene las columnas PeriodStart y PeriodEnd como se muestra a continuación:
Asumamos que necesitamos habilitar las Versiones de Sistema en una tabla existente en nuestra base de datos. Para hacer eso, dos columnas datetime2 que no aceptan nullPeriodStart y Periodend deberían ser añadidas con todas las especificaciones especificadas previamente como en el script a continuación:
|
1 2 3 4 5 6 7 8 |
ALTER TABLE dbo.AWBuildVersion ADD [TimeStart] DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL CONSTRAINT DFT_AWBuildVersion_TimeStart DEFAULT ('19000101'), [TimeEnd] DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL CONSTRAINT DFT_AWBuildVersion_TimeEnd DEFAULT ('99991231 23:59:59'), PERIOD FOR SYSTEM_TIME ([TimeStart], [TimeEnd]); ALTER TABLE dbo.AWBuildVersion DROP CONSTRAINT DFT_AWBuildVersion_TimeStart, DFT_AWBuildVersion_TimeEnd; ALTER TABLE dbo.AWBuildVersion SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.AWBuildVersion_History ) ); |
En los scripts previos, las restricciones por defecto eran definidas como que las columnas de periodo no deberían aceptar null y deberían luego ser eliminadas antes de habilitar las Versiones de Sistema ya que el Motor de SQL será responsable de llenar estas dos columnas. El Motor de SQL Server realizará una revisión de consistencia en las tablas temporal y de historial para verificar que todos los requerimientos del esquema son aplicados antes de habilitar Versiones de Sistema (System_Versioning). Esto incluye revisar que el tiempo PeriodEnd es mayor que el tiempo PeriodStart y que los periodos de las diferentes versiones de la misma fila no se sobrepondrán. La revisión de consistencia es habilitada por defecto y puede ser deshabilitada usando la sentencia DATA_CONSISTENCY_CHECK = OFF en la parte de habilitación de System_Versioning. Ahora que la tabla AWBuildVersion es definida como una tabla temporal exitosamente:
Habilitar System_Versioning en la tabla no evitará que usted modifique el esquema de la tabla. El sistema impondrá un bloqueo de esquema en la tabla temporal y de historial, donde estos cambios serán replicados a la tabla de historial. Un cambio específico en el esquema no puede ser realizado directamente usando una sentencia ALTER DATABASE en una tabla con System_Versioning habilitado, como añadiendo o removiendo columnas IDENTITY, COMPUTED, SPARSE, COLUMN_SET o ROWGUIDCOL. Para aplicar tales cambios en una tabla con System_Versioning habilitado, usted necesita primero deshabilitar System_Versioning, realizar el cambio que necesita y luego habilitar System_Versioning de nuevo. El script de abajo es usando para añadir una nueva columna IDENTITY a la tabla temporal Temporal_Table_Demo (no consideraremos esta columna añadida en la demostración que viene):
|
1 2 3 4 |
ALTER TABLE dbo.[dbo].[Temporal_Table_Demo] SET ( SYSTEM_VERSIONING = OFF ); ALTER TABLE [dbo].[dbo].[Temporal_Table_Demo] ADD ID int IDENTITY (1,1); ALTER TABLE dbo.[dbo].[Temporal_Table_Demo] SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.[dbo].[Temporal_Table_Demo_History])); |
Comencemos una demostración para investigar esta nueva característica. Primero, insertaremos 5 registros a la tabla temporal Temporal_Table_Demo usando la siguiente simple sentencia INSERT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE [SQLShackDemo] GO INSERT INTO [dbo].[Temporal_Table_Demo] ([DempID] ,[DepName] ,[DepLocation] ,[NumOfEmp]) VALUES (1,'Accounting','Build1',10), (2,'HR','Build1',3), (3,'Recruitment','Build1',2), (4,'IT','Build2',7), (5,'Security','Build2',3) GO |
Consultando la tabla temporal para verificar las filas insertadas:
|
1 2 3 4 5 6 7 |
SELECT [DempID] ,[DepName] ,[DepLocation] ,[NumOfEmp] ,[TimeStart] ,[TimeEnd] FROM [SQLShackDemo].[dbo].[Temporal_Table_Demo] |
El resultado en nuestro caso será:
Como discutimos previamente, el tiempo de PeriodStart será llenado por el sistema con el tiempo de inicio de la transacción, y el tiempo PeriodEnd será llenado por el sistema con el máximo valor datetime2 manteniendo los registros abiertos. Ningún cambio es aplicado a la tabla historial ya que ninguna acción es realizada en esas filas todavía. Si usted intenta consultarla, verá que está vacía.
Si eliminamos la última fila en la tabla temporal con DempID igual a 5:
|
1 |
DELETE FROM [SQLShackDemo].[dbo].[Temporal_Table_Demo] WHERE DempID = 5 |
Y consultamos la tabla temporal, veremos que el registro es removido de la tabla, lo cual es la situación normal:
Pero lo nuevo aquí es que, si consultamos la tabla historial relacionada:
|
1 2 3 4 5 6 7 |
SELECT [DempID] ,[DepName] ,[DepLocation] ,[NumOfEmp] ,[TimeStart] ,[TimeEnd] FROM [SQLShackDemo].[dbo].[Temporal_Table_Demo_History] |
Veremos que el registro eliminado es insertado en la tabla historial, y que el tiempo PeriodEnd es actualizado con el tiempo de inicio de la transacción, marcando la fila como cerrada:
De nuevo, si logramos actualizar el número de empleados en el departamento 1:
|
1 |
UPDATE [SQLShackDemo].[dbo].[Temporal_Table_Demo] SET NumOfEmp=11 WHERE DempID = 1 |
Y consultamos la tabla temporal de nuevo, veremos que la fila es actualizada y que el tiempo PeriodStart también fue actualizado con el nuevo tiempo de transacción, manteniendo la fila abierta:
Y el antiguo registro antes de la operación UPDATE será insertado en la tabla historial con el tiempo PeriodEnd como el tiempo actual de la transacción, cerrando la fila:
Los resultados previos nos muestran cuán útil es la nueva característica System_Versioning, ya que nos permite retrotraer o rastrear mínimamente los cambios aplicados a esa tabla sin obtener de vuelta los antiguos archivos de respaldo para uno o pocos registros.
Una nueva cláusula FOR SYSTEM_TIME es añadida a la sentencia SELECT para consultar los datos en las tablas historial y temporal, con cinco sub cláusulas para proveerle más opciones para especificar el periodo de tiempo requerido. La primera cláusula es la cláusula AS OF, la cual retorna valores después de realizar la unión entre las tablas temporal e historial y filtrar las filas que son válidas en el punto de tiempo especificado. La fila puede ser considerada como válida si el valor del tiempo PeriodStart es menor o igual al tiempo especificado y que ese tiempo PeriodEnd sea mayor que el tiempo especificado.
La siguiente sentencia SELECT usa la cláusula FOR SYSTEM_TIME AS FOR para recuperar todas las filas con TimeStart menor o igual que el tiempo provisto y TimeEnd mayor que el tiempo provisto:
|
1 2 3 |
SELECT * FROM [SQLShackDemo].[dbo].[Temporal_Table_Demo] FOR SYSTEM_TIME AS OF '2016-09-18 13:40:05.06' GO |
El resultado final para las filas válidas después de la unión interna entre las tablas temporal e historial será como sigue:
La segunda cláusula es la cláusula CONTAINED IN, que retorna todas las filas abiertas y cerradas dentro del rango de tiempo definido por los valores two-period. La siguiente sentencia SELECT usa la cláusula FOR SYSTEM_TIME CONTAINED IN para recuperar todas las filas que se abrieron y cerraron dentro de ese rango especificado de tiempo por los valores @StartTimey @EndTime:
|
1 2 3 4 |
DECLARE @StartTime datetime2 = '2016-09-18 13:40:05.06' DECLARE @EndTime datetime2 = '2016-09-18 13:46:03.28' SELECT * FROM [SQLShackDemo].[dbo].[Temporal_Table_Demo] FOR SYSTEM_TIME CONTAINED IN(@StartTime, @EndTime) |
El resultado será en nuestro caso:
La tercera cláusula es la cláusula FROM…TO, que retorna todas las filas activas dentro del rango de tiempo definido por los dos valores provistos, sin importar el tiempo de inicio activo. Una unión será realizada internamente entre la tabla temporal principal y la tabla historial para recuperar las filas activas dentro del rango de tiempo provisto. La sentencia SELECT a continuación usa la cláusula FOR SYSTEM_TIME FROM TO para recuperar todas las filas activas dentro del rango de tiempo especificado por los valores de @StartTime and @EndTime:
|
1 2 3 4 |
DECLARE @StartTime datetime2 = '2016-09-18 13:40:05.06' DECLARE @EndTime datetime2 = '2016-09-18 13:46:03.28' SELECT * FROM [SQLShackDemo].[dbo].[Temporal_Table_Demo] FOR SYSTEM_TIME FROM @StartTime TO @EndTime |
El resultado a continuación muestra todos los registros activos dentro del rango de tiempo provisto:
La Cuarta cláusula es la cláusula BETWEEN…AND que trabaja de la misma manera que la cláusula FROM…TO, excepto que retorna sólo las filas que se vuelven activas en el margen más bajo o se vuelven inactivas en el margen más alto provistos en la sentencia. La sentencia SELECT abajo usa la cláusula FOR SYSTEM_TIME BETWEEN AND para recuperar las filas que se vuelven activas en @StartTime o las filas que se vuelven inactivas en @EndTime:
|
1 2 3 4 |
DECLARE @StartTime datetime2 = '2016-09-18 13:40:05.06' DECLARE @EndTime datetime2 = '2016-09-18 13:46:03.28' SELECT * FROM [SQLShackDemo].[dbo].[Temporal_Table_Demo] FOR SYSTEM_TIME BETWEEN @StartTime AND @EndTime |
La salida de la consulta será como sigue:
La última cláusula es la cláusula ALL, que realiza una unión interna entre las filas en la tabla temporal principal y la tabla historial sin especificar ningún rango de tiempo, como sigue:
|
1 2 |
SELECT * FROM [SQLShackDemo].[dbo].[Temporal_Table_Demo] FOR SYSTEM_TIME ALL |
El resultado será similar a:
Como cualquier nueva característica introducida en SQL Server, una propiedad o DMV dentro de la DMV será añadida para consultar la información de esa característica.Las tablas de sistema sys.tables pueden ser consultadas para buscar las tablas con temporal_type no igual a cero, para recuperar todas las tablas con System_Versioning habilitado con las tablas historial correspondientes como sigue:
|
1 2 3 4 5 6 7 8 |
SELECT Tab1.name as Temporal_Table_Name, Tab2.name as History_Table_Name, Tab1.temporal_type_desc FROM sys.tables Tab1 LEFT JOIN sys.tables Tab2 ON Tab1.history_table_id = Tab2.object_id WHERE Tab1.temporal_type <> 0 ORDER BY Temporal_Table_Name GO |
El resultado de la consulta nos mostrará las dos tablas en las que habilitamos System_Versioning con sus tablas historial relacionadas:
La nueva vista de catálogo de sistema sys.periods puede ser usada también para listar todas las columnas periodo de las tablas con System_Versioning habilitado. La información completa puede ser recuperada uniendo sys.periods con sys.tables, como en la siguiente cosulta:
|
1 2 3 4 5 6 7 8 9 |
SELECT PS.name as Period_Name, Tbl.name as Temporal_Table_Name, clms1.name as Period_Start_CName, clms2.name as Period_Enf_CName FROM sys.periods PS INNER JOIN sys.tables Tbl ON PS.object_id = Tbl.object_id INNER JOIN sys.columns clms1 ON Tbl.object_id = clms1.object_id AND PS.start_column_id = clms1.column_id INNER JOIN sys.columns clms2 ON Tbl.object_id = clms2.object_id AND PS.end_column_id = clms2.column_id GO |
El resultado de la consulta nos mostrará las dos tablas en las que habilitamos System_Versioning con sus tablas historial relacionadas:
SQL Server le permite añadir índices a las tablas temporal e historial para mejorar el desempeño de la consulta. Asuma que necesitamos mejorar el desempeño de la consulta de abajo, la cual está corriendo muy frecuentemente en nuestro sistema y cualquier mejora en ella hará una diferencia en el desempeño general:
|
1 2 3 4 5 6 |
SET STATISTICS TIME ON DECLARE @StartTime datetime2 = '2016-09-18 13:40:05.06' DECLARE @EndTime datetime2 = '2016-09-18 13:46:03.28' SELECT * FROM [SQLShackDemo].[dbo].[Temporal_Table_Demo] FOR SYSTEM_TIME FROM @StartTime TO @EndTime SET STATISTICS TIME OFF |
Encendimos STATISTICS TIME para obtener el tiempo de ejecución de la consulta de manera precisa. Corramos la consulta y veamos el plan de ejecución usando la aplicación APEXSQL PLAN y el tiempo de ejecución. El plan de ejecución de la consulta nos muestra que un Escaneo de Índice Agrupado es realizado en la tabla temporal, ay que no hay índice en las columnas TimeStart y TimeEnd:
Y la consulta toma 107ms para correr completamente:
Creemos un índice no agrupado en las columnas TimeStart y TimeEnd:
|
1 2 3 4 5 6 7 8 |
USE [SQLShackDemo] GO CREATE NONCLUSTERED INDEX [IX_Temporal_Table_Demo_TStart_TEnd] ON [dbo].[Temporal_Table_Demo] ( [TimeStart] ASC, [TimeEnd] ASC ) GO |
Y ejecute la consulta de nuevo, el plan de ejecución generado usando la aplicación APEXSQL PLAN nos muestra ahora que el Escaneo de Índice Agrupado en la tabla Temporal cambió a Búsqueda de Índice:
Y que él tiempo de ejecución disminuyó claramente a 37ms:
Como puede ver, el índice que creamos en la tabla temporal mejoró el desempeño de la consulta y disminuyó el tiempo de ejecución considerablemente. Dependiendo de la carga de trabajo en su ambiente, usted puede personalizar el mejor índice que se ajuste a sus consultas y mejore el desempeño de su sistema.
Conclusión
La Tabla Temporal con Versiones de Sistema es un nuevo tipo de tabla que está enlazada a una tabla historial relacionada para mantener un historial para cada cambio de fila realizado en esa tabla. Usted puede desarrollar su propia solución dependiendo de la tabla temporal para rastrear los cambios de datos con el tiempo de cambio y retrotraer cualquier cambio desastroso sin la necesidad de restaurar los archivos de copia de seguridad. Usted puede obtener información acerca de las tablas temporales y las columnas periodo usando los objetos de sistema de SQL Server existentes o recién presentados. El buen punto aquí es que eso es manejado sólo por el sistema requiriendo ningún esfuerzo de su parte para definir los periodos de retención y seguir los procesos de inserción o eliminación en las tablas historial. Como cualquier nueva característica, pruébela cuidadosamente en su ambiente de pruebas antes de aplicarla al ambiente de producción.
Enlaces Útiles
- Tablas Temporales
- Creando una Tabla Temporal con Versiones de Sistema
- Cambiando el Esquema de una Tabla Temporal con Versiones de Sistema.
Vea más
Para investigar más, usted puede descargar ApexSQL Complete, un complemento gratis para SSMS y Visual Studio, que soporta todas las opciones de tabla temporal.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Restricciones en SQL Server: SQL NOT NULL, UNIQUE y SQL PRIMARY KEY - December 16, 2019
- Operaciones de copia de seguridad, truncamiento y reducción de registros de transacciones de SQL Server - November 4, 2019
- Qué elegir al asignar valores a las variables de SQL Server: sentencias SET vs SELECT T-SQL - November 4, 2019