Comparing Images

Question

Comparing Images

Steve Jones - SSC Editor

SSC Guru

Points: 736478
More actions
November 11, 2024 at 12:00 am

#4472254

Comments posted to this topic are about the item Comparing Images

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Thomas Franz Hall of Fame Points: 3897 More actions · Answer 1

when I cast the values as VARBINARY(MAX) it works too.

TRUNCATE TABLE dbo.Image_Staging; TRUNCATE TABLE dbo.Images
INSERT INTO  dbo.Images (imageid, imagestatus, imagemodified, imagebinary) VALUES (1, 1, GETDATE(), HASHBYTES('SHA2_512', CAST(NEWID()  AS VARCHAR(36))))
INSERT INTO  dbo.Images (imageid, imagestatus, imagemodified, imagebinary) VALUES (2, 1, GETDATE(), HASHBYTES('SHA2_512', CAST(NEWID()  AS VARCHAR(36))))
INSERT INTO  dbo.Images (imageid, imagestatus, imagemodified, imagebinary) VALUES (3, 1, GETDATE(), HASHBYTES('SHA2_512', CAST(NEWID()  AS VARCHAR(36))))

INSERT INTO dbo.Image_Staging (imageid, imagestatus, imagebinary) SELECT i.imageid, i.imagestatus, i.imagebinary  FROM dbo.Images AS i WHERE i.imageid IN (1, 3)
INSERT INTO dbo.Image_Staging (imageid, imagestatus, imagebinary) VALUES (5, 1, HASHBYTES('SHA2_512', CAST(NEWID() AS VARCHAR(36))))

SELECT * FROM dbo.Images AS i
SELECT * FROM dbo.Image_Staging AS ist

SELECT i.imageid
FROM
  dbo.Image_Staging AS ist
  INNER JOIN dbo.Images AS i
    ON CAST(i.imagebinary AS VARBINARY(max)) = CAST(ist.imagebinary AS VARBINARY(max));

returns the id 1 and 3 (as expected). Same when I cast both to VARBINARY(64) / BINARY(64), since this is the lenght of the inserted SHA-512-Hash. Or one to BINARY(64) and the other to VARBINARY(MAX)

So answer 4 is correct too (except you say it is wrong, because it justs need to be a fitting BINARY datatype and not exactly the same one).

PS: instead of comparing the whole data type (which could be a 50 MB TIFF file) you should better store a hash value (MD5, SHA-256, SHA-512) in the database (in both tables) and use them for a join. Best way would be a persisted (or indexed) computed column.

This reply was modified 1 month, 1 week ago by Thomas Franz.

God is real, unless declared integer.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

The image data type should be avoided, no real benefit over modern binary data types.

😎

The image data type can only be converted to binary, varbinary and interestingly enough timestamp. The other way around, only binary, varbinary, char, varchar and timestamp can be converted into the image data type.

I have come across legacy systems where the image data type was used as the old ODBC drivers didn't support the modern binary data types, a bit of a PITA (excuse the pun) when upgrading the database parts of those systems.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

Awarded back points and changed the 4th answer. That isn't a good choice.