Fran Otero

Programación C#, ASP.NET,SqlServer, Mobile y electrónica.

Change Data Capture. Histórico de cambios en SQL Server 2008 R2

SQL Server posee un mecanismo propio de traza de cambios DML y DDL que permite consultar versiones anteriores de una misma tabla, ver las modificaciones que ha sufrido durante un determinado espacio de tiempo, o un amplio espectro de modificaciones DDL de la propia tabla.

Para ello no es  necesario crear ningún mecanismo externo, ni implementar complejas soluciones basadas en triggers: basta con habilitar CDC: Change Data Capture.

  1. Habilitar CDC en la base de datos
  2. Habilitar CDC para la tabla correspondiente, puede habilitarse para todas las columnas o para columnas en particular
  3. Siempre que queramos utilizamos la consulta cdc.fn_cdc_get_all_changes_nombreTabla para obtener los cambio realizados entre dos LSN determinados.

Los pasos a seguir son muy sencillos, y pueden verse la demostración completa en el código de ejemplo. Se trata de la creación de una tabla de ejemplo con columnas IdProducto, Nombre, Precio, PrecioRebajado, donde despues de habilitar CDC se realizan tres inserciones, dos actualizaciones y un borrado. El resultado de consultar los cambios es una tabla como esta:

Las operaciones se ordenan por orden de ejecución, siendo la primera la más antigua. Podemos observar tres primeros INSERT y un DELETE. En estos casos la información que aparece es la insertada y la borrada de la tabla respectivamente.

En el caso de UPDATE, se insertan dos filas por actualización. La primera de ellas se corresponde con lo que había en la tabla antes del UPDATE y la segunda con lo que hay después. En esta operación también es interesante consultar la columna __$update_mask, que nos indica cuáles han sido las columnas afectadas por la actulización. Por ejemplo, 0x0A=0000 1010 indica que se han modificado la segunda y la cuarta, mientras que 0x02=0000 0010 indica que únicamente se ha visto afectada la segunda columna de la tabla.

Posted: 25/4/2011 21:30 por Fran Otero Otero | con 4 comment(s)
Archivado en:
Comparte este post:

Comentarios

david ha opinado:

Muchas gracias gran característica.

# April 26, 2011 4:52 AM

pregunton cojonero ha opinado:

Mola, a ver cómo afecta al rendimiento activarlo...salu2grz

# April 26, 2011 12:41 PM

Fran Otero Otero ha opinado:

Pues sí, creo que se le puede sacar mucho partido a esta característica. Un apunte interesante, lsn_time_mapping para obtener fecha y hora de comienzo y fin de transacción a partir de su LSN:

select *

from

cdc.fn_cdc_get_all_changes_dbo_Productos

( sys.fn_cdc_get_min_lsn('dbo_Productos'),

sys.fn_cdc_get_max_lsn(),'all') AllChanges

inner join

cdc.lsn_time_mapping TimeMapping On AllChanges.__$start_lsn=TimeMapping.start_lsn

# April 26, 2011 5:46 PM

Romny ha opinado:

Para sql 2005. hay algo parecido, para no recurrir con los triggers

@romnyd

# April 27, 2011 12:36 AM