Detectando alterações em uma tabela do SQL Server

Não, não há nenhum. Qualquer tipo de rastreamento de 'última atualização em' causaria um grave problema de desempenho, pois todas as atualizações, de todas as transações, tentariam atualizar o registro que rastreava a 'última atualização em'. Isso significaria efetivamente que apenas uma transação pode atualizar a tabela a qualquer momento, e todas as outras transações precisam aguardar a confirmação da primeira . Serialização completa. O número de administradores/desenvolvedores dispostos a tolerar essa penalidade de desempenho apenas pelo benefício de saber quando ocorreu a última atualização é provavelmente pequeno.

Então você está preso para lidar com isso por meio de código personalizado. Isso significa gatilhos, pois a alternativa (detecção de registros de log) é uma prerrogativa reservada apenas para replicação transacional (ou é alter-ego do CDC ). Esteja ciente de que, se você tentar rastreá-lo por meio de uma coluna 'última atualização em', estará enfrentando exatamente o problema de serialização mencionado acima. Se a simultaneidade de atualização for importante, você terá que usar um mecanismo de fila (o gatilho usa um INSERT e, em seguida, um processo agrega os valores inseridos para formular a 'última atualização em'). Não tente trapacear com alguma solução 'inteligente' como se infiltrar na identidade atual ou procurar sys.dm_db_index_usage_stats . E também uma coluna 'updated_at' por registro, como os timestamps Rails,

Existe alguma alternativa 'leve'? Na verdade existe um, mas é difícil dizer se funcionará para você e é difícil acertar: Query Notifications . O Query Notification faz exatamente isso, ele configurará uma notificação se algum dado for alterado e você precisar atualizar sua consulta. Embora a maioria dos desenvolvedores esteja familiarizada apenas com sua encarnação .Net como SqlDependency, a Notificação de Consulta pode ser usada como um mecanismo persistente e duradouro para detectar alterações de dados. Comparado com o verdadeiro controle de alterações, ele será realmente leve e sua semântica está mais próxima de suas necessidades (algo, qualquer coisa , alterado, então você precisa executar novamente a consulta).

Mas no final, no seu lugar, eu realmente reconsideraria minhas suposições e voltaria à prancheta. Talvez você possa usar o envio de logs ou a replicação para configurar um banco de dados de relatórios em um servidor diferente. O que li nas entrelinhas é que você precisa de um pipeline de ETL adequado e um data warehouse de análise...

Parece que estou dois anos atrasado para o jogo, aqui, mas há de fato uma maneira bastante leve de fazer o que você está pedindo.

Existem dois mecanismos do SQL Server que podem ajudá-lo. Sua solução final pode ser um híbrido dos dois.

Alterar rastreamento . O SQL Server tem a capacidade de colocar tabelas específicas sob observação, registrando apenas quais linhas foram alteradas (por seu valor de chave primária) e que tipo de alteração foi (Inserir, Atualizar ou Excluir). Depois de configurar a detecção de alterações em um conjunto de tabelas, uma consulta leve pode informar se alguma alteração foi feita na tabela desde a última vez que você verificou. A sobrecarga é aproximadamente igual à manutenção de um índice simples adicional.

Versão de linha / timestamp . Este é um tipo de coluna varbinary de 8 bytes (castable para um BigInt) que é incrementado, em todo o banco de dados, sempre que uma linha que contém um é inserida ou atualizada (não ajuda com exclusões). Se você indexasse essas colunas, poderia saber facilmente se os dados da linha foram alterados comparando o MAX(timestamp) com seu valor desde a última vez que foi avaliado. Como o valor está aumentando monotonicamente, isso forneceria uma indicação confiável de que os dados foram alterados se o novo valor for maior do que na última vez que você o verificou.

Se a fonte for somente inserção, forneça uma IDENTITYcoluna. Quando você faz sua transferência de dados, você registra o valor mais alto gravado. Durante a próxima transferência, você só precisa consultar valores maiores que os registrados durante a transferência anterior. Fazemos isso para transferir registros de log para um data warehouse.

Para linhas atualizáveis, adicione um sinalizador "sujo". Ele terá três valores - limpo, sujo e excluído. As consultas diárias terão que omitir linhas com o sinalizador definido como "excluído". Isso será caro em manutenção, teste e tempo de execução. Após a grande consulta, você menciona que todas as linhas marcadas para exclusão devem ser removidas e o sinalizador redefinido para todas as outras. Isso não vai dimensionar bem.

Uma alternativa mais leve ao Change Data Capture é o Change Tracking . Ele não informará quais valores foram alterados, apenas que a linha foi alterada desde a última consulta. As funções integradas facilitam a recuperação de valores alterados e o gerenciamento de rastreamento. Tivemos sucesso usando CT para processar cerca de 100.000 alterações por dia em uma tabela de 100.000.000 linhas.

As Notificações de Consulta atuam em uma alavanca ainda mais alta - no nível de um conjunto de resultados. Conceitualmente, é como definir uma visão. Se o SQL Server detectar que alguma linha retornada por essa exibição foi alterada, ele acionará uma mensagem para o aplicativo. Não há indicação de quantas linhas foram alteradas ou quais colunas. Há apenas uma mensagem simples dizendo "algo aconteceu". Cabe ao aplicativo perguntar e reagir. Praticamente é muito mais complexo do que isso, como você pode imaginar. Há restrições sobre como a consulta pode ser definida e a notificação pode ser acionada para outras condições além dos dados alterados. Quando a notificação é acionada, ela é removida. Se outras atividades de interesse ocorrerem posteriormente, nenhuma outra mensagem será enviada.

No contexto da pergunta do OP, o QN terá a vantagem de ter baixa sobrecarga para configurar e baixo custo de tempo de execução. Pode ser um esforço significativo estabelecer e manter um regime rigoroso de assinatura-mensagem-reação. Como a tabela de dados é grande, é provável que haja alterações frequentes nela, o que significa que a notificação provavelmente será acionada na maioria dos ciclos de processamento. Como não há indicação do que mudou o processamento incremental dos deltas não será possível, como aconteceria com CT ou CDC. A sobrecarga devido ao disparo falso é cansativa, mas mesmo no pior caso, a consulta cara não precisa ser executada com mais frequência do que é atualmente.

SqlTableDependency

SqlTableDependency é um componente de implementação de alto nível para acessar notificações contendo valores de registro de tabela no banco de dados SQL Server.

SqlTableDependency é um componente C# genérico usado para receber notificações quando o conteúdo de uma tabela de banco de dados especificada é alterado.

Qual é a diferença com .NET SqlDepenency?

Basicamente, a principal diferença é que SqlTableDependency envia eventos contendo valores para o registro inserido, alterado ou excluído, assim como a operação DML (insert/delete/update) executada na tabela: SqlDepenency não informa quais dados foram alterados na tabela tabela de banco de dados, eles apenas dizem que algo mudou.

Dê uma olhada no projeto GITHUB .

Se as atualizações que você espera afetarem um índice (e somente se), você poderá usar a tabela do sistema sys.dm_db_index_usage_statspara detectar a última atualização de um índice na tabela em questão. Você usaria o last_user_updatecampo.

Por exemplo, para obter as tabelas atualizadas mais recentemente:

select
    object_name(object_id) as OBJ_NAME, *
from
    sys.dm_db_index_usage_stats
where
    database_id = db_id(db_name())
order by
    dm_db_index_usage_stats.last_user_update desc

Ou, para verificar se uma tabela específica foi alterada desde uma data específica:

select
    case when count(distinct object_id) > 0 then 1 else 0 end as IS_CHANGED
from
    sys.dm_db_index_usage_stats
where
    database_id = db_id(db_name())
    and object_id = object_id('MY_TABLE_NAME')
    and last_user_update > '2016-02-18'