Comparing Images

  • Comments posted to this topic are about the item Comparing Images

  • 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.

    God is real, unless declared integer.

  • 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.

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

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

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