Trabajar con datos modificados (SQL Server)

Los datos modificados están disponibles para los consumidores de captura de datos modificados a través de funciones con valores de tabla (TVF). Todas las consultas de estas funciones requieren dos parámetros para definir el intervalo de números de secuencia de registro (LSN) que son aptos para tener en cuenta al desarrollar el conjunto de resultados devuelto. Tanto los valores LSN superior como inferior que enlazan el intervalo se consideran incluidos dentro del intervalo.

Se proporcionan varias funciones para ayudar a determinar los valores LSN adecuados para su uso en la consulta de un TVF. La función sys.fn_cdc_get_min_lsn devuelve el LSN más pequeño asociado a un intervalo de validez de instancia de captura. El intervalo de validez es el intervalo de tiempo para el que los datos modificados están disponibles actualmente para sus instancias de captura. La función sys.fn_cdc_get_max_lsn devuelve el LSN más grande en el intervalo de validez. Las funciones sys.fn_cdc_map_time_to_lsn y sys.fn_cdc_map_lsn_to_time están disponibles para ayudar a colocar valores LSN en una escala de tiempo convencional. Dado que la captura de datos modificados usa intervalos de consulta cerrados, a veces es necesario generar el siguiente valor LSN en una secuencia para asegurarse de que los cambios no están duplicados en ventanas de consulta consecutivas. Las funciones sys.fn_cdc_increment_lsn y sys.fn_cdc_decrement_lsn son útiles cuando se requiere un ajuste incremental en un valor LSN.

Validación de límites de LSN

Se recomienda validar los límites de LSN que se van a usar en una consulta de TVF antes de utilizarlos. Los puntos de conexión nulos o los puntos de conexión que se encuentren fuera del intervalo de validez de una instancia de captura provocarán que un TVF de captura de datos cambie devuelva un error.

Por ejemplo, se devuelve el siguiente error para una consulta para todos los cambios cuando un parámetro que se usa para definir el intervalo de consulta no es válido o está fuera del intervalo o la opción de filtro de fila no es válida.

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ...

El error correspondiente devuelto para una net changes consulta es el siguiente:

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ...

Los errores de autorización devolverán errores al consultar todos los cambios, como se muestra:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'fn_cdc_get_all_changes_...', database 'MyDB', schema 'cdc'.

Lo mismo sucede cuando se consultan los cambios netos:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object fn_cdc_get_net_changes_...', database 'MyDB', schema 'cdc'.

Consulte la plantilla Enumerar cambios netos mediante TRY CATCH para obtener una demostración de cómo interceptar estos errores conocidos de TVF y devolver información más significativa sobre el error.

Funciones de consulta

En función de las características de la tabla de origen a las que se realiza el seguimiento y de la forma en que se configura su instancia de captura, se generan uno o dos TVF para consultar los datos modificados.

• La función cdc.fn_cdc_get_all_changes_<capture_instance> devuelve todos los cambios que se produjeron para el intervalo especificado. Esta función siempre se genera. Las entradas siempre se devuelven ordenadas, primero por el LSN de confirmación de transacción del cambio y, a continuación, por un valor que secuencia el cambio dentro de su transacción. Dependiendo de la opción de filtro de fila elegida, se devuelve la fila final en la actualización (opción de filtro de fila "all") o se devuelven los valores nuevos y antiguos en la actualización (opción de filtro de fila "todas las actualizaciones antiguas").

• La función cdc.fn_cdc_get_net_changes_<capture_instance> se genera cuando el parámetro @supports_net_changes se establece en 1 cuando la tabla de origen está habilitada.

  Nota:

  Esta opción solo se admite si la tabla de origen tiene una clave principal definida o si el parámetro @index_name se ha usado para identificar un índice único.

  La función netchanges devuelve un cambio por fila de tabla de origen modificada. Si se registra más de un cambio para la fila durante el intervalo especificado, los valores de columna reflejarán el contenido final de la fila. Para identificar correctamente la operación necesaria para actualizar el entorno de destino, el TVF debe tener en cuenta tanto la operación inicial en la fila durante el intervalo como la operación final en la fila. Cuando se especifica la opción de filtro de fila 'all', las operaciones que devuelve una net changes consulta se insertarán, eliminarán o actualizarán (nuevos valores). Esta opción siempre devuelve la máscara de actualización como nulo porque hay un costo asociado al cálculo de una máscara agregada. Si necesita una máscara de agregado que refleje todos los cambios en una fila, use la opción "todo con máscara". Si el procesamiento posterior no requiere que se distingan inserciones y actualizaciones, use la opción "todo con fusión". En este caso, el valor de la operación solo tomará dos valores: 1 para eliminar y 5 para una operación que podría ser una inserción o una actualización. Esta opción elimina el procesamiento adicional necesario para determinar si la operación derivada debe ser una inserción o una actualización, y puede mejorar el rendimiento de la consulta cuando esta diferenciación no es necesaria.

La máscara de actualización que se devuelve de una función de consulta es una representación compacta que identifica todas las columnas que han cambiado en una fila de datos modificados. Normalmente, esta información solo es necesaria para un pequeño subconjunto de las columnas capturadas. Las funciones están disponibles para ayudar a extraer información de la máscara en un formulario que las aplicaciones pueden usar más directamente. La función sys.fn_cdc_get_column_ordinal devuelve la posición ordinal de una columna con nombre para una instancia de captura determinada, mientras que la función sys.fn_cdc_is_bit_set devuelve la paridad del bit en la máscara proporcionada en función del ordinal que se pasó en la llamada de función. Juntas, estas dos funciones permiten extraer y devolver información de la máscara de actualización de forma eficaz con la solicitud de datos modificados. Consulte la plantilla Enumerate Net Changes Using All With Mask para obtener una demostración de cómo se usan estas funciones.

Escenarios de función de consulta

En las secciones siguientes se describen escenarios comunes para consultar datos de captura de cambios mediante las funciones de consulta cdc.fn_cdc_get_all_changes_<capture_instance> y cdc.fn_cdc_get_net_changes_<capture_instance>.

Consulta de todos los cambios dentro del intervalo de validez de la instancia de captura

La solicitud más sencilla de los datos modificados es una que devuelve todos los datos modificados actuales en el intervalo de validez de una instancia de captura. Para realizar esta solicitud, determine primero los límites LSN inferiores y superiores del intervalo de validez. A continuación, use estos valores para identificar los parámetros @from_lsn y @to_lsn pasados a la función de consulta cdc.fn_cdc_get_all_changes_<capture_instance> o cdc.fn_cdc_get_net_changes_<capture_instance>. Use la función sys.fn_cdc_get_min_lsn para obtener el límite inferior y sys.fn_cdc_get_max_lsn para obtener el límite superior. Consulte la plantilla Enumerar todos los cambios del rango válido para obtener código de ejemplo para consultar todos los cambios válidos actuales mediante la función de consulta cdc.fn_cdc_get_all_changes_<capture_instance>. Consulte la plantilla "Enumerar Cambios Netos para el Rango Válido" para obtener un ejemplo similar del uso de la función cdc.fn_cdc_get_net_changes_<capture_instance>.

Consulta de todos los cambios nuevos desde el último conjunto de cambios

En el caso de las aplicaciones típicas, la consulta de datos modificados será un proceso continuo, realizando solicitudes periódicas para todos los cambios que se produjeron desde la última solicitud. Para estas consultas, puede usar la función sys.fn_cdc_increment_lsn para derivar el límite inferior de la consulta actual del límite superior de la consulta anterior. Este método garantiza que no se repita ninguna fila porque el intervalo de consulta siempre se trata como un intervalo cerrado en el que ambos puntos de conexión se incluyen en el intervalo. A continuación, use la función sys.fn_cdc_get_max_lsn para obtener el punto de conexión superior para el nuevo intervalo de solicitud. Consulte la plantilla Enumerar todos los cambios desde la solicitud anterior para el código de ejemplo para mover sistemáticamente la ventana de consulta para obtener todos los cambios desde la última solicitud.

Consulta de todos los cambios nuevos hasta ahora

Una restricción típica que se coloca en los cambios devueltos por una función de consulta es incluir solo los cambios que se produjeron entre la solicitud anterior hasta la fecha y hora actuales. Para esta consulta, aplique la función sys.fn_cdc_increment_lsn al @from_lsn valor que se usó en la solicitud anterior para determinar el límite inferior. Dado que el límite superior del intervalo de tiempo se expresa como un momento específico en el tiempo, debe convertirse en un valor LSN antes de que una función de consulta pueda usarlo. Antes de que el valor de fecha y hora pueda ser convertido a un valor LSN equivalente, debe asegurarse de que el proceso de captura haya procesado todos los cambios que se hayan confirmado hasta el límite superior especificado. Esto es necesario para asegurarse de que todos los cambios pertinentes se han propagado a la tabla de cambios. Una manera de hacerlo es estructurar un bucle de espera que comprueba periódicamente si el lsn de confirmación máximo actual registrado para cualquier tabla de cambios de base de datos supera la hora de finalización deseada del intervalo de solicitud.

Una vez que el bucle delay comprueba que el proceso de captura ya ha procesado todas las entradas de registro pertinentes, use la función sys.fn_cdc_map_time_to_lsn para determinar el nuevo punto de extremo alto expresado como un valor LSN. Para asegurarse de que se recuperen todas las entradas confirmadas durante el tiempo especificado, llame a la función sys.fn_cdc_map_time_to_lsn y use la opción "el mayor menor o igual que".

La plantilla Enumerar todos los cambios hasta ahora muestra cómo usar la estrategia anterior para consultar los datos modificados.

Agregar un tiempo de confirmación a un conjunto de resultados de todos los cambios

La hora de confirmación de cada transacción con una entrada asociada en una tabla de cambios de base de datos está disponible en la tabla cdc.lsn_time_mapping. Al unir el valor __$start_lsn devuelto en una solicitud para todos los cambios con el valor start_lsn de una entrada de tabla de cdc.lsn_time_mapping, puede devolver el tran_end_time junto con los datos modificados para marcar el cambio con la hora de confirmación de la transacción en el origen. La plantilla "Anexar Hora de Confirmación a Todos los Cambios en el Conjunto de Resultados" muestra cómo realizar esta combinación.

Combinar datos modificados con otros datos de la misma transacción

En ocasiones, resulta útil combinar datos modificados con otra información recopilada sobre la transacción cuando se confirma en el origen. La columna tran_begin_lsn de la tabla cdc.lsn_time_mapping proporciona la información necesaria para realizar dicha combinación. Cuando se produce la actualización del origen, el valor de database_transaction_begin_lsn de la vista dinámica del sistema sys.dm_tran_database_transactions debe guardarse junto con cualquier otra información que se va a combinar con los datos modificados. Use la función fn_convertnumericlsntobinary para comparar los valores de database_transaction_begin_lsn y tran_begin_lsn. El código para crear esta función está disponible en la plantilla Crear función fn_convertnumericlsntobinary. La plantilla denominada "Return All Changes with a Given tran_begin_lsn" demuestra cómo realizar la combinación.

Consulta mediante funciones envolventes de fecha y hora

Un escenario de aplicación típico para consultar datos de cambio consiste en solicitar periódicamente estos datos mediante una ventana deslizante delimitada por valores de fecha y hora. Para esta clase de consumidores, la captura de datos de cambios proporciona el procedimiento almacenado sys.sp_cdc_generate_wrapper_function que genera scripts para crear funciones envoltorio personalizadas para las funciones de consulta de captura de datos de cambios. Estos contenedores personalizados permiten expresar el intervalo de consulta como un par datetime.

Las opciones de llamada para el procedimiento almacenado permiten generar contenedores para todas las instancias de captura a las que el autor de la llamada tiene acceso o solo a una instancia de captura especificada. Las opciones admitidas también incluyen la capacidad de especificar si el punto superior del intervalo de captura debe estar abierto o cerrado, cuál de las columnas capturadas disponibles debe incluirse en el conjunto de resultados y cuál de las columnas incluidas debe tener marcas de actualización asociadas. El procedimiento devuelve un conjunto de resultados con dos columnas: el nombre de función generado, que se deriva del nombre de la instancia de captura, y la sentencia CREATE para el procedimiento almacenado envoltorio. La función para envolver la consulta de todos los cambios siempre se genera. Si el @supports_net_changes parámetro se estableció cuando se creó la instancia de captura, también se genera la función para encapsular la función net changes.

Es responsabilidad del diseñador de aplicaciones llamar al procedimiento almacenado para la generación de scripts con el fin de crear las instrucciones de creación para los procedimientos almacenados envolventes, y ejecutar los scripts resultantes para crear las funciones. Esto no se produce automáticamente cuando se crea una instancia de captura.

Los contenedores datetime son propiedad del usuario y no se crean en el esquema predeterminado del autor de la llamada. La función generada es adecuada sin modificaciones para la mayoría de los usuarios. Sin embargo, siempre se puede aplicar más personalización al script generado antes de crear la función.

El nombre de la función para encapsular todas las consultas de cambios se fn_all_changes_ seguido del nombre de la instancia de captura. El prefijo que se usa para el contenedor net changes es fn_net_changes_. Ambas funciones toman tres argumentos, al igual que lo hacen sus TVF de captura de datos modificados asociados. Sin embargo, el intervalo de consulta de los envoltorios está limitado por dos valores datetime en lugar de dos valores LSN. El @row_filter_option parámetro para ambos conjuntos de funciones es el mismo.

Las funciones contenedoras generadas admiten la siguiente convención para recorrer sistemáticamente la escala de tiempo de captura de datos modificados: se espera que el @end_time parámetro del intervalo anterior se use como parámetro @start_time del intervalo posterior. La función envoltorio se encarga de asociar los valores de datetime a los valores de LSN y de garantizar que no se pierda ni se repita ningún dato si se sigue esta convención.

Los contenedores se pueden generar para admitir un límite superior cerrado o un límite superior abierto en la ventana de consulta especificada. Es decir, el autor de la llamada puede especificar si las entradas que tienen un tiempo de confirmación igual al límite superior del intervalo de extracción se incluirán dentro del intervalo. De forma predeterminada, se incluye el límite superior.

Aunque se produce un error en las TVF de consulta generadas si se proporciona un valor NULL para el @from_lsn valor o el @to_lsn valor, las funciones contenedoras datetime usan null para permitir que los contenedores datetime devuelvan todos los cambios actuales. Es decir, si se pasa null como punto de inicio inferior de la ventana de consulta al envoltorio datetime, se utiliza el punto de inicio inferior del intervalo de validez de la instancia de captura en la instrucción SELECT subyacente aplicada a la consulta de la función TVF. Del mismo modo, si null se pasa como punto de conexión superior de la ventana de consulta, se usa el extremo superior del intervalo de validez de la instancia de captura al seleccionar desde la consulta TVF.

El conjunto de resultados devuelto por una función contenedora incluye todas las columnas solicitadas seguidas de una columna de operación, recodadas como uno o dos caracteres para identificar la operación asociada a la fila. Si se han solicitado marcas de actualización, aparecen como columnas de bits después del código de operación, en el orden especificado en el @update_flag_list parámetro . Para obtener información sobre las opciones de llamada para personalizar los contenedores de fecha y hora generados, vea sys.sp_cdc_generate_wrapper_function (Transact-SQL).

La plantilla Crea una instancia de un CONTENEDOR TVF con marca de actualización muestra cómo personalizar una función contenedora generada para anexar una marca de actualización para una columna especificada al conjunto de resultados devuelto por una consulta net changes. La plantilla Instanciar TVF contenedor CDC para un esquema muestra cómo instanciar los contenedores de fecha y hora de las TVF de consulta para todas las instancias de captura creadas para las tablas de origen en un esquema de base de datos determinado.

Para obtener un ejemplo que utiliza un contenedor datetime para consultar los datos de cambios netos, consulte la plantilla Obtener cambios netos utilizando un contenedor con indicadores de actualización. Esta plantilla muestra cómo consultar los cambios netos con una función de envoltura cuando esta está configurada para devolver marcas de actualización. Tenga en cuenta que la opción de filtro de fila "all with mask" es necesaria para que la función de consulta subyacente devuelva una máscara de actualización que no sea NULL al actualizar. Los valores nulos se pasan para los límites inferior y superior del intervalo de fecha y hora para indicar a la función que use el extremo bajo y el extremo alto del intervalo de validez de la instancia de captura al realizar la consulta subyacente basada en LSN. La consulta devuelve una fila para cada modificación en una fila de origen que se produjo dentro del intervalo válido para la instancia de captura.

Uso de las funciones contenedor Datetime Wrapper para realizar la transición entre instancias de captura

La captura de datos modificados admite hasta dos instancias de captura para una sola tabla de origen de seguimiento. El uso principal de esta funcionalidad es facilitar una transición entre varias instancias de captura cuando el lenguaje de definición de datos (DDL) cambia y expande el conjunto de columnas disponibles para el seguimiento en la tabla de origen. Al realizar la transición a una nueva instancia de captura, una manera de proteger los niveles de aplicación más altos frente a los cambios en los nombres de las funciones de consulta subyacentes es usar una función contenedora para encapsular la llamada subyacente. A continuación, asegúrese de que el nombre de la función contenedora sigue siendo el mismo. Cuando se produce el cambio, se puede eliminar la función contenedora antigua y crear una nueva con el mismo nombre que hace referencia a las nuevas funciones de consulta. Al modificar primero el script generado para crear una función contenedora con el mismo nombre, puede realizar el cambio a una nueva instancia de captura sin afectar a las capas de aplicación superiores.

Véase también

Seguimiento de cambios de datos (SQL Server)
Acerca de la captura de datos modificados (SQL Server)
Habilitar y deshabilitar la captura de datos modificados (SQL Server)
Administrar y supervisar la captura de datos modificados (SQL Server)