Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg  [new]
Кифирчик
Member

Откуда: СПб
Сообщений: 934
Имеется несколько связных таблиц, для которых сервер приложения дергает запрос возвращающий их контрольную сумму.
В примере создана идентичная структура. Контрольная сумма возвращается примерно для 200..300 строк, работает довольно быстро, с точки зрения перфоманса такой вариант вполне устраивает.
+
declare @tableMain table(pKey uniqueidentifier, valInt int, isAct int,  updateDate datetime)
declare @relTable1 table(pKey uniqueidentifier, valInt1 int, updateDate datetime)
declare @relTable2 table(pKey uniqueidentifier, valInt2 int, updateDate datetime)
declare @relTable3 table(tKey uniqueidentifier, pKey uniqueidentifier, valInt3 int, updateDate datetime)
declare @relTable4 table(tKey uniqueidentifier, pKey uniqueidentifier, valInt4 int, updateDate datetime)

declare @cn int = 0;

declare @pKey uniqueidentifier
declare @tKey uniqueidentifier

while @cn <= 40
begin
    set @pKey = newid();
    insert into @tableMain select @pKey, @cn, @cn%2, getdate()
    insert into @relTable1 select @pKey, @cn+2, getdate()
    insert into @relTable2 select @pKey, @cn+4, getdate()
    
    declare @cn2 int = 0;
    while @cn2 < 4
    begin
        set @tKey = newid();

        insert into @relTable3 select @tKey, @pKey, @cn+@cn2, getdate()
        insert into @relTable4 select @tKey, @pKey, @cn2+@cn/2, getdate()

        set @cn2 = @cn2 + 1;
    end

    set @cn = @cn + 1;

end

--select * from @tableMain
--select * from @relTable1
--select * from @relTable2
--select * from @relTable3
--select * from @relTable4

;with actList as(select pKey from @tableMain where isAct = 1)
, list as (
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @tableMain as x join actList on actList.pKey = x.pKey
    group by x.pKey
    union
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @relTable1 as x join actList on actList.pKey = x.pKey
    group by x.pKey
    union
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @relTable2 as x join actList on actList.pKey = x.pKey
    group by x.pKey
    union
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @relTable3 as x join actList on actList.pKey = x.pKey
    group by x.pKey
    union
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @relTable4 as x join actList on actList.pKey = x.pKey
    group by x.pKey
)
select 'BeforeUpdate' as info, checksum_agg(binary_checksum(pKey,chkSum,MaxUpdateDate)) as Checksum from list
-- select 'BeforeUpdate' as info,pKey, checksum_agg(binary_checksum(pKey,chkSum,MaxUpdateDate)) as Checksum from list group by pKey

update @tableMain set updateDate = getdate()
where pKey in (select top 10 pKey from @tableMain where isAct = 1)


;with actList as(select pKey from @tableMain where isAct = 1)
, list as (
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @tableMain as x join actList on actList.pKey = x.pKey
    group by x.pKey
    union
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @relTable1 as x join actList on actList.pKey = x.pKey
    group by x.pKey
    union
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @relTable2 as x join actList on actList.pKey = x.pKey
    group by x.pKey
    union
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @relTable3 as x join actList on actList.pKey = x.pKey
    group by x.pKey
    union
    select 
        x.pKey
        , checksum_agg(binary_checksum(*)) as chkSum
        , max(x.updateDate) as MaxUpdateDate
    from @relTable4 as x join actList on actList.pKey = x.pKey
    group by x.pKey
)
select 'AfterUpdate' as info, checksum_agg(binary_checksum(pKey,chkSum,MaxUpdateDate)) as Checksum from list
-- select 'AfterUpdate' as info,pKey, checksum_agg(binary_checksum(pKey,chkSum,MaxUpdateDate)) as Checksum from list group by pKey


В процессе отладки я обновлял UpdateDate нескольких строк и смотрел как подтягиваются изменения в сервер приложений.
в примере это такой код
update @tableMain set updateDate = getdate()
where pKey in (select top 10 pKey from @tableMain where isAct = 1)

В какой-то момент я заметил, что реакции на обновление UpdateDate нет.
Покопался, и понял что запрос вернул туже контрольную сумму что и до обновления. Можно было бы еще 10 раз обновить UpdateDate но результат не менялся.
я попробовал развернуть списки, в контексте примера, выбрать контрольные суммы для каждого pKey
они определенно разные (приложенный рисунок - скрин реальных данных до обновления UpdateDate и после).
Спустя пол часа, я повторил обновление - и получил тот же результат.

Но, если обновить не 10, а 11 строк - то контрольная сумма уже поменяется.
update @tableMain set updateDate = getdate()
where pKey in (select top 11 pKey from @tableMain where isAct = 1)

через какое-то время проблема возвращается, потом может пропасть.

либо если я поменяю набор полей по которому итоговый checksum_agg считается
checksum_agg(binary_checksum(pKey,chkSum,MaxUpdateDate))
-- меняем на
checksum_agg(binary_checksum(chkSum,MaxUpdateDate))

то код снова реагирует на изменения UpdateDate

Пример хоть и повторяет используемый подход, но повторить результат с одинаковой контрольной суммой не получилось.
Еще отличие, в реальном запросе все таблички с хинтами with(nolock).

Как альтернатива - получать из базы список контрольных сумм, которые всегда корректно меняются, но тягать один int много быстрее чем список Guid/Int из 300 строк, еще и каждые 15 секунд, хочется как-то этот запрос подпилить.
Также хочется чтоб все решение было в одном SQL запросе который сторится в сервере приложения, без триггеров, без механизмов профайлинга и отслеживания транзакций.

Вопросы:
- я понимаю что checksum в принципе функция которая может повторяться, но возможно ли сделать какие-то модификации запроса чтоб эти повторения случались намного реже?
- какие есть альтернативы комбинации checksum_agg+binary_checksum?

К сообщению приложен файл. Размер - 30Kb
15 сен 20, 22:23    [22197870]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
Вы прежде, чем изобретать велосипеды, изучите матчасть.
Для отслеживания изменений данных в таблицах давно придумали rowversion.
15 сен 20, 23:14    [22197928]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg  [new]
Кифирчик
Member

Откуда: СПб
Сообщений: 934
Гавриленко Сергей Алексеевич
Вы прежде, чем изобретать велосипеды, изучите матчасть.
Для отслеживания изменений данных в таблицах давно придумали rowversion.

Без велосипедов не интересно )
тыкался уже с rowversion, вот еслиб это поле еще обновлялось когда запись удаляется

как из нескольких таблиц собрать rowversion в одно число на выходе?
на вскидку в голову пришло так, но что-то мне подсказывает что это не айс вариант
+
declare @tableMain table(pKey uniqueidentifier, valInt int, isAct int,  updateDate datetime, ver rowversion)
declare @relTable1 table(pKey uniqueidentifier, valInt1 int, updateDate datetime, ver rowversion)
declare @relTable2 table(pKey uniqueidentifier, valInt2 int, updateDate datetime, ver rowversion)
declare @relTable3 table(tKey uniqueidentifier, pKey uniqueidentifier, valInt3 int, updateDate datetime, ver rowversion)
declare @relTable4 table(tKey uniqueidentifier, pKey uniqueidentifier, valInt4 int, updateDate datetime, ver rowversion)

declare @cn int = 0;

declare @pKey uniqueidentifier
declare @tKey uniqueidentifier

while @cn <= 10
begin
    set @pKey = newid();
    insert into @tableMain select @pKey, @cn, @cn%2, getdate(), null
    insert into @relTable1 select @pKey, @cn+2, getdate(), null
    insert into @relTable2 select @pKey, @cn+4, getdate(), null
    
    declare @cn2 int = 0;
    while @cn2 < 4
    begin
        set @tKey = newid();

        insert into @relTable3 select @tKey, @pKey, @cn+@cn2, getdate(), null
        insert into @relTable4 select @tKey, @pKey, @cn2+@cn/2, getdate(), null

        set @cn2 = @cn2 + 1;
    end

    set @cn = @cn + 1;

end

-- select * from @tableMain
-- select * from @relTable1
-- select * from @relTable2
-- select * from @relTable3
-- select * from @relTable4

-- select * from @tableMain
-- select * from @relTable4

;with actList as(select pKey from @tableMain where isAct = 1)
, list as (
    select x.pKey, 'table0' as [table], checksum_agg(binary_checksum(ver)) as chkSum
    from @tableMain as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table1' as [table], checksum_agg(binary_checksum(ver)) as chkSum   
    from @relTable1 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table2' as [table], checksum_agg(binary_checksum(ver)) as chkSum
    from @relTable2 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table3' as [table], checksum_agg(binary_checksum(ver)) as chkSum    
    from @relTable3 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table4' as [table] , checksum_agg(binary_checksum(ver)) as chkSum
    from @relTable4 as x join actList on actList.pKey = x.pKey group by x.pKey
)
select 'with grouping' as info, checksum_agg(binary_checksum(chkSum)) as Checksum from list
union
select 'with max' as info, checksum_agg(binary_checksum(x.Ver)) as Checksum
 from (
    select 't' as info,  max(ver) as ver from @tableMain
    union
    select 't1',  max(ver) from @relTable1
    union
    select 't2',  max(ver) from @relTable2
    union 
    select 't3',  max(ver) from @relTable3
    union
    select 't4',  max(ver) from @relTable4
 ) as x
 union
select 'with max2', checksum(a0, a1, a2, a3, a4) from
    (select
         (select max(ver) from @tableMain) as a0
       , (select max(ver) from @relTable1) as a1
       , (select max(ver) from @relTable2) as a2
       , (select max(ver) from @relTable3) as a3
       , (select max(ver) from @relTable4) as a4
    )as x

 insert into @tableMain select newid(), 654, 987, getdate(), null

 
;with actList as(select pKey from @tableMain where isAct = 1)
, list as (
    select x.pKey, 'table0' as [table], checksum_agg(binary_checksum(ver)) as chkSum
    from @tableMain as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table1' as [table], checksum_agg(binary_checksum(ver)) as chkSum   
    from @relTable1 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table2' as [table], checksum_agg(binary_checksum(ver)) as chkSum
    from @relTable2 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table3' as [table], checksum_agg(binary_checksum(ver)) as chkSum    
    from @relTable3 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table4' as [table] , checksum_agg(binary_checksum(ver)) as chkSum
    from @relTable4 as x join actList on actList.pKey = x.pKey group by x.pKey
)
select 'with grouping after ins' as info, checksum_agg(binary_checksum(chkSum)) as Checksum from list
union
select 'with max' as info, checksum_agg(binary_checksum(x.Ver)) as Checksum
 from (
    select 't' as info,  max(ver) as ver from @tableMain
    union
    select 't1',  max(ver) from @relTable1
    union
    select 't2',  max(ver) from @relTable2
    union 
    select 't3',  max(ver) from @relTable3
    union
    select 't4',  max(ver) from @relTable4
 ) as x
 union
select 'with max2', checksum(a0, a1, a2, a3, a4) from
    (select
         (select max(ver) from @tableMain) as a0
       , (select max(ver) from @relTable1) as a1
       , (select max(ver) from @relTable2) as a2
       , (select max(ver) from @relTable3) as a3
       , (select max(ver) from @relTable4) as a4
    )as x

 update @relTable4 set valInt4 = 12345 where tKey in (select top 1 tKey from @relTable4)

 ;with actList as(select pKey from @tableMain where isAct = 1)
, list as (
    select x.pKey, 'table0' as [table], checksum_agg(binary_checksum(ver)) as chkSum
    from @tableMain as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table1' as [table], checksum_agg(binary_checksum(ver)) as chkSum   
    from @relTable1 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table2' as [table], checksum_agg(binary_checksum(ver)) as chkSum
    from @relTable2 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table3' as [table], checksum_agg(binary_checksum(ver)) as chkSum    
    from @relTable3 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table4' as [table] , checksum_agg(binary_checksum(ver)) as chkSum
    from @relTable4 as x join actList on actList.pKey = x.pKey group by x.pKey
)
select 'with grouping after upd' as info, checksum_agg(binary_checksum(chkSum)) as Checksum from list
union
select 'with max ' as info, checksum_agg(binary_checksum(x.Ver)) as Checksum
 from (
    select 't' as info,  max(ver) as ver from @tableMain
    union
    select 't1',  max(ver) from @relTable1
    union
    select 't2',  max(ver) from @relTable2
    union 
    select 't3',  max(ver) from @relTable3
    union
    select 't4',  max(ver) from @relTable4
 ) as x
 union
select 'with max2', checksum(a0, a1, a2, a3, a4) from
    (select
         (select max(ver) from @tableMain) as a0
       , (select max(ver) from @relTable1) as a1
       , (select max(ver) from @relTable2) as a2
       , (select max(ver) from @relTable3) as a3
       , (select max(ver) from @relTable4) as a4
    )as x

 delete from @relTable4 where tKey in (select top 1 tKey from @relTable4)

 ;with actList as(select pKey from @tableMain where isAct = 1)
, list as (
     select x.pKey, 'table0' as [table], checksum_agg(binary_checksum(ver)) as chkSum
    from @tableMain as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table1' as [table], checksum_agg(binary_checksum(ver)) as chkSum   
    from @relTable1 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table2' as [table], checksum_agg(binary_checksum(ver)) as chkSum
    from @relTable2 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table3' as [table], checksum_agg(binary_checksum(ver)) as chkSum    
    from @relTable3 as x join actList on actList.pKey = x.pKey group by x.pKey
    union
    select x.pKey, 'table4' as [table] , checksum_agg(binary_checksum(ver)) as chkSum
    from @relTable4 as x join actList on actList.pKey = x.pKey group by x.pKey
)
select 'with grouping after del' as info, checksum_agg(binary_checksum(chkSum)) as Checksum from list
union
select 'with max' as info, checksum_agg(binary_checksum(x.Ver)) as Checksum
 from (
    select 't' as info,  max(ver) as ver from @tableMain
    union
    select 't1',  max(ver) from @relTable1
    union
    select 't2',  max(ver) from @relTable2
    union 
    select 't3',  max(ver) from @relTable3
    union
    select 't4',  max(ver) from @relTable4
 ) as x
union
select 'with max2', checksum(a0, a1, a2, a3, a4) from
    (select
         (select max(ver) from @tableMain) as a0
       , (select max(ver) from @relTable1) as a1
       , (select max(ver) from @relTable2) as a2
       , (select max(ver) from @relTable3) as a3
       , (select max(ver) from @relTable4) as a4
    )as x


Сообщение было отредактировано: 16 сен 20, 01:05
16 сен 20, 01:05    [22197986]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
Кифирчик
тыкался уже с rowversion, вот еслиб это поле еще обновлялось когда запись удаляется
Завести флажок is_deleted и заменить удаление на апдейт.

Кифирчик
как из нескольких таблиц собрать rowversion в одно число на выходе?
Зачем вы все это городите?.. Достаточно запомнить максимальное значение rowversion для таблицы. И далее если в таблице текущее максимальное больше ранее запомненного, то в ней были изменения. Их даже элементарно можно будет выбрать. А если еще и индексы сделать...
16 сен 20, 01:39    [22197989]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg  [new]
Кифирчик
Member

Откуда: СПб
Сообщений: 934
Гавриленко Сергей Алексеевич
Зачем вы все это городите?

У меня в памяти приложения "документ" состоящий из нескольких таблиц в БД и мне интересно ловить изменения в этих нескольких таблицах.
И по этому городить нужно, либо в SQL либо в C#. В SQL предпочтительно. Хочется один запрос, и на выходе одно INT число.
Еще в примере приведено 4 запроса в 3х вариантах, для сравнения , возможно вам это "нагорожено" показалось.

Задумка с тем чтоб использовать rowversion вместо binary_checksum(*) однозначно лучше, и такое вариант будет точно лучше по производительности. хотя это требует внести изменения в генератор классов, чтоб не тянуть эти поля в EF.
То есть, можно сказать ваша рекомендация (в контексте тестового примера) такая:
select
         (select max(ver) from @tableMain) as a0
       , (select max(ver) from @relTable1) as a1
       , (select max(ver) from @relTable2) as a2
       , (select max(ver) from @relTable3) as a3
       , (select max(ver) from @relTable4) as a4


Но это не ловит удаления строк.
А идея с отказом от физического удаления мне не нравится. То что удаляется - оно заказчику не нужно, а сложность определенную вносит. Как в том анекдоте "не... эти хохмочки я уже видел")))
На предыдущих проектах на практике это не несло практической ценности и создавало больше неудобств, в текущем решили оставить IsDeleted только для справочников, либо если заказчик скажет "нужны вот эти удаленные документы" (зная тему заказчика, предсказываю вероятность этого - 0.1% ).
Получается, на одной чаше весов "перешерстить весь код где эти таблицы используются, все отчеты" на другой "в одной из нескольких подсистем будет проще запрос для отслеживания изменений в этих 5 таблицах". Малова-то будет )))

если бы вариант с rowversion научить ловить удаления и собирать в одно число - было бы идеально
16 сен 20, 09:45    [22198108]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 2994
Кифирчик

если бы вариант с rowversion научить ловить удаления и собирать в одно число - было бы идеально

не совсем понял что нужно на выходе, но для отслеживания имеются CDC и, если позволяет версия, TEMPORAL TABLE
16 сен 20, 09:56    [22198119]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
Кифирчик,

https://docs.microsoft.com/ru-ru/dotnet/framework/data/adonet/sql/detecting-changes-with-sqldependency
16 сен 20, 10:13    [22198131]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg  [new]
Кифирчик
Member

Откуда: СПб
Сообщений: 934
попробую обобщить...

вариант особенности
checksum_agg+binary_checksum гуру MSSQL судя по всему такой подход удачным не кажется + видимо будет проблематично подобрать такой запрос на выходе которого будет одно число (контрольная сумма) которое будет гарантированно меняться при любой модификации таблиц и так чтоб обойтись без утяжелений в виде приведений/for xml path/MD5 и т.д.
rowversion требует поля IsDeleted и отказа от физического удаления
Change Tracking позволяет ловить изменения в конкретной таблице; по описанию microsoft прям рекомендует это как обеспечивающее эффективный механизм отслеживания изменений для приложений; доступно с SQL Server 2014 (если поставить обновление);требует дополнительных манипуляций с базами данных после развертывания
Change Data Capture что-то более "масштабное" чем Change Tracking; позволяет заглянуть прям в журнал SQL; требует агента (express версии видимо отметаются) и создает свою схему в базе с данными и ХП
Temporal tables доступна с 2016й версии; автоматически позволяет вести всю историю изменений
SqlDependency через SqlCommand позволяет подписаться на изменения в таблице; требует отдельных ролей & пользователей и постоянно висящего коннекта; судя по тому небольшому количеству информации в сети заводится не с пол пинка и в целом не очень популярна


ничего не упустил?

Сообщение было отредактировано: 16 сен 20, 23:11
16 сен 20, 23:14    [22198855]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg  [new]
aleks222
Member

Откуда:
Сообщений: 1027
Кифирчик
ничего не упустил?

Все смешалось в доме Облонских.

SqlDependency - это РЕАЛИЗАЦИЯ на .NET конкретной технологии слежения за изменениями.

Она неизбежно использует какие-то механизмы сервера.
Только мелкософт не рассказывает.
Профайлер расскажет.


"rowversion требует поля IsDeleted и отказа от физического удаления "
Только если у вас фантазии не хватает.

rowversion + count(*)

"checksum_agg+binary_checksum видимо будет проблематично подобрать такой запрос на выходе которого будет одно число (контрольная сумма) которое будет гарантированно меняться при любой модификации таблиц и так чтоб обойтись без утяжелений в виде приведений/for xml path/MD5 и т.д."

Вы бредите.
Все хэши и чексуммы ГАРАНТИРОВАННО дают коллизии.
Ибо невозможно пронумеровать миллиард вариантов данных с помощью 256 значений одного байта.

Сообщение было отредактировано: вчера, 06:03
вчера, 06:00    [22198886]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg  [new]
aleks222
Member

Откуда:
Сообщений: 1027
Ах да, чуть сам не забыл.
Банальный триггер на таблице.
вчера, 06:12    [22198887]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg  [new]
Кифирчик
Member

Откуда: СПб
Сообщений: 934
aleks222
"rowversion требует поля IsDeleted и отказа от физического удаления "
Только если у вас фантазии не хватает.

rowversion + count(*)

вы не поверите, пробовал, и binary_checksum(rowversion, count(*)) и binary_checksum(rowversion + count(*))
не помогает. более того, с rowversion я дупы начал ловить даже в тестовом примере
для отдельной строки все работает отлично, но когда хэши всех строк собираются в одну контрольную сумму, то получается не то что ожидаешь.

aleks222

"checksum_agg+binary_checksum видимо будет проблематично подобрать такой запрос на выходе которого будет одно число (контрольная сумма) которое будет гарантированно меняться при любой модификации таблиц и так чтоб обойтись без утяжелений в виде приведений/for xml path/MD5 и т.д."

Вы бредите.
Все хэши и чексуммы ГАРАНТИРОВАННО дают коллизии.
Ибо невозможно пронумеровать миллиард вариантов данных с помощью 256 значений одного байта.

эх.. а я то только вчера узнал как hash/checksum функции работают ...
во-первых не 256 а INT, от -2147483648 до +2147483648
во-вторых я ожидал не уникальности, а её очень низкой вероятности, чего не получается.

У себя сделал, временно и как самое быстрое решение - загружаю на сервер список и считаю контрольную сумму в C#
            var result = 0;

            var orderedList = list.OrderBy(c => c.Key);

            foreach( var item in orderedList)
            {
                result = (result * 397) ^ item.Key.GetHashCode();
                result = (result * 397) ^ item.Checksum.GetHashCode();
            }

            return result;

и все заработало.

+ новый тикет что нужно сделать через Change Tracking + проверку из "сервера приложения" что эта фитча включена и вывод предупреждения если включить забыли.

и дополню резюме по топику

вариант особенности в контексте проблемы топика
checksum_agg+binary_checksum хорошо работает построчно (с низкой вероятностью повторения результата при изменении данных строки) но плохо работает когда checksum нескольких строк агрегируешь в одну (высокая вероятность повторения результата при изменении данных одной из строк или их количества);более ресурсоемко по сравнению с вариантами ниже; но можно сильно ускорить если брать checksum не от всех полей а от rowversion; но не требует ничего настраивать в БД ненадежный велосипед
trigger требует поле в таблице либо отдельную таблицу плюс сам триггер подходит; требует модификации БД
rowversion требует поля IsDeleted и отказа от физического удаления подходит; требует более серьезной модификации БД для учета поля IsDeleted
Change Tracking позволяет ловить изменения в конкретной таблице; по описанию microsoft прям рекомендует это как обеспечивающее эффективный механизм отслеживания изменений для приложений; доступно с SQL Server 2014 (если поставить обновление);требует дополнительных манипуляций с базами данных после развертывания судя по всему самый true way вариант; но требует усилия чтоб при развертывании не забывали про включение этой фитчи
Change Data Capture что-то более "масштабное" чем Change Tracking; позволяет заглянуть прям в журнал SQL; требует агента (express версии видимо отметаются) и создает свою схему в базе с данными и ХП из пушки по воробьям
Temporal tables доступна с 2016й версии; автоматически позволяет вести всю историю изменений из пушки по воробьям
SqlDependency фитча ADO.NET позволяющая через SqlCommand позволяет подписаться на изменения в таблице; требует отдельных ролей & пользователей и постоянно висящего коннекта подходит; но смущает то что нужно держать подключение и требует усилия чтоб при развертывании не забывали про включение этой фитчи
вчера, 10:37    [22198975]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
Вот статья имеется здесь уже очень давно: Журналирование изменений структуры БД и данных
Многого нового нет, конечно...
вчера, 10:57    [22199002]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg  [new]
aleks222
Member

Откуда:
Сообщений: 1027
Кифирчик
вы не поверите, пробовал, и binary_checksum(rowversion, count(*)) и binary_checksum(rowversion + count(*))
не помогает. более того, с rowversion я дупы начал ловить даже в тестовом примере
для отдельной строки все работает отлично, но когда хэши всех строк собираются в одну контрольную сумму, то получается не то что ожидаешь.


Ты, страдалец, ничего не понял.
Разъясняю для непонятливых.

Проверка

максимума rowversion и значения count(*)

дает искомое без отмены удалений.

Кифирчик

эх.. а я то только вчера узнал как hash/checksum функции работают ...
во-первых не 256 а INT, от -2147483648 до +2147483648
во-вторых я ожидал не уникальности, а её очень низкой вероятности, чего не получается.

Знать и понимать, и грамотно применять - это, как показывает твой пример, очень разные вещи.
Иначе, хотя бы, хэш подлиннее выбрал.

Поэтому твоя табличка, страдалец, бесполезна чуть более, чем полностью.

Сообщение было отредактировано: вчера, 12:05
вчера, 12:07    [22199102]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg  [new]
Кифирчик
Member

Откуда: СПб
Сообщений: 934
aleks222

Ты, страдалец, ничего не понял.
Разъясняю для непонятливых.

Проверка

максимума rowversion и значения count(*)

дает искомое без отмены удалений.

звучит слишком категорично.
для одной таблицы - ок, согласен.
но условия применения могут быть очень разные.
вот вам притянутый пример когда искомое по max(rowversion) & count(*) не верно.
это довольно близко к моему кейсу, и конкретно в моем случае rowversion + count не самый удачный вариант, как и checksum_agg.
+
declare @tableMain table(pKey uniqueidentifier,                        valInt  int, isAct int, updateDate datetime, ver rowversion)
declare @relTable1 table(pKey uniqueidentifier,                        valInt1 int, isAct int, updateDate datetime, ver rowversion)
declare @relTable2 table(pKey uniqueidentifier,                        valInt2 int, isAct int, updateDate datetime, ver rowversion)
declare @relTable3 table(tKey uniqueidentifier, pKey uniqueidentifier, valInt3 int, isAct int, updateDate datetime, ver rowversion)
declare @relTable4 table(tKey uniqueidentifier, pKey uniqueidentifier, valInt4 int, isAct int, updateDate datetime, ver rowversion)

declare @cn int = 0;

declare @pKey1 uniqueidentifier = newid();
declare @pKey2 uniqueidentifier = newid();
declare @pKey3 uniqueidentifier = newid();
declare @pKey4 uniqueidentifier = newid();
declare @pKey5 uniqueidentifier = newid();

insert into @tableMain select @pKey1, 1, 1, getdate(), null
insert into @relTable1 select @pKey1, 2, 1, getdate(), null
insert into @relTable2 select @pKey1, 3, 1, getdate(), null

insert into @tableMain select @pKey2, 1, 1, getdate(), null
insert into @relTable1 select @pKey2, 2, 1, getdate(), null
insert into @relTable2 select @pKey2, 3, 1, getdate(), null

insert into @tableMain select @pKey3, 1, 1, getdate(), null
insert into @relTable1 select @pKey3, 2, 1, getdate(), null
insert into @relTable2 select @pKey3, 3, 1, getdate(), null

        insert into @relTable3 select newid(), @pKey1, 1, 1, getdate(), null
        insert into @relTable3 select newid(), @pKey1, 1, 0, getdate(), null
        insert into @relTable4 select newid(), @pKey1, 2, 1, getdate(), null
        insert into @relTable4 select newid(), @pKey1, 2, 0, getdate(), null

        insert into @relTable3 select newid(), @pKey2, 1, 1, getdate(), null
        insert into @relTable3 select newid(), @pKey2, 1, 0, getdate(), null
        insert into @relTable4 select newid(), @pKey2, 2, 0, getdate(), null
        insert into @relTable4 select newid(), @pKey2, 2, 1, getdate(), null

        insert into @relTable3 select newid(), @pKey3, 1, 1, getdate(), null
        insert into @relTable3 select newid(), @pKey3, 1, 0, getdate(), null
        insert into @relTable4 select newid(), @pKey3, 2, 1, getdate(), null
        insert into @relTable4 select newid(), @pKey3, 2, 0, getdate(), null

-------------------------------------------------

;with actList as(select pKey from @tableMain where isAct = 1)
, list as (
    select x.pKey, 'table0' as [table], max(ver) as chkSum, count(*) as cn  
    from @tableMain as x join actList on actList.pKey = x.pKey and x.isAct = 1  group by x.pKey
    union
    select x.pKey, 'table1' as [table], max(ver) as chkSum, count(*)
    from @relTable1 as x join actList on actList.pKey = x.pKey and x.isAct = 1  group by x.pKey
    union
    select x.pKey, 'table2' as [table], max(ver) as chkSum, count(*)
    from @relTable2 as x join actList on actList.pKey = x.pKey and x.isAct = 1  group by x.pKey
    union
    select x.pKey, 'table3' as [table], max(ver) as chkSum, count(*) as cn    
    from @relTable3 as x join actList on actList.pKey = x.pKey and x.isAct = 1 group by x.pKey
    union
    select x.pKey, 'table4' as [table], max(ver) as chkSum, count(*) as cn 
    from @relTable4 as x join actList on actList.pKey = x.pKey and x.isAct = 1 group by x.pKey
)
select 'before change' as info, max(chkSum), sum(cn) as Count from list

----------------------------------
-- модификация данных

declare @a uniqueidentifier  = (select top 1 tKey from @relTable4 where pKey = @pKey1 and isAct = 0)

print @a

update @relTable4 set isAct = 1 where tKey = @a

select 'updated rows '+isnull(cast(@@rowcount as nvarchar(1)),'null')

delete from @relTable4 where tKey = @a

select 'deleted rows '+isnull(cast(@@rowcount as nvarchar(1)),'null')

------------------------------------

;with actList as(select pKey from @tableMain where isAct = 1)
, list as (
    select x.pKey, 'table0' as [table], max(ver) as chkSum, count(*) as cn  
    from @tableMain as x join actList on actList.pKey = x.pKey and x.isAct = 1  group by x.pKey
    union
    select x.pKey, 'table1' as [table], max(ver) as chkSum, count(*)
    from @relTable1 as x join actList on actList.pKey = x.pKey and x.isAct = 1  group by x.pKey
    union
    select x.pKey, 'table2' as [table], max(ver) as chkSum, count(*)
    from @relTable2 as x join actList on actList.pKey = x.pKey and x.isAct = 1  group by x.pKey
    union
    select x.pKey, 'table3' as [table], max(ver) as chkSum, count(*)    
    from @relTable3 as x join actList on actList.pKey = x.pKey and x.isAct = 1 group by x.pKey
    union
    select x.pKey, 'table4' as [table], max(ver) as chkSum, count(*)
    from @relTable4 as x join actList on actList.pKey = x.pKey and x.isAct = 1 group by x.pKey
)
select 'after change'as info, max(chkSum), sum(cn) as Count from list


и пожалуйста больше мне ни чего не советуйте и не разъясняйте. вашу картину мира я уже понял.
вчера, 13:58    [22199210]     Ответить | Цитировать Сообщить модератору
 Re: Отслеживание изменений в таблицах, иногда не меняется результат checksum_agg  [new]
SERG1257
Member

Откуда:
Сообщений: 2746
Как мне кажется вы зря мешаете в кучу вычисление хэша по строке и вычисления хэша по группе строк (агрегат)
Для первого случая есть HASHBYTES https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15
Для второго придется писать CREATE AGGREGATE. Почти уверен что assembly для этого по алгоритму MD5 или SHA2 кто-то уже написал.
вчера, 17:36    [22199528]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить