DISTINCT KEYWORD for image data type column

Posted on 2011-03-08
Last Modified: 2012-06-27
how want to use distinct key word for image datatype in sql server
Question by:Kanigi
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3

Expert Comment

ID: 35068620
I would suggest calculating a checksum for the data, and select distinct on that. Maybe you can use a managed function to calculate the checksum?

Author Comment

ID: 35068628
What is meant by calculating a checksum?

Expert Comment

ID: 35068669
I thought of SHA1 or MD5 over the Image data.

In the below example MyManagedFucntion could simply return the checksum for the data in MyImageColumn.

SELECT MAX(idCol), dbo.MyManagedFucntion(MyImageColumn)  FROM myTable
GROUP BY dbo.MyManagedFucntion(MyImageColumn)

Open in new window


Expert Comment

ID: 35068679
Maybe this will explain better:
LVL 75

Accepted Solution

Anthony Perkins earned 500 total points
ID: 35069188
You have a couple of options:
1.  As alluded to previously, use the HASHBYTES() function to get a hash of the image.
2. Use the undocumented function fn_varbintohexstr as in:
SELECT DISTINCT master.dbo.fn_varbintohexstr(YourColumnName)
FROM YourTableName

Both options have the caveat that they will only consider the first 8000 bytes.

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question