Marko Zivkovic
C:\Users\Marko\AppData\Local\Microsoft\Windows\INetCacheContent.Word\14.png

Cómo consultar los datos en tablas temporales de versión del sistema en SQL Server

July 31, 2019 by

Este artículo versara sobre cómo realizar consultas de tablas temporales en SQL Server utilizando FOR SYSTEM_TIME clause y sus cuatro subcláusulas AS OF, FROM TO, BETWEEN AND, CONTENED IN. Por otra parte, también se considerará la manera de limpiar la tabla del historial para lograr tener un tamaño manejable.

La cláusula FOR SYSTEM_TIME se utiliza para efectuar cualquier tipo de análisis basado en el tiempo con cuatro subcláusulas y puede emplearse para cada tabla en la consulta de forma independiente. Además, el mismo se puede utilizar dentro de funciones con valores de tabla y procedimientos almacenados.

Previamente para comenzar, vamos a crear una base de datos de SQL Server con una tabla temporal dbo.People. Al iniciar solo puedes copiar y ejecutar el siguiente código en la ventana de consulta:

Esto creará la tabla dbo.People con versiones del sistema y la tabla histórica correspondiente dbo.HistoryPeople:

Para lograr tener más información sobre las tablas temporales de SQL Server, visite Tablas temporales en SQL Server.

Insertaremos y actualizaremos algunos datos:

Tanto la versión del sistema como la tabla de historial se pueden analizar usando el estándar

SELECT * FROM <TableName> consulta de consulta.


Consultando la tabla dbo.People con versión del sistema

Código:

Resultado:

Esto restituirá todos los datos actuales:


Consultando la tabla dbo.HistoryPeople del historial de SQL Server

Código:

Resultado:

Esto repondrá todos los cambios de datos (Actualización, Eliminar, Fusionar) que se efectuaron en la tabla versionada del sistema dbo.People:


ALL subcláusula

Esta cláusula restituirá todas las filas de la tabla del historial de dbo.People system versioned y dbo.PeopleHistory.

Escriba el siguiente código:

Resultado:

El mismo efecto se puede lograr utilizando la siguiente consulta y omitir la cláusula FOR SYSTEM_TIME:


AS FOR sub cláusula

El uso de la subcláusula AS FOR puede restituir un estado de los datos para cada fila que incluye los valores actuales en el momento especificado en el pasado. La subcláusula AS FOR repone todos los registros de la versión del sistema de SQL Server y la tabla de historial que ejecuto con los siguientes criterios:

StartTime <= SpecifiedTime AND EndTime > SpecifiedTime

La siguiente consulta repondrá los registros que eran válidos en un momento específico en el pasado (‘2017-01-26 13:52:29’) para el PeopleID específico = 2:

Resultado:

El mismo resultado se puede obtener escribiendo el siguiente código y eludiendo el uso del FOR SYSTEM_TIME AS OF:


Subcláusula DE <fecha_hora_inicio> A <fecha_hora_final>

Esta subcláusula temporal es conveniente cuando se necesita lograr cambios para todos los registros que estaban activos entre < fecha_hora_inicio > y < fecha_hora_final >. La subcláusula FROM … TO … restablece los datos de las dos versiones del sistema de las tablas y el historial.

Se deben acatar los siguientes criterios:

StartTime < <end_date_time> AND EndTime > <start_date_time>

Ejecuta el siguiente código:

Resultado:

El mismo resultado se puede obtener al escribir el siguiente código, sin usar FOR SYSTEM_TIME FROM… TO…


ENTRE subcláusula <fecha_hora_inicio> AND <fecha_hora_final>

Esto es casi igual que la subcláusula FROM … TO … a excepción que BETWEEN … TO … incluye los registros que estaban activos en <fecha_hora_final> (es decir, StartTime = <fecha_hora_final>). La subcláusula BETWEEN … TO … restituye toda la fila que cumple los siguientes principios:

StartTime <= <fecha_hora_final> AND EndTime > <fecha_hora_inicio>

Ejecuta el siguiente código:

Resultado:

El mismo resultado se puede obtener usando este código, sin utilizar la subcláusula BETWEEN… TO…


CONTENIDO EN subcláusula <fecha_hora_inicio> <fecha_hora_final>

Esta subcláusula temporal repondrá solo los registros que existían dentro de los límites del período especificado. La subcláusula CONTAINED IN reintegra toda la fila que cumple los siguientes criterios:

StartTime >= <start_date_time> AND EndTime <= <end_date_time>

Ejecuta el siguiente código:

Resultado:

El mismo resultado se puede obtener usando este código:


Limpiando la tabla de historial de SQL Server

Con el tiempo la tabla de historia se incrementa significativamente. Dado que no se permite insertar, actualizar o eliminar datos de la tabla de historial, la única forma de limpiar la tabla de historial es deshabilitar primero el versionamiento del sistema:

Suprimir datos innecesarios de la tabla de historial:

y luego volver a habilitar el control de versiones del sistema:

La limpieza de la tabla de historial en las bases de datos de Azure SQL es distinto, ya que las bases de datos de Azure SQL tienen soporte incorporado para limpiar la tabla de historial. Primero, la limpieza del estancamiento del historial temporal debe habilitarse en un nivel de base de datos:

A continuación, establezca el período de retención por tabla:

Esto eliminará todos los datos en la tabla de historial con más de 90 días.

Las bases de datos locales de SQL Server 2016 no son compatibles con TEMPORAL_HISTORY_RETENTION y HISTORY_RETENTION_PERIOD y cualquiera de estas dos consultas anteriores se ejecutan en las bases de datos locales de SQL Server 2016, se producirán los siguientes errores:

Para TEMPORAL_HISTORY_RETENTION el error será:

Msg 102, Nivel 15, Estado 6, Línea 34
Sintaxis incorrecta cerca de ‘TEMPORAL_HISTORY_RETENTION’.

Para HISTORY_RETENTION_PERIOD el error será:

Mensaje 102, Nivel 15, Estado 1, Línea 39
Sintaxis incorrecta cerca de “HISTORY_RETENTION_PERIOD”.

Marko Zivkovic

Marko Zivkovic

Marko es un ingeniero mecánico a quien le gusta jugar basketball, football (table-soccer) y escuchar música rock. Está interesado en código SQL, desarrollo PHP y técnicas en HTML y CSS.
Actualmente trabajando para ApexSQL LLC como un Ingeniero de Ventas de Software, él está ayudando a los clientes con problemas técnicos y hace aseguramiento de calidad para los complementos ApexSQL Complete, ApexSQL Refactor y ApexSQL Search.

Ver todas las entradas de Marko Zivkovic
Marko Zivkovic
515 Views