Início / dba / Perguntas / 343931
Accepted
Maury Markowitz
Asked: 2024-11-30 01:57:58 +0800 CST

Uma boa maneira de obter "tempo de transação" ou similar no SQL Server para rastreamento de alterações

  • 772

Gostaria de adicionar informações de versão ao nosso BD - tudo o que será usado é para verificar se a versão do usuário de uma linha mudou desde que ele a leu. Estou pensando em adicionar uma coluna chamada _VERSIONàs duas dúzias de tabelas em questão. Como parte de uma UPDATEdeclaração em lote, eu teria algo como SET ..._VERSION=something_or_other. Sou agnóstico sobre quais dados essa coluna contém.

Uma solução que vi é usar uma coluna datetime e então obter o horário de início da transação de sys.dm_tran_active_transactions, selecionando a linha para CURRENT_TRANSACTION_ID(). Para nossas necessidades, a precisão de 1/300 será mais do que suficiente. Entendo que usar CURRENT_TIMESTAMPnão é tão útil nessa situação porque isso mudará conforme o lote prossegue.

Se isso for canônico, farei isso. É fácil de implementar.

Mas essa é a melhor maneira para usos realmente simples? Existe algum outro valor no sys.dm_tran...que fornece o mesmo resultado, mas pode ser mais fácil de armazenar e WHERE do que um datetime?

OBSERVAÇÃO: sim, estou ciente das tabelas temporais e do rastreamento de alterações da MS e me disseram para não usá-los.

sql-server

3 respostas

  1. Best Answer

    verificar se a versão do usuário de uma linha mudou desde que ele a leu

    Você não precisa de nenhum tempo de transação.

    o tipo rowversion é introduzido para ele

    O tipo de dados rowversion é apenas um número incremental e não preserva uma data ou hora.

    • 4

  2. Outra resposta sugere adicionar uma rowversioncoluna às suas tabelas. Isso pode funcionar muito bem, mas você pode ficar infeliz se suas tabelas forem grandes e altamente transacionais, pois pode haver um bloqueio significativo ao adicionar essas colunas. Também não há grandes truques ou atalhos para isso, que eu tenha visto.

    Talvez seja melhor adicionar uma coluna não-NULLable com um valor padrão às suas tabelas — datetime2(0)seria bom — já que essa é uma alteração somente de metadados para a maioria das versões modernas do SQL Server (ao contrário da rowversionalteração) e usar AFTERgatilhos para manter o controle de instruções concluídas com sucesso UPDATE. Você não precisa deles para consultas INSERTou DELETEpor motivos óbvios:

    • Todas as inserções receberão o padrão SYSDATETIME()para quando forem inseridas
    • Exclusões físicas (não exclusões suaves) removerão a linha completamente

    Torna as coisas bem simples.

    com script

    Para a coluna:

    ALTER TABLE
    dbo.Users
ADD
    LastModified datetime2(0) NOT NULL
    DEFAULT SYSDATETIME();

    Para o gatilho:

    CREATE OR ALTER TRIGGER
    dbo.User_Updates
ON dbo.Users
AFTER 
    UPDATE
AS
BEGIN 
    IF ROWCOUNT_BIG() = 0
    BEGIN
        RETURN;
        /*Bail early if no rows*/
    END;
    
    /*
    Set these in case they've been 
    altered in an outer context
    */
    SET NOCOUNT, XACT_ABORT ON;
    SET ROWCOUNT 0;

    IF UPDATE(LastModified)
    BEGIN
        RETURN;
        /*
        Bail if someone modifies 
        the column manually
        */
    END;
    
    /*Update any qualifying rows*/
    UPDATE
        u
    SET
        u.LastModified = SYSDATETIME()
    FROM dbo.Users AS u
    WHERE EXISTS
    (
        SELECT
            1/0
        FROM Inserted AS i
        WHERE u.Id = i.Id
    );
END;

    Teste, 1-2-3:

    BEGIN TRANSACTION
    SELECT
        u.Id,
        u.Age,
        u.LastModified
    FROM dbo.Users AS u
    WHERE u.Id = 22656;
    
    UPDATE
        u
    SET
       u.Age = 138
    FROM dbo.Users AS u
    WHERE u.Id = 22656;
    
    SELECT
        u.Id,
        u.Age,
        u.LastModified
    FROM dbo.Users AS u
    WHERE u.Id = 22656;
ROLLBACK TRANSACTION;

    Resultados:

    NOZES

    Para o gatilho, você pode usar a tabela virtual Insertedou Deleted. Não importa aqui, porque ambas conterão o mesmo conjunto básico de linhas.

    Importaria se você quisesse a imagem anterior ( Deletedtabela) ou posterior ( Insertedtabela) para usar os resultados para auditar os valores realmente alterados.

    • 4

  3. Uma resposta brilhante no StackOverflow por @MartinSmith usa um recurso CDC, mas sem realmente aplicar o CDC à tabela.

    CREATE TABLE dbo.YourTable
(  
    FooId INT PRIMARY KEY CLUSTERED,   
    FooName VARCHAR(50) NOT NULL,
    modstamp DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,   
    MaxDateTime2 DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,     
    PERIOD FOR SYSTEM_TIME (modstamp,MaxDateTime2)    
);

    Tem algumas limitações.

    • O horário armazenado será atualizado pelo sistema e sempre será UTC.
    • Há uma necessidade de declarar uma segunda coluna (MaxDateTime2 acima) que é completamente supérflua para este caso de uso. Mas ela pode ser marcada como oculta, tornando-a mais fácil de ignorar.

    Note que não há uma tabela de histórico de Tabelas Temporais aqui, é apenas um par de colunas de atualização automática. Então isso não deve ser um problema se você estiver preocupado com o desempenho da Tabela Temporal.

    • 1

relate perguntas