sys.sp_cdc_enable_table (Transact-SQL)

Aplica-se a:SQL Server

Habilita o Change Data Capture para a tabela de origem especificada no banco de dados atual. Quando uma tabela está habilitada para Change Data Capture, um registro de cada operação DML (Linguagem de Manipulação de Dados) aplicado à tabela é gravado no log de transações. O processo do Change Data Capture recupera essas informações a partir do log e grava-as nas tabelas de alteração que são acessadas usando um conjunto de funções.

A captura de dados de alteração não está disponível em todas as edições do SQL Server. Para obter uma lista de recursos com suporte nas edições do SQL Server, confira Edições e recursos com suporte no SQL Server 2022.

Convenções de sintaxe de Transact-SQL

Sintaxe

sys.sp_cdc_enable_table
    [ @source_schema = ] 'source_schema'
      , [ @source_name = ] 'source_name'
    [ , [ @capture_instance = ] 'capture_instance' ]
    [ , [ @supports_net_changes = ] supports_net_changes ]
      , [ @role_name = ] 'role_name'
    [ , [ @index_name = ] 'index_name' ]
    [ , [ @captured_column_list = ] N'captured_column_list' ]
    [ , [ @filegroup_name = ] 'filegroup_name' ]
    [ , [ @allow_partition_switch = ] 'allow_partition_switch' ]
[ ; ]

Argumentos

@source_schema [ = ] 'source_schema'

O nome do esquema ao qual a tabela de origem pertence. @source_schema é sysname, sem padrão, e não pode ser NULL.

@source_name [ = ] 'source_name'

O nome da tabela de origem na qual habilitar a captura de dados de alteração. @source_name é sysname, sem padrão, e não pode ser NULL.

source_name deve existir no banco de dados atual. As tabelas no cdc esquema não podem ser habilitadas para captura de dados de alteração.

@role_name [ = ] 'role_name'

O nome da função de banco de dados usada para bloquear o acesso para alterar dados. @role_name é sysname e deve ser especificado. Se explicitamente definido como NULL, nenhuma função associada será usada para limitar o acesso aos dados de alteração.

Se a função existir atualmente, ela será usada. Se a função não existir, será feita uma tentativa de criar uma função de banco de dados com o nome especificado. Os espaços em branco do nome da função à direita da cadeia de caracteres são eliminados antes de tentar criar a função. Se o chamador não estiver autorizado a criar uma função no banco de dados, a operação de procedimento armazenado falhará.

@capture_instance [ = ] 'capture_instance'

o nome da instância de captura usada para nomear objetos do Change Data Capture específicos. @capture_instance é sysname e não pode ser NULL.

Se não for especificado, o nome será derivado do nome do esquema de origem mais o nome da tabela de origem, no formato <schemaname>_<sourcename>. @capture_instance não pode exceder 100 caracteres e deve ser exclusivo dentro do banco de dados. Seja especificado ou derivado, @capture_instance é cortado de qualquer espaço em branco à direita da cadeia de caracteres.

Uma tabela de origem pode ter um máximo de duas instâncias de captura. Para obter mais informações, consulte sys.sp_cdc_help_change_data_capture (Transact-SQL).

@supports_net_changes [ = ] supports_net_changes

Indica se o suporte à consulta de alterações líquidas será habilitado para esta instância de captura. @supports_net_changes é bit com um padrão de 1 se a tabela tiver uma chave primária ou se a tabela tiver um índice exclusivo que tenha sido identificado usando o parâmetro @index_name. Caso contrário, o parâmetro assumirá como 0padrão .

  • Se 0, somente as funções de suporte para consultar todas as alterações são geradas.
  • Se 1, as funções necessárias para consultar alterações líquidas também serão geradas.

Se @supports_net_changes estiver definido como 1, @index_name deverá ser especificado ou a tabela de origem deverá ter uma chave primária definida.

Quando @supports_net_changes é definido como 1, um índice adicional não clusterizado é criado na tabela de alterações e a função de consulta net changes é criada. Como esse índice precisa ser mantido, habilitar alterações líquidas pode ter um efeito negativo no desempenho do CDC.

@index_name [ = ] 'index_name'

O nome de um índice exclusivo a ser usado para identificar exclusivamente as linhas na tabela de origem. @index_name é sysname e pode ser NULL. Se especificado, @index_name deve ser um índice exclusivo válido na tabela de origem. Se @index_name for especificado, as colunas de índice identificadas terão precedência sobre quaisquer colunas de chave primária definidas como o identificador de linha exclusivo da tabela.

@captured_column_list [ = ] N'captured_column_list'

Identifica as colunas da tabela de origem a serem incluídas na tabela de alteração. @captured_column_list é nvarchar(max) e pode ser NULL. Se for NULL, todas as colunas serão incluídas na tabela de alteração.

Nomes de Coluna devem ser colunas válidas na tabela de origem. Colunas definidas em um índice de chave primária ou colunas definidas em um índice referenciado por @index_name devem ser incluídas.

@captured_column_list é uma lista separada por vírgulas de nomes de colunas. Nomes de colunas individuais dentro da lista podem ser opcionalmente citados usando aspas duplas () ou colchetes (""[]). Se um nome de coluna contiver uma vírgula inserida, o nome de coluna deve ser citado.

@captured_column_list não pode conter os seguintes nomes de coluna reservados: __$start_lsn, , , __$operation__$end_lsn__$seqvale __$update_mask.

@filegroup_name [ = ] 'filegroup_name'

O grupo de arquivos a ser usado para a tabela de alterações criada para a instância de captura. @filegroup_name é sysname e pode ser NULL. Se especificado, @filegroup_name deve ser definido para o banco de dados atual. Se for NULL, o grupo de arquivos padrão será usado.

Recomendamos a criação de um grupo de arquivos separado para tabelas de alteração do Change Data Capture.

@allow_partition_switch [ = ] 'allow_partition_switch'

Indica se o comando SWITCH PARTITION de ALTER TABLE pode ser executado em uma tabela que está habilitada para o Change Data Capture. @allow_partition_switch é bit, com um padrão de 1.

Para tabelas não particionadas, a configuração de alternância é sempre 1 e a configuração real é ignorada. Se a opção estiver explicitamente definida como 0 para uma tabela não particionada, o aviso 22857 será emitido para indicar que a configuração da opção foi ignorada. Se a opção estiver explicitamente definida como 0 para uma tabela particionada, o aviso 22356 será emitido para indicar que as operações de opção de partição na tabela de origem não são permitidas. Finalmente, se a configuração do switch for definida explicitamente 1 ou permitida como padrão 1 e a tabela habilitada estiver particionada, o aviso 22855 será emitido para indicar que os switches de partição não serão bloqueados. Se ocorrerem opções de partição, a captura de dados de alteração não rastreará as alterações resultantes do switch. Isso causa inconsistências de dados quando os dados de alteração são consumidos.

SWITCH PARTITION é uma operação de metadados, mas provoca alterações nos dados. As alterações de dados associadas a essa operação não são capturadas nas tabelas de alteração de captura de dados. Considere uma tabela com três partições em que são feitas alterações. O processo de captura controla as operações de inserção, atualização e exclusão do usuário executadas na tabela. No entanto, se uma partição for trocada para outra tabela (por exemplo, para executar uma exclusão em massa), as linhas que foram movidas como parte dessa operação não serão capturadas como linhas excluídas na tabela de alterações. Da mesma forma, se uma nova partição com linhas pré-preenchidas for adicionada à tabela, essas linhas não serão refletidas na tabela de alterações. Isso pode causar inconsistência de dados quando as alterações forem consumidas por um aplicativo e aplicadas a um destino.

Se você habilitar a alternância de partição no SQL Server, talvez também precise de operações de divisão e mesclagem em um futuro próximo. Antes de executar uma operação de divisão ou mesclagem em uma tabela replicada ou habilitada para CDC, verifique se a partição em questão não tem nenhum comando replicado pendente. Você também deve garantir que nenhuma operação DML seja executada na partição durante as operações de divisão e mesclagem. Se houver transações que o leitor de log ou o trabalho de captura CDC não processou, ou se as operações DML forem executadas em uma partição de uma tabela replicada ou habilitada para CDC enquanto uma operação de divisão ou mesclagem for executada (envolvendo a mesma partição), isso poderá levar a um erro de processamento (erro 608 - Nenhuma entrada de catálogo encontrada para ID de partição) com o agente leitor de log ou o trabalho de captura CDC. Para corrigir o erro, talvez seja preciso reinicializar a assinatura ou desabilitar a CDA nessa tabela ou banco de dados.

Valores do código de retorno

0 (sucesso) ou 1 (falha).

Conjunto de resultados

Nenhum.

Comentários

Antes de habilitar uma tabela para Change Data Capture, o banco de dados deve estar habilitado. Para determinar se o banco de dados está habilitado para captura de dados de alteração, consulte a is_cdc_enabled coluna na exibição de catálogo sys.databases . Para habilitar o banco de dados, use o procedimento armazenado sys.sp_cdc_enable_db.

Quando o Change Data Capture está habilitado para uma tabela, uma tabela de alteração e uma ou duas funções de consulta são geradas. A tabela de alteração serve como um repositório para as alterações da tabela de origem extraídas do log de transações pelo processo de captura. As funções de consulta são usadas para extrair dados da tabela de alteração. Os nomes dessas funções são derivados do parâmetro @capture_instance das seguintes maneiras:

  • Todas as alterações de função: cdc.fn_cdc_get_all_changes_<capture_instance>
  • Função de mudanças líquidas: cdc.fn_cdc_get_net_changes_<capture_instance>

sys.sp_cdc_enable_table Também cria os trabalhos de captura e limpeza para o banco de dados se a tabela de origem for a primeira tabela do banco de dados a ser habilitada para captura de dados de alteração e não existirem publicações transacionais para o banco de dados. Ele define a is_tracked_by_cdc coluna na exibição de catálogo sys.tables como 1.

O SQL Server Agent não precisa estar em execução quando o CDC está habilitado para uma tabela. No entanto, o processo de captura não processa o log de transações e grava entradas na tabela de alterações, a menos que o SQL Server Agent esteja em execução.

Permissões

Requer associação na função de banco de dados fixa db_owner.

Exemplos

R. Habilitar a captura de dados de alteração especificando apenas os parâmetros necessários

O exemplo a seguir habilita o Change Data Capture na tabela HumanResources.Employee. Somente os parâmetros necessários são especificados.

USE AdventureWorks2022;
GO

EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'HumanResources',
    @source_name = N'Employee',
    @role_name = N'cdc_Admin';
GO

B. Habilitar a captura de dados de alteração especificando parâmetros opcionais adicionais

O exemplo a seguir habilita o Change Data Capture na tabela HumanResources.Department. Todos os parâmetros, exceto @allow_partition_switch são especificados.

USE AdventureWorks2022;
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'HumanResources',
    @source_name = N'Department',
    @role_name = N'cdc_admin',
    @capture_instance = N'HR_Department',
    @supports_net_changes = 1,
    @index_name = N'AK_Department_Name',
    @captured_column_list = N'DepartmentID, Name, GroupName',
    @filegroup_name = N'PRIMARY';
GO