Converting SQL Server Binary Image Data to HTML Image URL

 

When working with images stored as binary data in SQL Server, you might need to display them in a web application. A convenient way to do this is by converting the binary data into a Base64-encoded string and using it as a data URL in HTML. Best suited for small thumbnail photos or icons.

Storing Images in SQL Server

Images are often stored in SQL Server as VARBINARY(MAX) columns. For example, a table structure for storing user photos might look like this:

CREATE TABLE UserPhotos (
    UserID INT PRIMARY KEY,
    PhotoThumbnail VARBINARY(MAX)
);

Converting the Binary Data to a Base64 Image URL

SQL Server doesn’t provide built-in functions to encode binary data as Base64, but we can achieve this using FOR XML PATH combined with BINARY BASE64. Below is a query to transform the binary image data into a Base64 string formatted as a data URL:

SELECT
    CASE
        WHEN p.PhotoThumbnail IS NOT NULL
        THEN 'data:image/png;base64,' +
            REPLACE(
                REPLACE(
                    (SELECT p.PhotoThumbnail FOR XML PATH(''), BINARY BASE64),
                    '<PhotoThumbnail>', ''
                ),
                '</PhotoThumbnail>', ''
            )
        ELSE NULL
    END AS PhotoURL
FROM UserPhotos p;

Explanation:

• FOR XML PATH('') is used to convert the binary data to a Base64 string.

• BINARY BASE64 ensures the binary data is properly encoded.

• REPLACE(..., '<PhotoThumbnail>', '') removes the XML tags from the output.

• The result is prefixed with data:image/png;base64, to create a valid data URL.

Using the Base64 String in HTML

Once you have the Base64-encoded image, you can use it directly in an <img> tag:

<img src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAA..." alt="User Photo">

Replace the src value with the actual Base64 string returned from the SQL query.

Considerations

• Performance: Storing and retrieving large binary data in SQL Server can impact performance. Consider using external storage (e.g., Azure Blob Storage, AWS S3) and storing image URLs in the database.

• Size Limitation: Base64 encoding increases the size of the image data by approximately 33%.

• Compatibility: Some browsers may limit the size of data URLs that can be used in src attributes.

By using this approach, you can dynamically retrieve and display images stored in SQL Server without needing to save them as separate files on a web server.