{"id":27536,"date":"2025-04-25T03:47:36","date_gmt":"2025-04-25T03:47:36","guid":{"rendered":"http:\/\/www.withdata.com\/blog\/?p=27536"},"modified":"2026-03-04T05:22:03","modified_gmt":"2026-03-04T05:22:03","slug":"store-images-in-sql-server-varbinary","status":"publish","type":"post","link":"https:\/\/www.withdata.com\/blog\/blob\/store-images-in-sql-server-varbinary.html","title":{"rendered":"Store images in SQL Server VARBINARY"},"content":{"rendered":"<h2>SQL Server VARBINARY Type Features<\/h2>\n<ul>\n<li>Variable-length binary data type (replaces legacy IMAGE type in SQL Server 2005+)<\/li>\n<li>VARBINARY(n): Stores up to n bytes (n = 1 to 8000) for small\/medium images<\/li>\n<li>VARBINARY(MAX): Stores up to 2GB per value (ideal for high-resolution images)<\/li>\n<li>Integrated with FILESTREAM (for ultra-large files &gt; 2GB, combines NTFS storage with database integration)<\/li>\n<li>Supports compression (ROW\/PAGE compression) for reduced storage footprint<\/li>\n<\/ul>\n<h2>Step 1: Create a Table with VARBINARY Column<\/h2>\n<h3>Basic Table (Standard VARBINARY Storage)<\/h3>\n<pre>CREATE TABLE image_storage (\r\n    image_id INT PRIMARY KEY IDENTITY(1,1), -- Auto-increment primary key\r\n    image_name VARCHAR(100) NOT NULL,\r\n    image_varbinary VARBINARY(MAX) NOT NULL, -- Use MAX for large images\r\n    upload_time DATETIME DEFAULT GETDATE()\r\n);\r\n<\/pre>\n<h3>Advanced: Table with FILESTREAM (for &gt;2GB Images)<\/h3>\n<pre>-- Prerequisite: Enable FILESTREAM on SQL Server instance first\r\nCREATE TABLE image_storage_filestream (\r\n    image_id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE, -- Required for FILESTREAM\r\n    image_name VARCHAR(100) NOT NULL,\r\n    image_filestream VARBINARY(MAX) FILESTREAM NOT NULL,\r\n    upload_time DATETIME DEFAULT GETDATE()\r\n);\r\n<\/pre>\n<h2>Step 2: Insert Images into SQL Server VARBINARY<\/h2>\n<h3>Method 1: Using T-SQL (SSMS\/Command Line)<\/h3>\n<pre>-- Method A: Insert from local file (SQL Server 2012+)\r\nINSERT INTO image_storage (image_name, image_varbinary)\r\nVALUES (\r\n    'product_photo.jpg',\r\n    (SELECT * FROM OPENROWSET(BULK N'\/path\/to\/product_photo.jpg', SINGLE_BLOB) AS ImageData)\r\n);\r\n\r\n-- Method B: Insert via parameter (for application integration)\r\nDECLARE @image VARBINARY(MAX);\r\nSELECT @image = BulkColumn FROM OPENROWSET(BULK N'\/path\/to\/product_photo.jpg', SINGLE_BLOB) AS x;\r\nINSERT INTO image_storage (image_name, image_varbinary) VALUES ('product_photo.jpg', @image);\r\nCOMMIT;\r\n<\/pre>\n<p><strong>Note:<\/strong> Replace &#8216;\/path\/to\/product_photo.jpg&#8217; with the actual file path (use double backslashes for Windows paths: C:\\\\images\\\\product_photo.jpg)<\/p>\n<h3>Method 2: Using DBBlobEditor<\/h3>\n<h4>Insert Single Image to\u00a0SQL Server VARBINARY<\/h4>\n<ol>\n<li>Connect to your SQL Server database in <a href=\"\/dbblobeditor\/\">DBBlobEditor<\/a><\/li>\n<li>Navigate to the \u201cimage_storage\u201d table in the target schema<\/li>\n<li>Click \u201c+\u201d button to add a record, and then <a href=\"\/blog\/sqllobeditor\/edit-sql-server-image-data.html\">Edit image stored in SQL Server VARBINARY field<\/a><\/li>\n<\/ol>\n<h4>Batch Insert Images to\u00a0SQL Server VARBINARY<\/h4>\n<ul>\n<li><a href=\"\/blog\/sqllobeditor\/import-images-to-sql-server-image-varbinarymax.html\">Batch import image files into SQL Server VARBINARY<\/a><\/li>\n<\/ul>\n<h2>Step 3: Retrieve and Restore SQL Server VARBINARY to Image<\/h2>\n<h3>Method 1: Using T-SQL to Export to File<\/h3>\n<pre>-- Method A: Using xp_cmdshell (requires elevated privileges)\r\nDECLARE @sql VARCHAR(8000), @image VARBINARY(MAX), @filepath VARCHAR(255) = 'C:\\exported_photo.jpg';\r\n\r\nSELECT @image = image_varbinary FROM image_storage WHERE image_id = 1;\r\n\r\nEXEC sp_configure 'show advanced options', 1;\r\nRECONFIGURE;\r\nEXEC sp_configure 'xp_cmdshell', 1;\r\nRECONFIGURE;\r\n\r\n-- Write binary data to file (via PowerShell)\r\nSET @sql = 'Powershell -Command \"[''System.IO.File'']::WriteAllBytes(''' + @filepath + ''', [System.Convert]::FromBase64String(''' + CAST(N'' AS XML).value('xs:base64Binary(sql:variable(\"@image\"))', 'VARCHAR(MAX)') + '''))\"';\r\nEXEC xp_cmdshell @sql;\r\n\r\n-- Disable xp_cmdshell (best practice)\r\nEXEC sp_configure 'xp_cmdshell', 0;\r\nRECONFIGURE;\r\nEXEC sp_configure 'show advanced options', 0;\r\nRECONFIGURE;\r\n<\/pre>\n<h3>Method 2: DBBlobEditor Export<\/h3>\n<h4>Retrieve and Restore Single Image<\/h4>\n<ol>\n<li>Connect to SQL Server database in DBBlobEditor<\/li>\n<li>Query the \u201cimage_storage\u201d table and locate the target image_varbinary column<\/li>\n<li>Double click to <a href=\"\/blog\/sqllobeditor\/edit-sql-server-image-data.html\">View image stored in SQL Server VARBINARY field<\/a><\/li>\n<li>Click menu \u201cFile\u201d -&gt; \u201cSave\u201d to save VARBINARY data as a local image file<\/li>\n<\/ol>\n<h4>Batch Export SQL Server VARBINARY to Images<\/h4>\n<ul>\n<li><a href=\"\/blog\/sqllobeditor\/export-images-from-sql-server-to-files.html\">Export images from SQL Server to files<\/a><\/li>\n<\/ul>\n<h2>SQL Server Specific Notes<\/h2>\n<ul>\n<li>Avoid using legacy <strong>IMAGE<\/strong> type (deprecated in SQL Server 2005+, replaced by VARBINARY(MAX))<\/li>\n<li>Use FILESTREAM only for images &gt; 2GB (overhead for smaller files)<\/li>\n<li>Grant necessary permissions: INSERT\/SELECT on tables, EXECUTE on xp_cmdshell (if using T-SQL export)<\/li>\n<li>Enable PAGE compression on tables with VARBINARY(MAX) columns to reduce storage (ALTER TABLE image_storage REBUILD WITH (DATA_COMPRESSION = PAGE))<\/li>\n<li>For high concurrency, use READ_COMMITTED_SNAPSHOT isolation level to avoid locks on VARBINARY columns<\/li>\n<\/ul>\n<hr \/>\n<h3>Related Guides<\/h3>\n<ul>\n<li><a href=\"\/blog\/blob\/images-in-blob.html\">Store images in BLOB: A Comprehensive Guide<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-db2-blob.html\">Store images in DB2 BLOB<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-oracle-blob.html\">Store images in Oracle BLOB<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-mysql-blob.html\">Store images in MySQL BLOB<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-postgresql-bytea.html\">Store images in PostgreSQL BYTEA<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-sqlite-blob.html\">Store images in SQLite BLOB<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server VARBINARY Type Features Variable-length binary data type (replaces legacy IMAGE type in SQL Server 2005+) VARBINARY(n): Stores up to n bytes (n = 1 to 8000) for small\/medium images VARBINARY(MAX): Stores up to 2GB per value (ideal for high-resolution images) Integrated with FILESTREAM (for ultra-large files &gt; 2GB, combines NTFS storage with database [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4524],"tags":[97,2065,4494,1321,96],"class_list":["post-27536","post","type-post","status-publish","format-standard","hentry","category-blob","tag-blob","tag-image","tag-photo","tag-sql-server","tag-varbinary"],"_links":{"self":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/27536","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/comments?post=27536"}],"version-history":[{"count":6,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/27536\/revisions"}],"predecessor-version":[{"id":45322,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/27536\/revisions\/45322"}],"wp:attachment":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/media?parent=27536"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/categories?post=27536"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/tags?post=27536"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}