CHANGETABLE (Transact-SQL)

Artigo
05/23/2023

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Retorna informações de controle de alterações para uma tabela. Você pode usar essa instrução para retornar todas as alterações de uma tabela ou informações de controle de alterações para uma linha específica.

Convenções de sintaxe de Transact-SQL

Sintaxe

CHANGETABLE (  
    { CHANGES <table_name> , <last_sync_version> 
    | VERSION <table_name> , <primary_key_values> } 
    , [ FORCESEEK ] 
    )  
[AS] <table_alias> [ ( <column_alias> [ ,...n ] )  
  
<primary_key_values> ::=  
( <column_name> [ , ...n ] ) , ( <value> [ , ...n ] )

Argumentos

MUDANÇAS table_name , last_sync_version
Retorna informações de controle para todas as alterações em uma tabela que ocorreram desde a versão especificada por last_sync_version.

table_name
É a tabela definida pelo usuário na qual as alterações controladas são obtidas. O controle de alterações deve estar habilitado na tabela. Um nome de tabela de uma, duas, três ou quatro partes pode ser usado. O nome da tabela pode ser um sinônimo para a tabela.

last_sync_version
Um valor escalar bigint anulável. Uma expressão causará um erro de sintaxe. Se o valor for NULL, todas as alterações efetuadas serão retornadas. Quando obtém alterações, o aplicativo chamador deve especificar o ponto a partir do qual são necessárias alterações. O last_sync_version especifica esse ponto. A função retorna informações de todas as linhas que foram alteradas desde essa versão. O aplicativo está consultando para receber alterações com uma versão maior que last_sync_version. Normalmente, antes de obter alterações, o aplicativo chamará CHANGE_TRACKING_CURRENT_VERSION() para obter a versão que será usada na próxima vez que forem necessárias alterações. Por isso, o aplicativo não precisa interpretar ou entender o valor real. Como last_sync_version é obtido pelo aplicativo de chamada, o aplicativo precisa persistir o valor. Se o aplicativo perder este valor, ele deverá reinicializar os dados. last_sync_version deve ser validado para garantir que não seja muito antigo, pois algumas ou todas as informações de alteração podem ter sido limpas de acordo com o período de retenção configurado para o banco de dados. Para obter mais informações, consulte Opções CHANGE_TRACKING_MIN_VALID_VERSION (Transact-SQL) e ALTER DATABASE SET (Transact-SQL).

VERSÃO table_name, { primary_key_values }
Retorna as informações de controle de alterações mais recentes de uma linha especificada. Os valores de chave primária devem identificar a linha. primary_key_values identifica as colunas de chave primária e especifica os valores. Os nomes de coluna de chave primária podem ser especificados em qualquer ordem.

table_name
É a tabela definida pelo usuário na qual devem ser obtidas as informações de controle de alterações. O controle de alterações deve estar habilitado na tabela. Um nome de tabela de uma, duas, três ou quatro partes pode ser usado. O nome da tabela pode ser um sinônimo para a tabela.

column_name
Especifica o nome de coluna/colunas de chave primária. Podem ser especificados vários nomes de coluna em qualquer ordem.

value
É o valor da chave primária. Se houver várias colunas de chave primária, os valores deverão ser especificados na mesma ordem em que as colunas aparecem na lista column_name .

[ BUSCA FORÇADA ]
Aplica-se a: SQL Server (começando com SQL Server 2016 (13.x) SP2 CU16, SQL Server 2017 (14.x) CU24 e SQL Server 2019 (15.x) CU11), Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Parâmetro opcional que força uma operação de busca a ser usada para acessar o table_name. Em alguns casos em que poucas linhas foram alteradas, uma operação de verificação ainda pode ser usada para acessar o table_name. Se uma operação de verificação introduzir um problema de desempenho, use o FORCESEEK parâmetro.

[COMO] table_alias [ (column_alias [ ,...n ] ) ]
Fornece nomes para os resultados que são retornados por CHANGETABLE.

table_alias
É o alias da tabela de que é retornada por CHANGETABLE. table_alias é obrigatório e deve ser um identificador válido.

column_alias
É um alias de coluna opcional ou lista de aliases de coluna opcional para as colunas que são retornadas por CHANGETABLE. Isso permite que os nomes de colunas sejam personalizados caso haja nomes duplicados nos resultados.

Tipos de retorno

table

Valores retornados

CHANGETABLE CHANGES

Quando CHANGES é especificado, são retornadas zero ou mais linhas que têm as colunas a seguir.

Nome da coluna	Tipo de dados	Descrição
SYS_CHANGE_VERSION	bigint	Valor de versão associado à última alteração efetuada na linha
SYS_CHANGE_CREATION_VERSION	bigint	Valores de versão associados à última operação de inserção.
SYS_CHANGE_OPERATION	nchar(1)	Especifica o tipo de alteração: U = Atualização I = Inserir D = Excluir
SYS_CHANGE_COLUMNS	varbinary(4100)	Lista as colunas alteradas desde a last_sync_version (a linha de base). Observe que as colunas computadas nunca são listadas como alteradas. O valor será NULL quando qualquer uma das condições a seguir for verdadeira: O controle de alterações da coluna não está habilitado. A operação é de inserção ou exclusão. Todas as colunas de chave não primária foram atualizadas em uma operação. Este valor binário não deve ser interpretado diretamente. Em vez disso, para interpretá-lo, use CHANGE_TRACKING_IS_COLUMN_IN_MASK().
SYS_CHANGE_CONTEXT	varbinary(128)	Altere as informações de contexto que você pode especificar opcionalmente usando a cláusula WITH como parte de uma instrução INSERT, UPDATE ou DELETE.
<Valor da coluna de chave primária>	Igual às colunas de tabela de usuário	Os valores de chave primária da tabela controlada. Esses valores identificam exclusivamente cada linha da tabela do usuário.

CHANGETABLE VERSION

Quando VERSION é especificado, uma linha que tem as seguintes colunas é retornada.

Nome da coluna	Tipo de dados	Descrição
SYS_CHANGE_VERSION	bigint	Valor de versão de alteração atual associado à linha. O valor será NULL se uma alteração não tiver sido efetuada por um período maior que o de retenção do controle de alterações ou se a linha não tiver sido alterada desde que o controle de alterações foi habilitado.
SYS_CHANGE_CONTEXT	varbinary(128)	Altere as informações de contexto que podem opcionalmente ser especificadas usando a cláusula WITH como parte de uma instrução INSERT, UPDATE ou DELETE.
<Valor da coluna de chave primária>	Igual às colunas de tabela de usuário	Os valores de chave primária da tabela controlada. Esses valores identificam exclusivamente cada linha da tabela do usuário.

Comentários

Em geral, a função CHANGETABLE é usada na cláusula FROM de uma consulta como se fosse uma tabela.

CHANGETABLE(CHANGES...)

Para obter dados de linha para linhas novas ou modificadas, una o conjunto de resultados à tabela do usuário utilizando as colunas de chave primária. Apenas uma linha é retornada para cada linha na tabela de usuário que foi alterada, mesmo que tenha havido várias alterações na mesma linha desde o valor last_sync_version .

Alterações de coluna de chave primária nunca são marcadas como atualizações. Se um valor de chave primária for alterado, será considerado como excluído do valor antigo e inserido no valor novo.

Se você excluir uma linha e depois inserir uma linha que tenha a chave primária antiga, a alteração será vista como uma atualização em todas as colunas da linha.

Os valores retornados para as SYS_CHANGE_OPERATION colunas e SYS_CHANGE_COLUMNS são relativos à linha de base (last_sync_version) especificada. Por exemplo, se uma operação de inserção foi feita na versão 10 e uma operação de atualização na versão 15, e se a last_sync_version de linha de base for 12, uma atualização será relatada. Se o valor last_sync_version for 8, uma inserção será relatada. SYS_CHANGE_COLUMNS nunca relatará colunas computadas como tendo sido atualizadas.

Geralmente, todas as operações que inserem, atualizam ou excluem dados em tabelas de usuário são controladas, inclusive a instrução MERGE.

As seguintes operações que afetam dados de tabela de usuário não são controladas:

Executando a UPDATETEXT instrução. Essa instrução foi preterida e será removida em uma versão futura do SQL Server. No entanto, as alterações feitas usando a .WRITE cláusula da instrução UPDATE são controladas.
Excluindo linhas usando TRUNCATE TABLE. Quando uma tabela está truncada, as informações do controle de alterações, associadas à tabela, são redefinidas como se o controle de alterações tivesse acabado de ser habilitado na tabela. Um aplicativo cliente deverá sempre validar sua última versão sincronizada. A validação falhará se a tabela foi truncada.

CHANGETABLE(VERSION...)

Um conjunto de resultados vazio será retornado se uma chave primária inexistente for especificada.

O valor de SYS_CHANGE_VERSION pode ser NULL se uma alteração não tiver sido feita por mais tempo do que o período de retenção (por exemplo, a limpeza removeu as informações de alteração) ou se a linha nunca tiver sido alterada desde que o controle de alterações foi habilitado para a tabela.

Permissões

Requer a SELECT permissão nas colunas de chave primária e VIEW CHANGE TRACKING a permissão na tabela especificada pelo valor table_name>< para obter informações de controle de alterações.

Exemplos

R. Retornando linhas para uma sincronização inicial de dados

O exemplo a seguir mostra como obter dados para uma sincronização inicial dos dados da tabela. A consulta retorna todos os dados de linha e suas versões associadas. Você pode inserir ou adicionar esses dados ao sistema que conterá os dados sincronizados.

-- Get all current rows with associated version  
SELECT e.[Emp ID], e.SSN, e.FirstName, e.LastName,  
    c.SYS_CHANGE_VERSION, c.SYS_CHANGE_CONTEXT  
FROM Employees AS e  
CROSS APPLY CHANGETABLE   
    (VERSION Employees, ([Emp ID], SSN), (e.[Emp ID], e.SSN)) AS c;

B. Listando todas as alterações efetuadas desde uma versão específica

O exemplo a seguir lista todas as alterações feitas em uma tabela desde a versão especificada (@last_sync_version). [Emp ID] e SSN são colunas em uma chave primária composta.

DECLARE @last_sync_version bigint;  
SET @last_sync_version = <value obtained from query>;  
SELECT [Emp ID], SSN,  
    SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION,  
    SYS_CHANGE_COLUMNS, SYS_CHANGE_CONTEXT   
FROM CHANGETABLE (CHANGES Employees, @last_sync_version) AS C;

C. Obtendo todos os dados alterados para uma sincronização

O exemplo a seguir mostra como você pode obter todos os dados alterados. Essa consulta une as informações de controle de alterações à tabela de usuário de forma que as informações da tabela de usuário sejam retornadas. Um LEFT OUTER JOIN é usado de forma que uma linha seja retornada para as linhas excluídas.

-- Get all changes (inserts, updates, deletes)  
DECLARE @last_sync_version bigint;  
SET @last_sync_version = <value obtained from query>;  
SELECT e.FirstName, e.LastName, c.[Emp ID], c.SSN,  
    c.SYS_CHANGE_VERSION, c.SYS_CHANGE_OPERATION,  
    c.SYS_CHANGE_COLUMNS, c.SYS_CHANGE_CONTEXT   
FROM CHANGETABLE (CHANGES Employees, @last_sync_version) AS c  
    LEFT OUTER JOIN Employees AS e  
        ON e.[Emp ID] = c.[Emp ID] AND e.SSN = c.SSN;

D. Detectando conflitos usando CHANGETABLE (VERSION...)

O exemplo a seguir mostra como atualizar uma linha apenas se ela não tiver sido alterada desde a última sincronização. O número de versão da linha específica é obtido usando CHANGETABLE. Se a linha tiver sido atualizada, as alterações não serão efetuadas e a consulta retornará informações sobre a alteração mais recente efetuada na linha.

-- @last_sync_version must be set to a valid value  
UPDATE  
    SalesLT.Product  
SET  
    ListPrice = @new_listprice  
FROM  
    SalesLT.Product AS P  
WHERE  
    ProductID = @product_id AND  
    @last_sync_version >= ISNULL (  
        (SELECT CT.SYS_CHANGE_VERSION FROM   
            CHANGETABLE(VERSION SalesLT.Product,  
            (ProductID), (P.ProductID)) AS CT),  
        0);

Confira também

Funções de controle de alterações (Transact-SQL)
Controle de alterações de dados (SQL Server)
CHANGE_TRACKING_IS_COLUMN_IN_MASK (Transact-SQL)
CHANGE_TRACKING_CURRENT_VERSION (Transact-SQL)
CHANGE_TRACKING_MIN_VALID_VERSION (Transact-SQL)

Partilhar via

CHANGETABLE (Transact-SQL)

Sintaxe

Argumentos

Tipos de retorno

Valores retornados

CHANGETABLE CHANGES

CHANGETABLE VERSION

Comentários

CHANGETABLE(CHANGES...)

CHANGETABLE(VERSION...)

Permissões

Exemplos

R. Retornando linhas para uma sincronização inicial de dados

B. Listando todas as alterações efetuadas desde uma versão específica

C. Obtendo todos os dados alterados para uma sincronização

D. Detectando conflitos usando CHANGETABLE (VERSION...)

Confira também

Comentários

Recursos adicionais