{"id":3577,"date":"2023-04-12T15:39:33","date_gmt":"2023-04-12T15:39:33","guid":{"rendered":"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/"},"modified":"2026-01-15T18:17:46","modified_gmt":"2026-01-15T18:17:46","slug":"sql-server-image-and-varbinary-data-types","status":"publish","type":"post","link":"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/","title":{"rendered":"What Are the SQL Server IMAGE and VARBINARY Data Types and When Should I Use Them?"},"content":{"rendered":"

The IMAGE data type<\/strong>\u00a0in SQL Server has been used to store the\u00a0image<\/strong>\u00a0files. Recently, Microsoft began suggesting using VARBINARY(MAX) instead of IMAGE <\/strong>for storing a large amount of data in a single column since IMAGE will be retired in a future version of MS\u00a0SQL\u00a0Server.<\/p>\n

Illustration<\/strong><\/p>\n

As always, I think the best way to understand something is via a working example. Here, lets imagine an employee database where, included with identification information, is a unique image of the individual. I would like to import images for each employee from a source location into a table and then be able to export the image for processing later.<\/p>\n

Azure Data Studio<\/strong><\/p>\n

For this exercise I chose to use Azure Data Studio<\/a> and an on-premises SQL Server database, but you could just as easy use SQL Server Management Studio<\/a> (SSMS).<\/p>\n

\"\"<\/p>\n

Not sure which tool to use? Review a comparison between the tools here<\/a>.<\/p>\n

For this exercise, I am assuming that you have some basic understanding of the use of Azure Data Studio (logging in and connecting to a database, executing queries, etc.) but if not, there is a helpful QuickStart guide<\/a>.<\/p>\n

Setup<\/strong><\/p>\n

There is a preliminary step I must do to make this example work. That is, the OLE Automation Procedures<\/em> option must be \u201cset and active\u201d on the SQL Server instance to be used for the image export action I\u2019ll use and the bulkadmin server role<\/a> (which grants bulk copy and other bulk operations) must be given to the user who will be importing and exporting images.<\/p>\n

The\u00a0SQL Server Ole Automation Procedures\u00a0option<\/strong><\/a> controls whether\u00a0OLE Automation\u00a0objects can be instantiated within Transact-SQL\u00a0batches. These are extended stored\u00a0procedures\u00a0that allow\u00a0SQL Server\u00a0users to execute functions external to\u00a0SQL Server\u00a0within the security context of\u00a0SQL Server.<\/p>\n

Here is the T-SQL script that grants those privileges (I am assigning the bulkadmin role to myself):<\/p>\n

\"\"<\/p>\n

Creating a Table<\/strong><\/p>\n

Now that I have the prerequisite work completed, I can go ahead and create a table that will hold the images. The table will have a \u201ctypical\u201d Employee ID (defined as an IDENTITY) and Name (NVARCHAR) but will also include two additional columns which will hold the name of the physical image file stored as the NVARCHAR data type and also the binary data of that image file<\/em> stored as the VARBINARY data type (previously, you might have used the IMAGE data type).<\/p>\n

Below is the simple T-SQL script I used to create the table:<\/p>\n

\"\"<\/p>\n

Sort your Images<\/strong><\/p>\n

In my little example here I am assuming that I have a list of employee names who have each provided a \u201cselfie\u201d image file saved in the jpg file format:<\/p>\n

\"\"<\/p>\n

I have saved the image files in the following local folder:<\/p>\n

\"\"<\/p>\n

What I want to do is store the actual provided image in<\/em> the database table as binary data (not a reference to a file location), along with the employee\u2019s name.<\/p>\n

Importing Images<\/strong><\/p>\n

Since I am going to want to load images as new employees are hired, I can use a handy stored procedure as shown below. The procedure (loosely based upon a concept offered online at msqltips.com<\/a>) inserts a record into our EmployeeDetails table converting and storing the physical image file as binary data using the OPENROWSET<\/a> SQL function:<\/p>\n

\"\"<\/p>\n

Once I have created the stored procedure, I can execute the following T-SQL query statements (one for each of our employees):<\/p>\n

\"\"\u00a0<\/strong><\/p>\n

Querying the Table<\/strong><\/p>\n

Now, if I query the table (using the following T-SQL select command) I will see that our EmployeeDetails table has been loaded with each Employee\u2019s ID, Name, the name of the picture or image (selfie) file and the image has been converted and stored as well:<\/p>\n

select * from EmployeeDetails<\/p>\n

\"\"\u00a0<\/strong><\/p>\n

Exporting Images<\/strong><\/p>\n

Now that I have the data successfully stored, there is no need to keep the physical source image files around since they have been saved in the database table as binary data. To use that data later, I need a second stored procedure that can \u201cconvert\u201d the binary data back to a physical image \u201con the fly\u201d so that it can be viewed.<\/p>\n

Below is a stored procedure that uses an ADO Stream Object to do that. The ADO Stream Object<\/a> is used to read, write, and manage a stream of binary data or text.<\/p>\n

\"\"<\/p>\n

This stored procedure will read binary data from the table, convert it back to a .jpg file and then save it to a desired folder. You can run the procedure using the\u00a0 following T-SQL command:<\/p>\n

exec dbo.usp_ExportEmployeeImage ‘Brad Pitt’,’C:MyPicturesOutput’,’BP.jpg’<\/p>\n

\"\"<\/p>\n

Viewer<\/strong><\/p>\n

Now that I have seen how to convert images to binary data, store them in and retrieve them from a SQL Server table all using T-SQL, it would be nice to be able to view the images using a simple table query, however that is not possible. Remember, the (image) data stored in VARBINARY columns is stored in binary format so when you select the data in AZURE or SSMS, it is not converted, it is displayed using the hexadecimal representation of the binary data (as shown earlier).<\/p>\n

There are third party image viewers available, such as YOHZ Software\u2019s SQL Image Viewer<\/a> which you can download and try for free. Once you install it (there is no complicated configuration required) you can connect to your SQL database and run a simple query:<\/p>\n

\"\"\u00a0<\/strong><\/p>\n

Although the above option is pretty slick, I always prefer to explore creating my own solution.<\/p>\n

Simple Alternative<\/strong><\/p>\n

As a simple alternative, I will use some open source Python code to create my own \u201cimage viewer\u201d. I have used pyodbc<\/a> a lot in the past, so I can again use it here to connect to the SQL database and, once I have connected, to run queries as well as execute the stored procedures created earlier. Below is my code:<\/p>\n

To connect, use this function:<\/p>\n

\"\"<\/p>\n

If I run the script, it generates the \u201cemployee_list.html\u201d file shown below (opened in a web browser):<\/p>\n

\"\"<\/strong><\/p>\n

Conclusion<\/strong><\/p>\n

This exercise investigated a variety of topics, such as moving from the IMAGE to VARBINARY data type and how to convert, store and retrieve images using OLE Automation\u00a0objects all by executing T-SQL statements in Azure Data Studio. Finally, I used some Python code to create a simple HTML page to view the data.<\/p>\n","protected":false},"excerpt":{"rendered":"

The IMAGE data type\u00a0in SQL Server has been used to store the\u00a0image\u00a0files. Recently, Microsoft began suggesting using VARBINARY(MAX) instead of IMAGE for storing a large amount of data in a single column since IMAGE will be retired in a future version of MS\u00a0SQL\u00a0Server. Illustration As always, I think the best way to understand something is…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[27],"tags":[32],"class_list":["post-3577","post","type-post","status-publish","format-standard","hentry","category-quebit-value","tag-sql"],"acf":[],"yoast_head":"\nWhat Are the SQL Server IMAGE and VARBINARY Data Types and When Should I Use Them? - QueBIT<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What Are the SQL Server IMAGE and VARBINARY Data Types and When Should I Use Them? - QueBIT\" \/>\n<meta property=\"og:description\" content=\"The IMAGE data type\u00a0in SQL Server has been used to store the\u00a0image\u00a0files. Recently, Microsoft began suggesting using VARBINARY(MAX) instead of IMAGE for storing a large amount of data in a single column since IMAGE will be retired in a future version of MS\u00a0SQL\u00a0Server. Illustration As always, I think the best way to understand something is…\" \/>\n<meta property=\"og:url\" content=\"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/\" \/>\n<meta property=\"og:site_name\" content=\"QueBIT\" \/>\n<meta property=\"article:published_time\" content=\"2023-04-12T15:39:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-01-15T18:17:46+00:00\" \/>\n<meta name=\"author\" content=\"agoddard\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"agoddard\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/sql-server-image-and-varbinary-data-types\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/sql-server-image-and-varbinary-data-types\\\/\"},\"author\":{\"name\":\"agoddard\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#\\\/schema\\\/person\\\/e52d72da0fd2f5f70d189343fe4f5084\"},\"headline\":\"What Are the SQL Server IMAGE and VARBINARY Data Types and When Should I Use Them?\",\"datePublished\":\"2023-04-12T15:39:33+00:00\",\"dateModified\":\"2026-01-15T18:17:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/sql-server-image-and-varbinary-data-types\\\/\"},\"wordCount\":1076,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/sql-server-image-and-varbinary-data-types\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/wp-content\\\/uploads\\\/2021\\\/05\\\/Q1-min.jpg\",\"keywords\":[\"SQL\"],\"articleSection\":[\"QueBIT Value\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/quebit.com\\\/askquebit\\\/sql-server-image-and-varbinary-data-types\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/sql-server-image-and-varbinary-data-types\\\/\",\"url\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/sql-server-image-and-varbinary-data-types\\\/\",\"name\":\"What Are the SQL Server IMAGE and VARBINARY Data Types and When Should I Use Them? - QueBIT\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/sql-server-image-and-varbinary-data-types\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/sql-server-image-and-varbinary-data-types\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/wp-content\\\/uploads\\\/2021\\\/05\\\/Q1-min.jpg\",\"datePublished\":\"2023-04-12T15:39:33+00:00\",\"dateModified\":\"2026-01-15T18:17:46+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#\\\/schema\\\/person\\\/e52d72da0fd2f5f70d189343fe4f5084\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/sql-server-image-and-varbinary-data-types\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/quebit.com\\\/askquebit\\\/sql-server-image-and-varbinary-data-types\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/sql-server-image-and-varbinary-data-types\\\/#primaryimage\",\"url\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/wp-content\\\/uploads\\\/2021\\\/05\\\/Q1-min.jpg\",\"contentUrl\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/wp-content\\\/uploads\\\/2021\\\/05\\\/Q1-min.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/sql-server-image-and-varbinary-data-types\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"What Are the SQL Server IMAGE and VARBINARY Data Types and When Should I Use Them?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#website\",\"url\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/\",\"name\":\"QueBIT\",\"description\":\"QueBIT\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#\\\/schema\\\/person\\\/e52d72da0fd2f5f70d189343fe4f5084\",\"name\":\"agoddard\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g\",\"caption\":\"agoddard\"},\"sameAs\":[\"https:\\\/\\\/quebit.com\\\/askquebit\"],\"url\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/author\\\/agoddard\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"What Are the SQL Server IMAGE and VARBINARY Data Types and When Should I Use Them? - QueBIT","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/","og_locale":"en_US","og_type":"article","og_title":"What Are the SQL Server IMAGE and VARBINARY Data Types and When Should I Use Them? - QueBIT","og_description":"The IMAGE data type\u00a0in SQL Server has been used to store the\u00a0image\u00a0files. Recently, Microsoft began suggesting using VARBINARY(MAX) instead of IMAGE for storing a large amount of data in a single column since IMAGE will be retired in a future version of MS\u00a0SQL\u00a0Server. Illustration As always, I think the best way to understand something is…","og_url":"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/","og_site_name":"QueBIT","article_published_time":"2023-04-12T15:39:33+00:00","article_modified_time":"2026-01-15T18:17:46+00:00","author":"agoddard","twitter_card":"summary_large_image","twitter_misc":{"Written by":"agoddard","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/#article","isPartOf":{"@id":"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/"},"author":{"name":"agoddard","@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084"},"headline":"What Are the SQL Server IMAGE and VARBINARY Data Types and When Should I Use Them?","datePublished":"2023-04-12T15:39:33+00:00","dateModified":"2026-01-15T18:17:46+00:00","mainEntityOfPage":{"@id":"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/"},"wordCount":1076,"commentCount":0,"image":{"@id":"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/#primaryimage"},"thumbnailUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2021\/05\/Q1-min.jpg","keywords":["SQL"],"articleSection":["QueBIT Value"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/","url":"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/","name":"What Are the SQL Server IMAGE and VARBINARY Data Types and When Should I Use Them? - QueBIT","isPartOf":{"@id":"https:\/\/quebit.com\/askquebit\/#website"},"primaryImageOfPage":{"@id":"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/#primaryimage"},"image":{"@id":"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/#primaryimage"},"thumbnailUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2021\/05\/Q1-min.jpg","datePublished":"2023-04-12T15:39:33+00:00","dateModified":"2026-01-15T18:17:46+00:00","author":{"@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084"},"breadcrumb":{"@id":"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/#primaryimage","url":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2021\/05\/Q1-min.jpg","contentUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2021\/05\/Q1-min.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/quebit.com\/askquebit\/sql-server-image-and-varbinary-data-types\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/quebit.com\/askquebit\/"},{"@type":"ListItem","position":2,"name":"What Are the SQL Server IMAGE and VARBINARY Data Types and When Should I Use Them?"}]},{"@type":"WebSite","@id":"https:\/\/quebit.com\/askquebit\/#website","url":"https:\/\/quebit.com\/askquebit\/","name":"QueBIT","description":"QueBIT","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/quebit.com\/askquebit\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084","name":"agoddard","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g","caption":"agoddard"},"sameAs":["https:\/\/quebit.com\/askquebit"],"url":"https:\/\/quebit.com\/askquebit\/author\/agoddard\/"}]}},"_links":{"self":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3577","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/comments?post=3577"}],"version-history":[{"count":1,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3577\/revisions"}],"predecessor-version":[{"id":4919,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3577\/revisions\/4919"}],"wp:attachment":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/media?parent=3577"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/categories?post=3577"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/tags?post=3577"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}