How To Compare Image Data Types In SQL Server

Question

Hi Guys,

I'm having a lot of trouble writing a select statement to compare two values in t-sql which are of type image. This is what I have so far:

SELECT a.UserId, b.UserId
FROM [dbo].[aspnet_Profile] as a inner join [dbo].[aspnet_Profile] as b on
    (a.UserId = <some string> AND b.UserId = <some other string>)
WHERE BINARY_CHECKSUM(a.PropertyValuesBinary) = BINARY_CHECKSUM(b.PropertyValuesBinary)

But I keep getting error messages no matter what I do to the WHERE clause. For the above query, the error message I get is:

Error in binarychecksum. There are no comparable columns in the binarychecksum input.

At any rate, any help would be very much appreciated. I'm have a very hard time doing anything with this data-type, for some reason...

BTW: I'm using SQL Server Web (I think it's the 2008 edition)...

Thanks!

Andrew

Answer 1

Can you do this?

   select BINARY_CHECKSUM(A.PropertyValuesBinary) from aspnet_Profile as A

Knowing the #s of the error messages would help us help you.

Answer 2

You can't use binarychecksum on image types

BINARY_CHECKSUM ignores columns of noncomparable data types in its computation. Noncomparable data types include text, ntext, image, cursor, xml, and noncomparable common language runtime (CLR) user-defined types.

http://msdn.microsoft.com/en-us/library/ms173784.aspx

It might be easier to store a MD5 has along with the image. I can't imagine that you're getting good performance by requesting a binary_checksum of all of your images!

Answer 3

Take a look here