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

Откуда: Мурманск
Сообщений: 1415
Как-то не сталкивался я раньше с необходимостью такой проверки, а теперь в легком недоумении как эту проверку реализовать средствами MS SQL. Уникальный индекс по данному полю сделать нельзя, а как тогда выкрутится?
10 сен 18, 11:46    [21669683]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
skyANA
Member

Откуда: Зеленоград
Сообщений: 27753
Страдалецъ,

HASHBYTES вызвать для колонки
10 сен 18, 12:00    [21669704]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
Страдалецъ
Member

Откуда: Мурманск
Сообщений: 1415
Т.е. завести поле под контрольную сумму и в тригере на добавление его заполнять этой функцией?
10 сен 18, 13:03    [21669771]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
iap
Member

Откуда: Москва
Сообщений: 46977
Страдалецъ
Т.е. завести поле под контрольную сумму и в тригере на добавление его заполнять этой функцией?
Надо ещё совпадения обрабатывать - ведь хэшкод или конрольная сумма неуникальны
10 сен 18, 13:38    [21669811]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
Не проверял, но должно работать.

create table dbo.TableName (
	Id int identity primary key,
	Data varbinary(max),
	DataHash as checksum(Data) --persisted не надо если будет индекс по этому полю
)
go

create index IDX_TableName_DataHash 
on dbo.TableName ( DataHash )
go

-- в процедуре которая обновляет данные
if exists ( select *
            from dbo.TableName
            where DataHash = checksum(@NewData) and Data = @NewData )
    throw 50001, 'Невозможно вставить дубликат Data!', 1;

-- или в триггере 
if exists ( select *
            from Inserted i
                join dbo.TableName t on i.DataHash = t.DataHash
                                    and i.Data = t.Data )
    throw 50001, 'Невозможно вставить дубликат Data!', 1;
10 сен 18, 14:17    [21669865]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
Тип image необходимо изменить на varbinary(max), иначе ничего не выйдет.

Реализвать проверку можно примерно так:
+
use tempdb;
go

create table dbo.t (id int identity primary key, s varbinary(max), tag1 as checksum(s), tag2 as datalength(s));
create index IX_t__tag1__tag2 on dbo.t (tag1, tag2);
go

create trigger dbo.tr_t__check_uniqueness
on dbo.t
after insert, update
as
begin
 set nocount on;

 if not exists(select 1 from inserted)
  return;
 
 if exists(select 1 from inserted i join dbo.t t on t.tag1 = i.tag1 and t.tag2 = i.tag2 where i.id <> t.id and i.s = t.s)
  begin
   raiserror('Нарушена уникальность столбца s', 16, 2);
   rollback;
   return;
  end;
end;
go

if object_id('tempdb..#s', 'U') is not null
 drop table #s;

declare
 @s1 varbinary(max) = cast(replicate(cast(0x01 as varbinary(max)), 9000) as varbinary(max)),
 @s2 binary(8000) = cast(replicate(0x01, 8000) as binary(8000)),
 @s3 binary(8000) = cast(replicate(0x02, 8000) as binary(8000));

declare
 @s4 varbinary(max) = convert(varbinary(max), convert(varchar(max), @s2, 2) + convert(varchar(max), @s2, 2), 2),
 @s5 varbinary(max) = convert(varbinary(max), convert(varchar(max), @s2, 2) + convert(varchar(max), @s3, 2), 2);

select s into #s from (values (@s1), (@s2), (@s3), (@s4), (@s5)) a(s);
go

insert into dbo.t (s) select s from #s;
go

insert into dbo.t (s) select top (1) s from #s;
go

select * from dbo.t;
go

drop table dbo.t;
go
10 сен 18, 14:19    [21669869]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
invm,

Мне кажется datalength все усложняет.
Достаточно и checksum если конечно у автора темы не гигантское кол-во строк в таблице.
10 сен 18, 14:31    [21669878]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
Владимир Затуливетер
Мне кажется datalength все усложняет.
Предлагаете проверять на совпадение два блоба, если у них одинаковый хеш, но разная длина?
10 сен 18, 14:37    [21669885]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
invm,

Да, т.к. вероятность такого совпадения ооооочень мала.
Подход checksum + datalength оправдан только на большом кол-во строк, когда появляются дубликаты по checksum. Поэтому не вижу смылса усложнять код в большинстве случаев.

Вот нагуглил ответ с цифрами. Для 32-х битных хешей кол-во хешируемых значений должно быть меньшь 250 тыс., на таком кол-ве вероятность появления дубликата при вставке уже близка к 100%.
https://stackoverflow.com/questions/37343529/how-many-rows-can-you-have-when-using-checksum-as-primary-key

я ожидал больших значений, не учел парадокс дней рождения
10 сен 18, 15:23    [21669938]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
Владимир Затуливетер
Да, т.к. вероятность такого совпадения ооооочень мала.
Подход checksum + datalength оправдан только на большом кол-во строк, когда появляются дубликаты по checksum.
Вероятность появления дубликатов зависит от данных, а не от количества строк.
В моем примере как раз это и показано.
10 сен 18, 15:36    [21669959]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
invm
Вероятность появления дубликатов зависит от данных, а не от количества строк.

не понял, о чем это вы?

чем больше мы вставляем в таблицу значений рандомных, тем больше вероятность получить дупликат хеша для этих значений.
10 сен 18, 16:02    [21669986]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
Владимир Затуливетер
не понял, о чем это вы?

чем больше мы вставляем в таблицу значений рандомных, тем больше вероятность получить дупликат хеша для этих значений.
Видимо пример так и не запускали...
10 сен 18, 16:24    [21670009]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
Запустил сейчас и внимательно посмотрел.
Я так понимаю вы про нули в tag1?
Ну блин, так не пойдет, это синтетические, специально подобранные данные, в реальной жизни набор байт случайный.
10 сен 18, 16:33    [21670020]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
Владимир Затуливетер
Ну блин, так не пойдет, это синтетические, специально подобранные данные, в реальной жизни набор байт случайный.
Да ну? Вы в курсе какие реальные данные у ТС и насколько часто они будут давать дубликаты по хешу?
10 сен 18, 17:04    [21670052]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
Ну а вы в курсе видимо?

Давайте подождем автора, он нам расскажет.
10 сен 18, 17:12    [21670070]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
Владимир Затуливетер
Ну а вы в курсе видимо?
А мне не надо быть в курсе.
Я не строю решение на неподтвержденных предположениях и анализе вероятностей.
10 сен 18, 17:19    [21670082]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
Ну я про тоже, от того они и получаются более сложными чем это необходимо.

Я думаю смысла нет дальше продолжать нашу дисскусию, вроде бы понятна и моя и ваша точка зрения, понятны все плюсы и минусы предложенных решений.
У автора темы будет из чего выбрать.
Мы сюда пришли помогать людям, а не срач в темах разводить.
Ща уже модераторы проснуться и ругаться будут.
10 сен 18, 17:44    [21670121]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
Владимир Затуливетер
Ну я про тоже, от того они и получаются более сложными чем это необходимо.
Не зная как именно необходимо, невозможно эту необходимость оценивать. А вы именно этим и занимаетесь, гадая и основываясь на неверных посылках.
В частности, вы исходите из неверного понимания назначения хеша.
10 сен 18, 18:20    [21670157]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
ну почему тогда все возможные варианты не учтены у вас?
надо из реальных задач исходить, смысл решать несуществующие? я только это хочу сказать.
with t(n) as ( select 1 union all select n+1 from t where n < 1000 )
select d.val, checksum(val), datalength(val)
from t cross apply ( select cast(replicate(cast(n as varbinary(max)), 8000) as varbinary(max)) ) d(val)
option(maxrecursion 0)
10 сен 18, 19:03    [21670196]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
Страдалецъ
Member

Откуда: Мурманск
Сообщений: 1415
С интересом прочитал всю ветку, даже не ожидал столь бурной полемики по этому вопросу. Спасибо всем за подсказки и полезные советы. Собственно в базе хранится будут фотографии, в основном портретного формата. В процессе работы с подобными базами всегда сталкивался с необходимостью находить и удалять повторяющиеся изображения. Сейчас хочу эту ситуацию пресечь на корню.
10 сен 18, 19:44    [21670234]     Ответить | Цитировать Сообщить модератору
 Re: Уникальность значений в поле типа Image  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
Владимир Затуливетер
надо из реальных задач исходить, смысл решать несуществующие?
Вот именно. Особенно, если придумывать подходящие под собственные решения.

ЗЫ: Подумайте на досуге: для чего придумали хеш-таблицы.
10 сен 18, 19:45    [21670235]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить