Change Data Capture e o binário __$update_mask

E a moral da história é... teste, tente outras coisas, pense grande, depois pequeno, sempre assuma que existe uma maneira melhor.

Tão cientificamente interessante quanto foi minha última resposta. Eu decidi tentar uma outra abordagem. Lembrei que poderia concatenar com o truque XML PATH(''). Como eu sabia como obter o ordinal de cada coluna alterada da lista capture_column da resposta anterior, pensei que valeria a pena testar se a função de bit MS funcionaria melhor dessa maneira para o que precisávamos.

SELECT __$update_mask ,
        ( SELECT    CC.column_name + ','
          FROM      cdc.captured_columns CC
                    INNER JOIN cdc.change_tables CT ON CC.[object_id] = CT.[object_id]
          WHERE     capture_instance = 'dbo_OurTableName'
                    AND sys.fn_cdc_is_bit_set(CC.column_ordinal,
                                              PD.__$update_mask) = 1
        FOR
          XML PATH('')
        ) AS changedcolumns
FROM    cdc.dbo_MyTableName PD

É muito mais limpo do que (embora não tão divertido quanto) todo aquele CLR, retorna a abordagem de volta apenas ao código SQL nativo. E rufar de tambores... retorna os mesmos resultados em menos de um segundo . Como os dados de produção são 100 vezes maiores, cada segundo conta.

Estou deixando a outra resposta para fins científicos - mas, por enquanto, esta é a nossa resposta correta.

Então, depois de alguma pesquisa, decidimos ainda fazer isso no lado do SQL antes de passar para o data warehouse. Mas estamos adotando essa abordagem muito aprimorada (com base em nossas necessidades e em uma nova compreensão de como a máscara funciona).

Obtemos uma lista dos nomes das colunas e suas posições ordinais com esta consulta. O retorno volta em um formato XML para que possamos passar para o SQL CLR.

DECLARE @colListXML varchar(max);

SET @colListXML = (SELECT column_name, column_ordinal
    FROM  cdc.captured_columns 
    INNER JOIN cdc.change_tables 
    ON captured_columns.[object_id] = change_tables.[object_id]
    WHERE capture_instance = 'dbo_OurTableName'
    FOR XML Auto);

Em seguida, passamos esse bloco XML como uma variável e o campo de máscara para uma função CLR que retorna uma string delimitada por vírgula das colunas que foram alteradas pelo campo binário _$update_mask. Essa função clr interroga o campo de máscara para alterar o bit de cada coluna na lista xml e, em seguida, retorna seu nome do ordinal relacionado.

SELECT  cdc.udf_clr_ChangedColumns(@colListXML,
        CAST(__$update_mask AS VARCHAR(MAX))) AS changed
    FROM cdc.dbo_OurCaptureTableName
    WHERE NOT __$update_mask IS NULL;

O código c# clr se parece com isso: (compilado em um assembly chamado CDCUtilities)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString udf_clr_cdcChangedColumns(string columnListXML, string updateMaskString)
    {
        /*  xml of column ordinals shall be formatted as follows:

            <cdc.captured_columns column_name="Column1" column_ordinal="1" />                
            <cdc.captured_columns column_name="Column2" column_ordinal="2" />                

        */

        System.Text.ASCIIEncoding encoding=new System.Text.ASCIIEncoding();
        byte[] updateMask = encoding.GetBytes(updateMaskString);

        string columnList = "";
        System.Xml.XmlDocument colList = new System.Xml.XmlDocument();
        colList.LoadXml("<columns>" + columnListXML + "</columns>"); /* generate xml with root node */

        for (int i = 0; i < colList["columns"].ChildNodes.Count; i++)
        {
            if (columnChanged(updateMask, int.Parse(colList["columns"].ChildNodes[i].Attributes["column_ordinal"].Value)))
            {
                columnList += colList["columns"].ChildNodes[i].Attributes["column_name"].Value + ",";
            }
        }

        if (columnList.LastIndexOf(',') > 0)
        {
            columnList = columnList.Remove(columnList.LastIndexOf(','));   /* get rid of trailing comma */
        }

        return columnList;  /* return the comma seperated list of columns that changed */
    }

    private static bool columnChanged(byte[] updateMask, int colOrdinal)
    {
        unchecked  
        {
            byte relevantByte = updateMask[(updateMask.Length - 1) - ((colOrdinal - 1) / 8)];
            int bitMask = 1 << ((colOrdinal - 1) % 8);  
            var hasChanged = (relevantByte & bitMask) != 0;
            return hasChanged;
        }
    }
}

E a função para o CLR assim:

CREATE FUNCTION [cdc].[udf_clr_ChangedColumns]
       (@columnListXML [nvarchar](max), @updateMask [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [CDCUtilities].[UserDefinedFunctions].[udf_clr_cdcChangedColumns]

Em seguida, anexamos essa lista de colunas ao conjunto de linhas e passamos para o data warehouse para análise. Ao usar a consulta e o clr, evitamos ter que usar duas chamadas de função por linha por alteração. Podemos ir direto ao ponto com resultados personalizados para nossa instância de captura de alterações.

Obrigado a esta postagem stackoverflow sugerida por Jon Seigel pela maneira de interpretar mask.

Em nossa experiência com essa abordagem, conseguimos obter uma lista de todas as colunas alteradas de 10 mil linhas cdc em menos de 3 segundos.