none
Change Tracking Functions x Estatisticas RRS feed

  • Pergunta

  • Bom dia senhores.

    Tenho uma tela de grid para expor informações de produção ao usuário. Essa tela deve detectar se houve alteração nos dados dados da tabela e fazer refresh, (se isso estiver habilitado pelo próprio usuário). Inicialmente resolvi isso com trigger na tabela e uma tabela de apoio. O trigger atualiza uma data na tabela de apoio. A tela verifica essa data e faz o refresh quando sua variável for menor que a data na tabela de apoio.

    Agora estou buscando uma maneira mais pratica de fazer isso.

    Encontrei duas formas:

    1 - na view  sys.dm_db_index_usage_stats há uma coluna last_user_update que indica a ultima vez que os dados foram alterados. Nessa view há uma linha para cada índice, me bastaria pegar a data\hora mais recente. O problema nessa solução seria quando as estatísticas fossem apagadas para melhora de performance.

    2 – A outra forma seria usando as Change Tracking Functions. Ainda nao estudei esta forma, mas me parece que ela serviria.

    Minha pergunta é se alguem ja fez algo parecido e se teria alguma dica de qual a melhor forma.

    Obrigado.

     

    quinta-feira, 29 de janeiro de 2015 11:55

Respostas

  • CeiltonLM,

    A muito tempo eu utilizei esta funcionalidade que foi implementada a partir da versão 2008 do SQL Server.

    Acredito que poderá ser sim uma solução, mas o que você terá que analisar é se este tracking que você estará fazendo terá exatamente a informação que você precisa.

    Veja se este exemplo poderá ajudar:

    CREATE DATABASE DB_test;
    GO
    
    ALTER DATABASE DB_test SET
    CHANGE_TRACKING = ON
     (AUTO_CLEANUP = ON,          -- automatic tracking table clean up process
      CHANGE_RETENTION = 1 HOURS  -- specify the time frame for which tracked information will be maintained -- 
    );
    GO
    
    --create test table
    USE DB_test;
    GO
    
    CREATE TABLE dbo.tb
    (id int
     CONSTRAINT PK_tb_id PRIMARY KEY,
     col1 int,
     col2 varchar(10),
     col3 nvarchar(max),
     col4 varbinary(max));
    GO
    
    ALTER TABLE dbo.tb
    ENABLE CHANGE_TRACKING
     WITH(TRACK_COLUMNS_UPDATED = ON  -- With this option, you can include columns also whose values were changed
     );
    GO
    
    SELECT CHANGE_TRACKING_CURRENT_VERSION() as Currentversion,
           CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')) as minvalidversion;
    GO
    
    -- testing
    
    -- a.insert data 
    INSERT dbo.tb(id, col1, col2, col3, col4)
    VALUES(1,1, 'AA', 'AAA', 0x1),
    	  (2,2, 'BB', 'BBB', 0x2),
          (3,3, 'CC', 'CCC', 0x2);
     
    SELECT CHANGE_TRACKING_CURRENT_VERSION() as Currentversion,
           CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')) as minvalidversion,
           *
    FROM CHANGETABLE(CHANGES dbo.tb, 0) CHG LEFT JOIN dbo.tb DATA
                                             ON DATA.id = CHG.id;
     
    -- b. update data 
    BEGIN TRAN;
    UPDATE dbo.tb SET
    col1 = 11
    WHERE id = 1;
     
    UPDATE dbo.tb SET
    col1 = 111
    WHERE id = 1;
    COMMIT TRAN;
     
    SELECT CHANGE_TRACKING_CURRENT_VERSION() as Currentversion,
           CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')) as minvalidversion,
           *
    FROM CHANGETABLE(CHANGES dbo.tb, 0) CHG LEFT JOIN dbo.tb DATA
                                             ON DATA.id = CHG.id;

    Outro recurso que eu acho que possa se enquadrar melhor na sua necessidade é Change Data Capture, conhecido como CDC, também implementado a partir do SQL Server 2008.

    Veja um exemplo:

    -- Habilitando o CDC para o Banco de Dados --
    Use SQLMagazine
    Go
    
    Exec sys.sp_cdc_enable_db
    Go
    
    -- Desabilitando o CDC para o Banco de Dados --
    Use SQLMagazine
    Go
    
    Exec sys.sp_cdc_disable_db
    Go
    
    -- Criando a Tabela de Exemplo --
    Create Table Produtos
     (Codigo Int Identity(1,1),
       Descricao VarChar(20))
    Go
    
    -- Adicionando a Chave Primaria --
    Alter Table Produtos
        Add Constraint [PK_Codigo_Produtos] Primary Key (Codigo)
    Go
    
    -- Inserindo a Massa de Registros para Teste --   
    Declare @ContadorRegistros Int
    Set @ContadorRegistros=1
    
    While @ContadorRegistros <=1000
     Begin
     
      If @ContadorRegistros =1
       Insert Into Produtos Values ('Produto Nº: 1')
      Else
       Insert Into Produtos Values ('Produto Nº: '+Convert(VarChar(4),@@Identity+1))
       
       Set @ContadorRegistros += 1;
     End
     
    -- Visualizando os Dados --   
    Select * from Produtos
          
    -- Habilitando o Change Data Capture para trabalhar sobre a table Produtos --
    EXECUTE sys.sp_cdc_enable_table
        @source_schema = N'dbo',
        @source_name = N'Produtos',
        @role_name = N'cdc_Admin';
    GO
    
    -- Retornando todas as linhas capturadas pelo CDC --
    DECLARE @from_lsn binary(10), 
                      @to_lsn binary(10)
    
    SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Produtos')
    SET @to_lsn   = sys.fn_cdc_get_max_lsn()
    
    SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Produtos
      (@from_lsn, @to_lsn, N'all')
    Go
    
    -- Inserindo novos dados --
    Insert Into Produtos Values ('Produto Nº: '+Convert(VarChar(4),@@Identity+1))
    Go
    
    -- Atualizando dados já existentes --
    Update Produtos
    Set Descricao= Descricao+' - Upd'
    Where Codigo Between 11 And 21
    Go
    
    -- Retornando todas as linhas capturadas pelo CDC com Net Changes--
    DECLARE @from_lsn binary(10), 
                      @to_lsn binary(20)
    
    SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Produtos')
    SET @to_lsn   = sys.fn_cdc_get_max_lsn()
    
    SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Produtos
                                  (@from_lsn, @to_lsn, N'all')
    GO
    
    -- Retornando as colunas utilizadas pelo CDC para Captura --
    Execute sys.sp_cdc_get_captured_columns 
                             @capture_instance = N'dbo_Produtos';
    Go
    
    -- Retornando informações de configuração da captura de dados de alteração de uma tabela específica --
    Execute sys.sp_cdc_help_change_data_capture 
                              @source_schema = N'dbo', 
                              @source_name = N'Produtos';
    Go
    
    -- Retornando informações de configuração da captura de dados de alteração de todas as tabelas --
    EXECUTE sys.sp_cdc_help_change_data_capture;

    Há algum tempo eu escrevi um artigo específico sobre esta funcionalidade para revista SQLMagazine.


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 30 de janeiro de 2015 17:03