The image data type in SQL Server is frequently used to store variable length binary data. It does not have to store only ‘images’, though most people associate that data type with images.
You may occasionally need to view the type of data that’s stored, but SQL Server Management Studio can only display the binary representation of the data (we’re using the AdventureWorks sample database here).
SQL Image Viewer is one tool that can display the image directly in the result set.
Interesting points to note:
- the image format was never provided. SQL Image Viewer can read the binary data and determine the image format itself. It recognizes png, gif, jpg, tiff, bmp, wmf, emf, dicom, and raw camera formats.
- details of the image like size and resolution are provided automatically
Though its name might suggest otherwise, SQL Image Viewer doesn’t just support ‘images’. It recognizes a wide variety of file formats, including Microsoft Office documents, OpenOffice documents, PDF, epub, multimedia files (avi, wav, mp3, mp4, svg, swf, flv, mkv, webm, m4v, mov, 3gp), and compressed archives (zip, 7zip, rar, bzip2, gzip).
For PDFs, SQL Image Viewer is able to display a thumbnail of the first page, and also the number of pages in the file.
Once retrieved, you can export the binary data. You can export them to a spreadsheet:
or simply export them as individual files:
You can name the files using values from other columns. You can also convert the exported images to other formats:
For images that contain EXIF values, you can view these values using the internal viewer:
SQL Image Viewer supports Oracle, PostgreSQL, MySQL, MariaDB, Microsoft Access, SQLite and ODBC supported databases.
Thus, you can easily view the data that’s stored in
- binary and varbinary columns in Oracle databases
- bytea columns in PostgreSQL databases
- binary and varbinary columns in MySQL and MariaDB databases
- binary and varbinary columns in Microsoft Access databases
- OLE-Object columns in Microsoft Access databases