El seguimiento de cambios para SQL Server es una tecnología flexible y fácil de usar para supervisar tablas de inserciones, actualizaciones y eliminaciones. En esta publicación, analizaré cómo comenzar con el seguimiento de cambios en SQL Server y mostraré un ejemplo de cómo comenzar con él.
Seguimiento de cambios en SQL Server
El seguimiento de cambios es un mecanismo ligero para el seguimiento de las filas que se han insertado, actualizado y eliminado en las tablas supervisadas por seguimiento de cambios. El seguimiento de cambios apareció por primera vez en SQL Server 2008 y ha estado en todas las versiones desde entonces. Aún mejor, el seguimiento de cambios está disponible en todas las ediciones de SQL Server, incluso en la edición gratuita express.
En pocas palabras, así es como funciona: En las tablas que tienen habilitado el seguimiento de cambios, el cambio neto de cada fila se rastrea internamente y se puede acceder a él a través de las funciones de seguimiento de cambios. Cada cambio tiene un ID de versión adjunto, y habrá un nuevo ID de versión para cada fila que se inserte, actualice o elimine. La versión de seguimiento de cambios es un entero de 8 bytes (BIGINT) que refleja el ID de cambio más reciente de esa base de datos. Es importante tener en cuenta que la versión de seguimiento de cambios no es específica de la tabla: una operación DML en cualquier tabla rastreada de cada base de datos incrementará el número de versión. El número de versión siempre será secuencial, pero no necesariamente será contiguo dentro de una sola tabla si hay más de una tabla habilitada para el seguimiento de cambios.
El seguimiento de cambios está habilitado a nivel de base de datos. Después de eso, cada tabla que se monitoreará debe inscribirse individualmente en el seguimiento de cambios. Cada tabla que se monitoree mediante seguimiento de cambios debe tener una clave primaria, ya que este es el identificador de nivel de fila utilizado para informar sobre las operaciones de DML dentro del seguimiento de cambios. Al habilitar el seguimiento de cambios a nivel de tabla, puede optar por realizar un seguimiento de las columnas modificadas en la actualización más reciente, lo que le dará una mayor visibilidad de lo que se cambió.
Una vez configurado, usar el seguimiento de cambios es un proceso relativamente simple. Hay algunas funciones, la más notable, CHANGE_TRACKING_CURRENT_VERSION () y CHANGETABLE (), que se pueden usar para verificar el sello de versión actual en el seguimiento de cambios y recuperar la lista de cambios recientes. Voy a demostrar ambas funciones en breve.
El seguimiento de cambios no es Registro de auditoría
Voy a tener cuidado de no usar las palabras auditoría o registro para describir el seguimiento de cambios. Permítaseme ser claro: este no es un mecanismo de registro completo. El historial de cambios no se rastrea en absoluto: el seguimiento de cambios solo informa el hecho de que se produjo un cambio, pero no conserva el historial de versiones. Considere el caso de una fila de datos con un ID de 1234. Esa fila se inserta, se actualiza 5 veces y se elimina. El seguimiento de cambios no muestra el historial de inserción, actualización y eliminación; más bien, reportaría solo el cambio neto, que la fila ID 1234 se eliminó. Si su proceso de carga requiere un historial de registro detallado para cada cambio (en lugar de solo el delta de todos los cambios), tendría que usar algo como la captura de datos de cambio.
Configurar el seguimiento de cambios en SQL Server
Habilitar el seguimiento de cambios a nivel de tabla es un proceso de dos pasos. En primer lugar, debe estar habilitado en la base de datos. Esto se puede hacer a través de la interfaz de usuario en las Propiedades de la base de datos, en la pestaña Seguimiento de cambios.
Como se muestra, no hay mucho que configurar al habilitar el seguimiento de cambios en una base de datos. Simplemente establezca el valor de Seguimiento de cambios en True para configurar el seguimiento de cambios para esa base de datos. Opcionalmente, también se puede ajustar el valor del período de retención. El valor predeterminado es 2 días, que he anulado en este ejemplo para usar 14 días en su lugar. Al igual que con la mayoría de las operaciones de interfaz de usuario, hay un comando T-SQL para hacer lo mismo. El comando para configurar el seguimiento de cambios en esta base de datos se encuentra a continuación.
Después de este paso, el seguimiento de cambios está habilitado, pero todavía no está rastreando nada. Todavía tiene que habilitarse para que se realice el seguimiento de cada tabla. La interfaz de usuario de Propiedades de tabla lo hace muy fácil.
Como se muestra, simplemente cambiar el valor de Seguimiento de cambios a True habilita el seguimiento de cambios para esta tabla. En este ejemplo, también opté por rastrear las columnas cambiadas durante las actualizaciones (más sobre esto en un poco).
El último paso anterior se repetiría para cada tabla a seguir en seguimiento de cambios. Una vez que se habilite el seguimiento de cambios, cualquier cambio (inserciones, actualizaciones o eliminaciones) en esa tabla se almacenará en la caché de seguimiento de cambios.
Configurar el seguimiento de cambios
Para el ejemplo anterior, voy a insertar, actualizar y eliminar algunos datos para demostrar cómo acceder a los datos de seguimiento de cambios generados para esas operaciones DML. Para referencia, aquí está la estructura de la tabla.
Mostré anteriormente cómo habilitar el seguimiento de cambios para una sola tabla mediante la interfaz de usuario. Prefiero usar T-SQL para esta tarea, ya que es más fácil de repetir. Habilitar el seguimiento de cambios para la tabla que creé anteriormente se puede hacer como se muestra aquí:
Recuerde que mencioné anteriormente que el seguimiento de cambios usa un ID de versión para rastrear la versión actual de las tablas rastreadas. Ese ID de versión es nuestro marcador de línea de tiempo para detectar cambios. Para recuperar ese valor, hay una función muy simple: CHANGE_TRACKING_CURRENT_VERSION (). Se utiliza como se muestra a continuación.
En mi sistema de pruebas, este valor es 470 (ya que he ejecutado varias pruebas antes de escribir esto). Este es el punto de partida, y cualquier cambio realizado a partir de este punto activaría un nuevo número de versión. Tomaré nota de ese valor y ahora haré algunos cambios en la tabla descrita anteriormente. Insertaré un puñado de filas para mostrar cómo el seguimiento de cambios muestra los insertos.
Después de insertar estas seis filas, compruebo de nuevo el valor CHANGE_TRACKING_CURRENT_VERSION () y encuentro que el valor ahora es 476. Se ha aumentado en 6-uno por fila insertada, que es lo que esperaría.
Usando Funciones de Seguimiento de cambios
A continuación, usaremos la función de seguimiento de cambios CHANGETABLE () para mostrar los cambios netos en esta tabla.
Para desglosar esto:
- CHANGETABLE es la función de sistema con valor de tabla que devolverá la lista de cambios almacenados en seguimiento de cambios
- CAMBIOS indica que estoy buscando los cambios ya que la versión especificada
- @ver es la variable que configuré para almacenar el número de versión. CHANGETABLE devolverá todos los resultados que reflejen los cambios desde esta versión. Tenga en cuenta que puede usar una variable como lo hice yo, o simplemente pasar un número escalar (usar el literal 470 aquí habría logrado lo mismo)
Cuando corro el código anterior, recibo el siguiente conjunto de resultados.
Esto me indica la versión de la inserción y/o actualización, la operación (I, U o D para insertar, actualizar o eliminar, respectivamente), la máscara de columna para las operaciones de actualización (más sobre esto momentáneamente) y la clave principal de la fila afectada por este cambio. Como CHANGETABLE () devuelve una tabla, podría unir fácilmente este conjunto de resultados a la tabla original para ver la operación de cambio junto con los datos actuales de esa tabla.
Esto se verá un poco diferente para una operación de actualización. A continuación, ejecutaré una instrucción de actualización, pero primero, voy a anotar la versión actual de seguimiento de cambios (que sigue siendo 476).
Ahora la instrucción update, que actualizará dos filas de la tabla:
Ahora, cuando corro el código de CHANGETABLE() desde arriba, usando la versión de seguimiento de cambios más reciente (476) como punto de partida, obtengo un conjunto de resultados diferente:
Estos son los metadatos para todos los cambios desde la versión 476, que solo incluye las dos filas actualizadas de la instrucción UPDATE anterior. Observe que la versión de creación es nula, porque este cambio fue una actualización, no una inserción. Además, el valor SYS_CHANGE_COLUMNS ahora está rellenado, aunque el valor realmente no nos muestra lo que ha cambiado (todavía). Este es un buen momento para hablar de la función de seguimiento de cambios CHANGE_TRACKING_IS_COLUMN_IN_MASK(). Esta función comprobará si la columna especificada se ha actualizado desde la versión más reciente. Su sintaxis es un poco peculiar,pero para verificar si el nombre medio se actualizó, la consulta se vería así:
Honestamente, no se si alguna vez he usado la función CHANGE_TRACKING_IS_COLUMN_IN_MASK. Es un poco molesto porque tienes que ejecutar esto para cada columna que quieras verificar. La mayor parte de mi trabajo está en el almacenamiento de datos, y me he encontrado con pocos casos en los que necesito saber exactamente qué columnas se actualizaron, solo quiero saber si la fila se ha actualizado. Sin embargo, para otros escenarios (especialmente en OLTP), puedo ver la necesidad de esto.
he demostrado inserciones y actualizaciones. Veamos cómo se vería un borrado. Una vez más, tomaré nota del número de versión actual – 478 – para la próxima operación. Ahora eliminaré una fila de datos:
Habiendo eliminado una fila, volveré a ejecutar CHANGETABLE() para ver qué informes de seguimiento de cambios para esta operación.
Encuentro la fila que eliminé en la última operación, con el SYS_CHANGE_OPERATION establecido en D (eliminar):
Ahora, recuerde que el número de versión hace una diferencia aquí! El número de versión pasado a CHANGETABLE() es el punto de partida para cualquier cambio devuelto por esa función. A través de este ejercicio, he estado revisando los resultados del seguimiento de cambios después de cada operación de DML. Sin embargo, puedo establecer el número de versión inicial en cualquier número de versión válido, o simplemente usar NULL para obtener todos los resultados de seguimiento de cambios disponibles para esa tabla. Para demostrarlo, estableceré el valor de nuevo en la versión 470, el punto de partida antes de cualquier actualización, para mostrar cómo se vería el historial completo. Cuando vuelvo a ejecutar CHANGETABLE () usando nuestra versión original de seguimiento de cambios, obtengo lo siguiente:
Hay un par de matices predecibles aquí. En primer lugar, la fila que muestra el ID de registro de 1 (que era el registro de Phoebe Buffay que eliminé) se muestra simplemente como una operación de eliminación, a pesar de que esta fila se insertó y posteriormente se eliminó desde el número de versión inicial. Recuerde, es el delta el que se mostrará: cada operación en esa fila no se conserva en el seguimiento de cambios. Para los IDS numerados 2 y 4, que eran las dos filas que inserté y actualizé posteriormente, la OPERACIÓN SYS_CHANGE_ muestra un inserto, aunque actualizamos ambos registros después de insertar. La señal es que SYS_CHANGE_VERSION y SYS_CHANGE_CREATION_VERSION en estas filas no coinciden, lo que indica que el cambio más reciente no fue el insert.
Conclusión
El seguimiento de cambios es un medio sencillo y ligero de detección de cambios en SQL Server. El uso del seguimiento de cambios permite identificar fácilmente los datos nuevos, modificados y eliminados, eliminando la necesidad de comparaciones de fuerza bruta. En mi próximo post, miraré esto desde una perspectiva ETL, integrando el seguimiento de cambios en un proceso de carga de extremo a extremo.