Tablas temporales - SQL Server

¿Qué es una tabla temporal?

Las tablas temporales nos habilitan la posibilidad de recuperar datos de cualquier punto en el pasado y por cada cambio de datos (Actualizar, Eliminar, Combinar). Con la tabla temporal, los usuarios pueden restaurar datos de cambios accidentales (actualizar/eliminar), así como verificar todos los cambios en los datos, etc. La tabla temporal es una nueva característica de SQL Server disponible desde la versión CTP2 de SQL Server 2016. Una tabla temporal se compone de dos partes (tablas), la primera es la tabla en la que se habilita el seguimiento de datos y se llama una tabla versión original del sistema, esta tabla contiene datos actuales (reales), la segunda parte es la tabla en la que se almacenan los cambios de datos que se realizaron (Actualizar, Eliminar, Combinar) en una tabla con versión del sistema y se llama tabla de historial.

$H:\ApexSQL\My articles\Temporal table\25.png$

¿Qué debe tener cada tabla temporal?

Columnas datetime2 Cada tabla temporal debe tener dos columnas con un tipo de datos datetime2. Estas columnas son empleadas exclusivamente por el sistema para examinar el período de validez de cada fila siempre que se cambie una fila. Ambas columnas deben definirse como NOT NULL. Si no hay restricciones NOT NULL para las columnas datetime2, el sistema las mantendrá automáticamente como NOT NULL. Si las restricciones NULL se aplican a estas columnas, el sistema generará un error:

Msg 13587, Nivel 16, Estado 1, Línea 1 El período de la columna “Tiempo de Inicio” en una tabla temporal con versión original del sistema no puede ser anulable.

Una columna datetime2 debe tener configurada la opción GENERATED ALWAYS AS ROW START y la segunda columna datetime2 debe tener fijada la opción GENERATED ALWAYS AS ROW END. La columna GENERATED ALWAYS AS ROW START se refiere al momento en que los datos de la fila se volvieron actuales, básicamente en un INSERT/UPDATE del registro en la tabla temporal del sistema con versión original, el sistema establecerá la hora UTC actual en función del reloj del sistema en el que el servidor SQL Server se ejecuta. La columna GENERADA SIEMPRE COMO FIN DE FILA representa el momento en que los datos de la fila ya no eran actuales. Esta columna indica la hora en que se producen los cambios ACTUALIZAR/ BORRAR. El sistema establecerá la hora UTC actual. Clave principal así mismo una tabla temporal con versión del sistema debe contar con una clave primaria definida. Si la tabla no tiene una clave principal definida, aparecerá el siguiente error:

Msg 13553, Nivel 16, Estado 1, Línea 1 La tabla temporal versionada del sistema ‘test.dbo.people ‘debe tener una clave primaria definida.

Atributos La tabla debe tener una cláusula PERIOD FOR SYSTEM_TIME (<startcol>, <endcol>) con dos columnas datetime2 en ella. PERIOD FOR SYSTEM_TIME especifica el nombre de las columnas que el sistema manejara para registrar el período durante el cual un registro es válido:

StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)

La tabla también debe disponer de la cláusula SYSTEM_VERSIONING. SYSTEM_VERSIONING se usa para activar/desactivar las versiones del sistema para la tabla con versión del sistema:

StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)
WITH (SYSTEM_VERSIONING = ON);

El parámetro HISTORY_TABLE opcional define el nombre de la tabla de historial:

StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory));

Si no se especifica el nombre de la tabla de historial, SQL Server generará automáticamente un nombre de tabla de historial en un formato como este:

MSSQL_TemporalHistoryFor_693577509

Donde el número 693577509 es identificador (id) para la tabla con versión del sistema. Aquí un ejemplo de una tabla versionada por el sistema:

CREATE TABLE People(
PeopleID int PRIMARY KEY NOT NULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName varchar(25),
StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory));

Después de ejecutar esta declaración, se producirá la tabla temporal de personas y se marcará como una tabla con versión del sistema. Dentro de la tabla de Personas se creará la tabla de Historia de Personas y se indicará como tabla de Historial:

$H:\ApexSQL\My articles\Temporal table\1.png$

Generación de una tabla temporal con versión del sistema

Según el caso, se puede generar una tabla temporal con versión del sistema modificando una tabla existente de la base de datos añadiendo atributos temporales o generando una nueva tabla temporal con versión del sistema. Hay tres formas de lograr una tabla temporal con versión del sistema: tabla temporal con una tabla de historial anónima, creando una tabla temporal con una tabla de historial predeterminada o creando una tabla temporal con una tabla de historial definida por el usuario. Tabla temporal con una tabla de historial anónima Básicamente, al crear este tipo de tabla temporal, la única opción que se omite en el código que se muestra arriba es un parámetro HISTORY_TABLE:

CREATE TABLE People(
PeopleID int PRIMARY KEY NOT NULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName varchar(25),
StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)
WITH (SYSTEM_VERSIONING = ON);

Lo que quiere decir es que, cuando se ejecute este código, SQL Server generará automáticamente el nombre de la tabla de historial y generara una tabla de historial en el mismo esquema que la tabla actual/temporal. El nombre de la tabla de historial será así:

MSSQL_TemporalHistoryFor_613577224

La generación de una tabla temporal con una tabla de historial predeterminada Este enfoque le permite al usuario elegir un nombre mucho más amigable/significativo para la tabla de historial en el parámetro HISTORY_TABLE, en lugar de hacer que el sistema elija un nombre.

CREATE TABLE People(
PeopleID int PRIMARY KEY NOT NULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName varchar(25),
StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory));

El nombre del esquema (dbo) es indispensable para el parámetro HISTORY_TABLE; de lo contrario, aparecerá el siguiente error:

Msg 13539, Nivel 15, Estado 1, Línea 54 La configuración de SYSTEM_VERSIONING en ON falló debido a que la tabla de historial ‘PeopleHistory’ no está especificada en un formato de nombre de dos partes.

Generar una tabla temporal con una tabla de historial definida por el usuario La creación de una tabla de historial definida por el usuario es conveniente cuando en la tabla de historial se requieren opciones adicionales, como un índice, para lograr un mejor rendimiento de consulta. La tabla de historial no puede disponer de una clave principal/ externa, desencadenadores, restricciones de tabla, índice único. De lo contrario, se mostrará el siguiente error:

Msg 13515, Nivel 16, Estado 1, Línea 45 La configuración de SYSTEM_VERSIONING en ON falló porque la tabla de historial ‘test.dbo.PeopleHistory’ tiene claves únicas personalizadas definidas. Tome en cuenta que debe dejar caer todas las claves únicas y volver a intentarlo.

El historial y la tabla de versión del sistema deben tener el mismo número de columnas, los mismos nombres de columna y los mismos tipos de datos para que una tabla temporal se cree correctamente. El siguiente ejemplo muestra el efecto de generar una tabla de historial con un nombre de columna diferente (NickName2) que el nombre de la columna (NickName) en la tabla versionada. Copie el siguiente código en el editor de consultas y ejecute:

CREATE TABLE PeopleHistory(
PeopleID int NOT NULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName2 varchar(25),
StartTime datetime2 NOT NULL,
EndTime2 datetime2 NOT NULL
)
CREATETABLE dbo.People(
PeopleID int PRIMARYKEYNOTNULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName varchar(25),
StartTime datetime2 GENERATED ALWAYS ASROWSTARTNOTNULL,
EndTime datetime2 GENERATED ALWAYS ASROWENDNOTNULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory));

Se levantará el siguiente error:

Msg 13524, Nivel 16, Estado 1, Línea 45 La configuración de SYSTEM_VERSIONING en ON falló porque la columna ‘NickName2’ en el ordinal 4 en la tabla de historial ‘test.dbo.PeopleHistory’ tiene un nombre diferente al de la columna ‘NickName’ en el mismo ordinal de la tabla ‘Test.dbo.People’.

El siguiente código generara con éxito la tabla de historial definida por el usuario y la versión del sistema:

CREATE TABLE PeopleHistory(
PeopleID int NOT NULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName varchar(25),
StartTime datetime2 NOT NULL,
EndTime2 datetime2 NOT NULL
)
CREATETABLE dbo.People(
PeopleID int PRIMARYKEYNOTNULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName varchar(25),
StartTime datetime2 GENERATED ALWAYS ASROWSTARTNOTNULL,
EndTime datetime2 GENERATED ALWAYS ASROWENDNOTNULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory));

Modifique una tabla no temporal para que esta sea una tabla temporal versionada por el sistema Para convertir una tabla existente, genere una tabla temporal, para ello la versión del sistema debe estar habilitada. Vamos a crear una tabla e insertar algunos datos:

CREATE TABLE People(
PeopleID int PRIMARY KEY NOT NULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName varchar(25)
)
INSERTINTO dbo.People
(
PeopleID,
Name,
LastName,
NickName
)
VALUES
(
1, — PeopleID – int‘Marko’, — Name – varchar‘Zivkovic’, — LastName – varchar‘Ziva’— NickName – varchar
)

Ahora ensaye habilitar el control de versiones del sistema:

ALTER TABLE dbo.People
SET (SYSTEM_VERSIONING = ON)

Aparecerá el siguiente error:

Msg 13510, Nivel 16, Estado 1, Línea 1 No se puede configurar SYSTEM_VERSIONING en ON cuando el período SYSTEM_TIME no está determinado.

Tal como se mencionó previamente, cada tabla temporal debe tener dos columnas con el tipo de datos datetime2, de otra forma se mostrará el error anterior. Ahora tratemos de ejecutar la siguiente declaración con dos columnas datetime2:

ALTER TABLE dbo.People
ADD StartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
EndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

El resultado será:

Msg 4901, Nivel 16, Estado 1, Línea 4 ALTER TABLE solo deja agregar columnas que pueden contener nulos, o tener una definición DEFAULT especificada, o la columna que se añade es una columna de identidad o marca de tiempo, o de otra forma si no hay ninguna de las anteriores se cumplen las condiciones, la tabla debe estar vacía para permitir la adición de esta columna. La columna ‘StartTime’ no se puede incluir a la tabla no vacía ‘People’ porque no cumple con estas condiciones.

Este error se genera porque el sistema no deja agregar columnas NOT NULL en la tabla que ya tienen datos. Para resolver esto, agreguemos un valor predeterminado para las columnas datetime2 y activemos las versiones del sistema con el nombre predeterminado para la tabla de historial:

ALTER TABLE dbo.People
ADD StartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN DEFAULT GETUTCDATE(),
EndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN DEFAULT CONVERT(DATETIME2, ‘9999-12-31 23:59:59.9999999’),
PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
Go
ALTER TABLE dbo.People
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.PeopleHistory))
GO

Ahora, cuando se ejecute la instrucción sugerida sobre la tabla, People se convertirá en tabla temporal:

$H:\ApexSQL\My articles\Temporal table\3.png$

el indicador HIDDEN entonces ocultará estas columnas datetime2 para que no aparezcan en el resultado de la consulta (SELECT *):

SELECT * FROM dbo.People

$H:\ApexSQL\My articles\Temporal table\10.png$

Para visualizar las columnas datetime2 se deben incorporar explícitamente en la lista de instrucciones SELECT:

SELECT Name, LastName, NickName, StartTime, EndTime FROM dbo.People

$H:\ApexSQL\My articles\Temporal table\14.png$

La instrucción INSERT INTO funcionará sin incluir valores para estas columnas. La siguiente instrucción INSERT se ejecutará con éxito, aunque las columnas PERIOD (StartTime, EndTime) se omiten de la lista de instrucciones INSERT

INSERT INTO dbo.People
VALUES
(2, ‘Alan’, ‘Beker’, ‘Al’)

Insertar datos Hay varias formas de insertar datos en una tabla temporal cuando las columnas de PERÍODO no están OCULTAS.

Inserte datos cuando las columnas de PERÍODO se evitan y eliminan de la lista de columnas de la sentencia INSERT:

INSERT dbo.People (PeopleID, Name, LastName, NickName) VALUES (2, ‘Alan’, ‘Beker’, ‘Al’)

Esta declaración se ejecutará correctamente porque el sistema generará automáticamente valores para las columnas de PERÍODO.
Inserte datos cuando las columnas de PERÍODO estén en la lista de columnas de la sentencia INSERT:

INSERT dbo.People (PeopleID, Name, LastName, NickName, StartTime, EndTime) VALUES (2, ‘Alan’, ‘Beker’, ‘Al’, DEFAULT, DEFAULT)

Si las columnas de PERÍODO en la columna INSERTAR, deben especificarse por con DEFAULT para su valor.
Insertar datos cuando la lista de columnas INSERT no está especificada

INSERT dbo.People VALUES (3, ‘Joe’, ‘Anderson’, ‘Jo’, DEFAULT, DEFAULT)

El valor predeterminado se debe distinguir en la lista de VALORES para las columnas PERÍODO, si no se detallan valores para las columnas PERÍODO, aparecerá el siguiente error: Código:

INSERT dbo.People
VALUES (4, ‘Mike’, ‘Carter’, ‘Mik’)

Resultado:

Msg 213, Nivel 16, Estado 1, Línea 16 El nombre de la columna o el número de valores incluidos no coincide con la definición de la tabla.

Al insertar datos en una tabla temporal, la tabla versionada del sistema (actual) se verá afectada, la tabla de historial estará vacía:

$H:\ApexSQL\My articles\Temporal table\17.png$

Tabla versionada del sistema:

SELECT p.* FROM dbo.People p

$H:\ApexSQL\My articles\Temporal table\13.png$

Tabla de historia:

SELECT ph.* FROM dbo.PeopleHistory ph

$H:\ApexSQL\My articles\Temporal table\5.png$

Los valores insertos en la columna Hora de inicio de la tabla con versión del sistema es el momento en que se ejecuta la instrucción INSERT y el valor en Hora final es el máximo datetime2 que es 9999-12-31 23: 59: 59.9999999 Actualización de datos Ejecutemos la siguiente declaración: que actualizará el NickName de Ziva a Mare:

UPDATE dbo.People
SET dbo.People.NickName = ‘Mare’
WHERE dbo.People.PeopleID = 1

Ahora, recupere datos de la tabla temporal e histórica:

–Get the records from the temporal table
SELECT p.* FROM dbo.People p
–Get the records from the history table
SELECT ph.* FROM dbo.PeopleHistory ph

$H:\ApexSQL\My articles\Temporal table\15.png$

La tabla temporal (actual) que se actualiza y en la columna Hora de inicio se establece la hora en que se ejecuta la instrucción de actualización. De esta forma el sistema insertará automáticamente el registro en la tabla de historial con los datos que estaban antes de la actualización y en la columna Tiempo de finalización de la tabla de historial se insertará la hora en que se ejecute la instrucción de actualización. Intentemos actualizar la tabla de historial PeopleHistory y establecer NickName de Ziva a Maruska:

UPDATE dbo.PeopleHistory
SET
dbo.PeopleHistory.NickName = ‘Maruska’
WHERE dbo.PeopleHistory.PeopleID = 1

Esto devolverá un error:

Msg 13561, Nivel 16, Estado 1, Línea 54 No se pueden actualizar las filas en una tabla de historial temporal ‘test.dbo.PeopleHistory’.

La única forma de renovar una tabla de historial (PeopleHistory) es configurar SYSTEM_VERSIONING en OFF en la tabla con versión del sistema (Personas):

ALTER TABLE dbo.People
SET (SYSTEM_VERSIONING = OFF)

Dado que SYSTEM_VERSIONING está configurado en DESACTIVADO, el enlace entre las tablas con versión del sistema (Personas) y el historial (PeopleHistory) desapareció y ambas tablas se convertirán como cualquier otra tabla regular: ahora intentemos actualizar la columna de Hora de inicio en la tabla Personas:

$H:\ApexSQL\My articles\Temporal table\8.png$

UPDATE dbo.People
SET
StartTime=GETUTCDATE()
WHERE dbo.People.PeopleID = 2

Aparecerá el siguiente error:

Msg 13537, Nivel 16, Estado 1, Línea 54 No se pueden actualizar las columnas GENERADAS SIEMPRE en la tabla “test.dbo.Personas”.

De tal forma que, para estar disponible para actualizar las columnas de PERIODO, se debe ejecutar la siguiente declaración:

ALTER TABLE dbo.People
DROP PERIOD FOR SYSTEM_TIME
GO

Ahora ejecuta la sentencia UPDATE:

UPDATE dbo.People
SET
StartTime=GETUTCDATE()
WHERE dbo.People.PeopleID = 2

Aparecerá el siguiente mensaje:

(1 fila (s) afectadas)

Eliminar datos Vamos a eliminar el registro de la tabla de Personas donde el ID de Gente es 1

DELETE FROM dbo.People WHERE PeopleID = 1

Este código permitirá eliminar el registro de la tabla temporal (Personas) e insertará el registro eliminado en la tabla de historial (PeopleHistory): para ello recupere datos de la tabla temporal y de historial:

–Get the records from the temporal table
SELECT p.* FROM dbo.People p
–Get the records from the history table
SELECT ph.* FROM dbo.PeopleHistory ph

$H:\ApexSQL\My articles\Temporal table\16.png$

De la imagen líneas arriba, queda claro que el registro se eliminó de la tabla People y luego se insertó en la tabla de historial PeopleHistory con EndTime del registro como el tiempo de ejecución de la sentencia DELETE. Si se ejecuta la declaración de eliminación en la tabla de historial:

DELETE FROM dbo.PeopleHistory WHERE PeopleID = 1

El resultado será:

Msg 13560, Nivel 16, Estado 1, Línea 61 No se pueden eliminar filas de una tabla de historial temporal ‘test.dbo.PeopleHistory’.

Esto está ocurriendo debido a que las operaciones de actualización/eliminación no están permitidas en la tabla de historial porque la opción SYSTEM_VERSIONING está habilitada. Para eliminar el historial y la tabla versionada del sistema, ejecute el siguiente código:

–Disable the system versioning
ALTER TABLE dbo.People SET (SYSTEM_VERSIONING = OFF)
GO
–Drop Period definition
ALTER TABLE dbo.People
DROP PERIOD FOR SYSTEM_TIME
GO
–Now drop People and PeopleHistory tables
DROP TABLE dbo.People
DROP TABLE dbo.PeopleHistory

Acerca de
Últimas entradas

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

Últimas entradas de Marko Zivkovic (ver todo)

Cómo poder consultar los datos de Excel utilizando servidores vinculados a SQL Server - August 1, 2019
Cómo poder importar un archivo plano a una base de datos de SQL Server usando el asistente para Importar un archivo plano - August 1, 2019
Tablas temporales – SQL Server - August 1, 2019

¿Qué es una tabla temporal?

¿Qué debe tener cada tabla temporal?

Generación de una tabla temporal con versión del sistema

Related posts: