Versionat de Taules a SQL Server: Què és i com funciona?

La importància de la traçabilitat dels canvis

Una de les peticions que ens fan freqüentment en molts dels projectes que iniciem, és tenir una traçabilitat dels canvis que es fan a qualsevol taula.

Per a alguns dels projectes, només cal una traçabilitat simple i conèixer la data, hora de creació i última actualització de la taula, així com els usuaris que van realitzar aquestes accions.

Però en altres casos, es requereix un historial complet de tots els canvis realitzats, per a això es recorria a generar una taula d’històric de canvis, la implementació de l’emplenament d’aquesta taula normalment tenia 2 possibles opcions:

• Utilitzar els poc populars Triggers que es disparen a la base de dades a nivell de fila en realitzar-se una operació d’Update/Insert/Delete
• Sobrecarregar la lògica de negoci i implementar-lo en els mètodes on es faci qualsevol canvi sobre la taula

Sigui quina sigui l’elecció que fem entre les dues opcions anteriors, ens obliga a incórrer a un cost tant econòmic com de rendiment, que podríem estalviar-nos majoritàriament, si apliquem una de les moltes característiques no tan conegudes de què disposa SQL Server .

Vegem a continuació que característiques estem parlant.

Què és el System Versioning?

La característica de la qual us volem parlar i mostrar en aquesta ocasió és el System Versioning, una característica que automatitza de manera transparent a l’aplicació la generació i el manteniment de la Taula Històrica.

Després d’activar aquesta característica en SQL Server, es crea automàticament la Taula Històrica amb el mateix Schema que la Taula Principal, i es comencen a registrar tots els canvis sobre la Taula Principal, generant una fila a la Taula Històrica de forma automàtica cada vegada que es fa qualsevol tipus d’actualització sobre la Taula Principal.

Com habilitem el System Versioning en una taula existent?

Per habilitar el System Versioning a la nostra taula existent, tan sols hem de fer els passos següents:

Pas 1

El primer és modificar el schema de la Taula Principal, afegint 3 columnes que seran utilitzades internament per registrar les dates de vigència de cada fila:

alter table dbo.[Contacto]
add
    TimeStart datetime2 generated always as row start not null default getutcdate(),
    TimeEnd datetime2 generated always as row end not null default convert(datetime2, '9999-12-31 23:59:59.9999999'),
    period for system_time (TimeStart, TimeEnd);

Pas 2

El pas següent és activar la característica de Versionat a la Taula Principal, indicant el nom de la Taula Històrica que es crearà.

alter table dbo.[Contacto]  
set (system_versioning = on (HISTORY_TABLE = dbo.[Contacto_History]));

Un cop activada la característica, veurem al SSMS (SQL Server Management Studio) un petit canvi visual en la forma com es mostra la Taula Principal, com veieu a continuació:

Sense Versionat	Amb Versionat

A més, si despleguem la Taula Principal, també veurem el nom i les propietats de la Taula Històrica, com podeu veure a la imatge següent:

I d’aquesta manera tan senzilla, ja tindríem habilitat el System Versioning a la nostra Taula.

Però, quines accions pot dur a terme aquesta característica de SQL Server?

System Versioning en acció

Sobre la Taula Principal (Contacte) que ja hem versionat, realitzem les operacions següents:

Pas 1

Inserim un nou Contacte, amb nom = ‘Contacto Versionado’:

insert into Contacto (Nombre, Telefono)  values ('Contacto Versionado',  '+34 666 66 66')

Obtenint la següent fila:

Pas 2

Esperem uns minuts, i tot seguit realitzem un Update del telèfon:

update Contacto set Telefono = '+34 999 99 99' where Id = 1

Pas 3

Esperem uns minuts més, i realitzem un altre Update, en aquesta ocasió del Nom:

update Contacto (Nombre, Telefono)  set Nombre = 'Contacto Versionado 2.0' where Id = 1

Pas 4

Finalment, examinem el contingut de les dues taules:

select Id, Nombre, Telefono, TimeStart, TimeEnd from Contacto where Id = 1

select Id, Nombre, Telefono, TimeStart, TimeEnd from Contacto_History where Id = 1

Com era previsible a la Taula Principal podem observar que es conserva la informació actualitzada després de l’últim Update de l’acció número 3, mentre que a la Taula d’History tenim el contingut previ de la Taula Principal a realitzar cadascun dels Updates realitzats a les accions número 2 i 3.

Com extreure informació d’una data i hora en SQL Server?

Després de l’activació de la característica System Versioning, una de les consultes molt útils que també podem fer és obtenir l’estat d’una fila a una data i hora passada determinada.

Per fer aquesta consulta, utilitzarem la clàusula FOR SYSTEM_TIME en realitzar un SELECT sobre la Taula Principal.

Partint de la data i hora d’execució de les 3 accions, tindríem la taula següent:

Número d’Acció	Descripció	Data i Hora Acció
1	Insert del Contacte	2022-11-28 12:34:36
2	Update del Telèfon	2022-11-28 12:36:42
3	Update del Nom	2022-11-28 12:40:54

Vegem-ne ara una sèrie d’exemples:

Exemple 1

Obtenim la versió del Contacte, just després d’executar l’Acció 1 i abans de l’Acció 2:

select Id, Nombre, Telefono, TimeStart, TimeEnd from Contacto for system_time as of '2022-11-28 12:35:00' where Id = 1

Exemple 2

Obtenim la versió del Contacte just després de l’Acció 2 i abans de l’Acció 3:

select Id, Nombre, Telefono, TimeStart, TimeEnd from Contacto for system_time as of '2022-11-28 12:37:00' where Id = 1

Exemple 3

I, finalment, obtenim la informació del Contacte tot just després de l’Acció 3:

select Id, Nombre, Telefono, TimeStart, TimeEnd from Contacto for system_time as of '2022-11-28 12:37:00' where Id = 1

Com extreure informació històrica a SQL Server?

A més de la clàusula AS OF, també podríem utilitzar les clàusules següents per consultar informació històrica:

• FROM <start_date_time> TO <end_date_time>
• BETWEEN <start_date_time> AND <end_date_time>
• CONTAINED IN (<start_date_time>, <end_date_time>)
• ALL

FROM i BETWEEN

FROM y BETWEEN tornen files que se superposen amb el període especificat.

CONTAINED IN

CONTAINED IN retornen aquelles files que van existir íntegrament dins del període.

ALL

Amb ALL obtindríem totes les files històriques.

I amb aquesta senzilla característica de SQL Server, System Versioning, podrem tenir una traçabilitat detallada de tots els canvis que es fan a qualsevol Taula del nostre projecte.

Per si vols desenvolupar més en profunditat la creació de taules, et deixo el següent enllaç.

Francisco Valcarreras – Senior Product Owner at Itequia