How To Determine Whether A Record With Specific Image Data Type Already Exists In The Table?

What is the most effective way to do that? I'm looking for a stored procedure, with returns me a new ID or an ID of the record with that image. Image could be up to 15-20MB, but mo

Solution 1:

most effective way

The most effective way I can think of is to use a persisted computed column for a hash value of the image column. Use hashbytes to calculate the hash and add a unique constraint on the computed column.

Table definition:

createtable Images
(
  ID intidentityprimary key, 
  Img varbinary(max),
  ImgHash asconvert(varbinary(16), hashbytes('MD5', Img)) persisted unique
)

Sample code against Images table:

insertinto Images values 
(convert(varbinary(max), 'Image1')),
(convert(varbinary(max), 'Image2'))

declare@NewImagevarbinary(max) =convert(varbinary(max), 'Image2')

selectcount(*)
from Images
where ImgHash = hashbytes('MD5', @NewImage)

The unique constraint creates an index that will be used in the query.

Your SP to add an image could look like this using merge and output with a trick from this answer UPDATE-no-op in SQL MERGE statement provided by Andriy M.

createprocedure Images_Add
  @NewImagevarbinary(max)
asdeclare@dummyintmerge Images as T
using (select@NewImage, hashbytes('MD5', @NewImage)) as S(Img, ImgHash)
on T.ImgHash = S.ImgHash
whennot matched theninsert(Img) values(S.Img)
when matched thenupdateset@dummy=0  
output inserted.ID;