sys.sp_cdc_enable_table (Transact-SQL)

  • Articolo

Si applica a:SQL Server

Abilita l'acquisizione dei dati delle modifiche per la tabella di origine specificata nel database corrente. Quando una tabella viene abilitata per Change Data Capture, un record di ogni operazione DML (Data Manipulation Language) applicata alla tabella viene scritto nel log delle transazioni. Il processo Change Data Capture recupera queste informazioni dal log e le scrive nelle tabelle delle modifiche accessibili mediante un set di funzioni.

Change Data Capture non è disponibile in ogni edizione di SQL Server. Per un elenco delle funzionalità supportate dalle edizioni di SQL Server, vedere Edizioni e funzionalità supportate di SQL Server 2022.

Convenzioni di sintassi Transact-SQL

Sintassi

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' ]
[ ; ]

Argomenti

[ @source_schema = ] 'source_schema'

Nome dello schema in cui appartiene la tabella di origine. @source_schema è sysname, senza impostazione predefinita e non può essere NULL.

[ @source_name = ] 'source_name'

Nome della tabella di origine in cui abilitare Change Data Capture. @source_name è sysname, senza impostazione predefinita e non può essere NULL.

source_name deve esistere nel database corrente. Le tabelle nello cdc schema non possono essere abilitate per Change Data Capture.

[ @role_name = ] 'role_name'

Nome del ruolo del database utilizzato per controllare l'accesso ai dati delle modifiche. @role_name è sysname e deve essere specificato. Se viene impostato in modo esplicito su NULL, non viene utilizzato alcun ruolo di controllo per limitare l'accesso ai dati delle modifiche.

Se il ruolo esiste attualmente, viene usato. Se il ruolo non esiste, viene effettuato un tentativo di creare un ruolo del database con il nome specificato. Nel nome del ruolo vengono eliminati gli spazi vuoti nella parte destra della stringa prima di provare a creare il ruolo. Se il chiamante non è autorizzato a creare un ruolo all'interno del database, l'operazione della stored procedure ha esito negativo.

[ @capture_instance = ] 'capture_instance'

nome dell'istanza di acquisizione usata per denominare gli oggetti Change Data Capture specifici dell'istanza. @capture_instance è sysname e non può essere NULL.

Se non è specificato, il nome deriva dal nome dello schema di origine più il nome della tabella di origine nel formato <schemaname>_<sourcename>. @capture_instance non può superare i 100 caratteri e deve essere univoco all'interno del database. Se specificato o derivato, @capture_instance viene tagliato qualsiasi spazio vuoto a destra della stringa.

Una tabella di origine può avere un massimo di due istanze di acquisizione. Per altre informazioni, vedere sys.sp_cdc_help_change_data_capture (Transact-SQL).

[ @supports_net_changes = ] supports_net_changes

Indica se deve essere abilitato il supporto per l'esecuzione di query per le modifiche delta per questa istanza di acquisizione. @supports_net_changes è bit con un valore predefinito 1 se la tabella ha una chiave primaria o la tabella ha un indice univoco identificato tramite il parametro @index_name. In caso contrario, per impostazione predefinita il parametro è 0.

  • Se 0, vengono generate solo le funzioni di supporto per eseguire query per tutte le modifiche.
  • Se 1, vengono generate anche le funzioni necessarie per eseguire query per le modifiche nette.

Se @supports_net_changes è impostato su 1, è necessario specificare @index_name oppure la tabella di origine deve avere una chiave primaria definita.

Quando @supports_net_changes è impostato su 1, viene creato un indice non cluster aggiuntivo nella tabella delle modifiche e viene creata la funzione di query Net Changes. Poiché questo indice deve essere mantenuto, l'abilitazione delle modifiche nette può avere un effetto negativo sulle prestazioni di CDC.

[ @index_name = ] 'index_name'

Nome di un indice univoco da utilizzare per identificare in modo univoco le righe nella tabella di origine. @index_name è sysname e può essere NULL. Se specificato, @index_name deve essere un indice univoco valido nella tabella di origine. Se si specifica @index_name , le colonne di indice identificate hanno la precedenza su qualsiasi colonna chiave primaria definita come identificatore di riga univoco per la tabella.

[ @captured_column_list = ] N'captured_column_list'

Identifica le colonne della tabella di origine da includere nella tabella delle modifiche. @captured_column_list è nvarchar(max) e può essere NULL. Se il valore è NULL, nella tabella delle modifiche vengono incluse tutte le colonne.

I nomi delle colonne devono essere colonne valide nella tabella di origine. Le colonne definite in un indice di chiave primaria o le colonne definite in un indice a cui fa riferimento @index_name devono essere incluse.

@captured_column_list è un elenco delimitato da virgole di nomi di colonna. I nomi delle singole colonne all'interno dell'elenco possono essere racchiusi facoltativamente tra virgolette doppie ("") o parentesi quadre ([]). Se un nome di una colonna contiene una virgola incorporata, il nome della colonna deve essere racchiuso tra virgolette.

@captured_column_list non può contenere i nomi di colonna riservati seguenti: __$start_lsn, __$end_lsn, __$seqval__$operation, e __$update_mask.

[ @filegroup_name = ] 'filegroup_name'

Filegroup da usare per la tabella delle modifiche creata per l'istanza di acquisizione. @filegroup_name è sysname e può essere NULL. Se specificato, è necessario definire @filegroup_name per il database corrente. Se il valore è NULL; viene utilizzato il filegroup predefinito.

Si consiglia di creare un filegroup separato per modificare le tabelle di acquisizione dei dati delle modifiche.

[ @allow_partition_switch = ] 'allow_partition_switch'

Indica se il comando SWITCH PARTITION di ALTER TABLE può essere eseguito su una tabella abilitata per Change Data Capture. @allow_partition_switch è bit, con un valore predefinito .1

Per le tabelle non partizionate, l'impostazione di cambio è sempre 1 mentre l'impostazione effettiva viene ignorata. Se l'opzione è impostata in modo esplicito su 0 per una tabella non partizionata, viene generato l'avviso 22857 per indicare che l'impostazione dell'opzione è stata ignorata. Se l'opzione è impostata in modo esplicito su 0 per una tabella partizionata, viene generato l'avviso 22356 per indicare che le operazioni di cambio di partizione nella tabella di origine non sono consentite. Infine, se l'impostazione dell'opzione è impostata in modo esplicito 1 su o consentita per impostazione predefinita 1 e la tabella abilitata è partizionata, viene generato l'avviso 22855 per indicare che le opzioni di partizione non verranno bloccate. Se si verificano commutatori di partizione, Change Data Capture non tiene traccia delle modifiche risultanti dall'opzione. Ciò causa incoerenze nei dati quando vengono utilizzati i dati delle modifiche.

SWITCH PARTITION è un'operazione eseguita sui metadati, ma apporta modifiche anche ai dati. Le modifiche ai dati associate a questa operazione non vengono acquisite nelle tabelle delle modifiche di Change Data Capture. Si consideri ad esempio una tabella in cui sono presenti tre partizioni cui vengono apportate modifiche. Il processo di acquisizione tiene traccia delle operazioni di inserimento, aggiornamento ed eliminazione eseguite dall'utente nella tabella. Tuttavia, se una partizione viene spostata in un'altra tabella ,ad esempio per eseguire un'eliminazione bulk, le righe spostate come parte di questa operazione non vengono acquisite come righe eliminate nella tabella delle modifiche. Analogamente, se una nuova partizione con righe prepopolate viene aggiunta alla tabella, queste righe non vengono riflesse nella tabella delle modifiche. Questa situazione può provocare incoerenza tra i dati quando le modifiche vengono utilizzate da un'applicazione e applicate a una destinazione.

Se si abilita il cambio di partizione in SQL Server, potrebbero essere necessarie anche operazioni di suddivisione e unione in futuro. Prima di eseguire un'operazione di suddivisione o unione in una tabella replicata o abilitata per CDC, assicurarsi che la partizione in questione non abbia comandi replicati in sospeso. È anche necessario assicurarsi che nessuna operazione DML venga eseguita sulla partizione durante le operazioni di divisione e unione. Se sono presenti transazioni che il lettore di log o il processo di acquisizione CDC non ha elaborato o se le operazioni DML vengono eseguite su una partizione di una tabella replicata o abilitata per CDC durante l'esecuzione di un'operazione di divisione o unione (che coinvolge la stessa partizione), potrebbe causare un errore di elaborazione (errore 608 - Nessuna voce di catalogo trovata per l'ID partizione) con l'agente di lettura log o il processo di acquisizione CDC. Per correggere l'errore potrebbe essere necessario reinizializzare la sottoscrizione o disabilitare la funzionalità CDC sulla tabella o sul database.

Valori del codice restituito

0 (esito positivo) o 1 (errore).

Set di risultati

Nessuno.

Osservazioni:

Prima di abilitare una tabella per Change Data Capture, è necessario abilitare il database. Per determinare se il database è abilitato per Change Data Capture, eseguire una query sulla is_cdc_enabled colonna nella vista del catalogo sys.databases . Per abilitare il database, utilizzare la stored procedure sys.sp_cdc_enable_db .

Quando Change Data Capture viene abilitato per una tabella, vengono generate una tabella delle modifiche e una o due funzioni di query. La tabella delle modifiche viene utilizzata come repository per le modifiche della tabella di origine estratte dal log delle transazioni dal processo di acquisizione. Le funzioni di query vengono utilizzate per estrarre i dati dalla tabella delle modifiche. I nomi di queste funzioni derivano dal parametro @capture_instance nei modi seguenti:

  • Tutte le modifiche funzione: cdc.fn_cdc_get_all_changes_<capture_instance>
  • Funzione Net changes:Net changes function: cdc.fn_cdc_get_net_changes_<capture_instance>

sys.sp_cdc_enable_table crea inoltre i processi di acquisizione e pulizia per il database se la tabella di origine è la prima tabella nel database da abilitare per Change Data Capture e non esistono pubblicazioni transazionali per il database. Imposta la is_tracked_by_cdc colonna nella vista del catalogo sys.tables su 1.

SQL Server Agent non deve essere in esecuzione quando CDC è abilitato per una tabella. Tuttavia, il processo di acquisizione non elabora il log delle transazioni e scrive le voci nella tabella delle modifiche, a meno che SQL Server Agent non sia in esecuzione.

Autorizzazioni

Richiede l'appartenenza al ruolo predefinito del database db_owner .

Esempi

R. Abilitare Change Data Capture specificando solo i parametri obbligatori

Nell'esempio seguente viene abilitata l'acquisizione dei dati delle modifiche per la tabella HumanResources.Employee. Vengono specificati solo i parametri obbligatori.

USE AdventureWorks2022;
GO

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

B. Abilitare Change Data Capture specificando parametri facoltativi aggiuntivi

Nell'esempio seguente viene abilitata l'acquisizione dei dati delle modifiche per la tabella HumanResources.Department. Vengono specificati tutti i parametri tranne @allow_partition_switch .

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

Contenuto correlato