Store images in SQL Server VARBINARY

by

Posted on by

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 > 2GB, combines NTFS storage with database integration)
  • Supports compression (ROW/PAGE compression) for reduced storage footprint

Step 1: Create a Table with VARBINARY Column

Basic Table (Standard VARBINARY Storage)

CREATE TABLE image_storage (
    image_id INT PRIMARY KEY IDENTITY(1,1), -- Auto-increment primary key
    image_name VARCHAR(100) NOT NULL,
    image_varbinary VARBINARY(MAX) NOT NULL, -- Use MAX for large images
    upload_time DATETIME DEFAULT GETDATE()
);

Advanced: Table with FILESTREAM (for >2GB Images)

-- Prerequisite: Enable FILESTREAM on SQL Server instance first
CREATE TABLE image_storage_filestream (
    image_id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE, -- Required for FILESTREAM
    image_name VARCHAR(100) NOT NULL,
    image_filestream VARBINARY(MAX) FILESTREAM NOT NULL,
    upload_time DATETIME DEFAULT GETDATE()
);

Step 2: Insert Images into SQL Server VARBINARY

Method 1: Using T-SQL (SSMS/Command Line)

-- Method A: Insert from local file (SQL Server 2012+)
INSERT INTO image_storage (image_name, image_varbinary)
VALUES (
    'product_photo.jpg',
    (SELECT * FROM OPENROWSET(BULK N'/path/to/product_photo.jpg', SINGLE_BLOB) AS ImageData)
);

-- Method B: Insert via parameter (for application integration)
DECLARE @image VARBINARY(MAX);
SELECT @image = BulkColumn FROM OPENROWSET(BULK N'/path/to/product_photo.jpg', SINGLE_BLOB) AS x;
INSERT INTO image_storage (image_name, image_varbinary) VALUES ('product_photo.jpg', @image);
COMMIT;

Note: Replace ‘/path/to/product_photo.jpg’ with the actual file path (use double backslashes for Windows paths: C:\\images\\product_photo.jpg)

Method 2: Using DBBlobEditor

Insert Single Image to SQL Server VARBINARY

  1. Connect to your SQL Server database in DBBlobEditor
  2. Navigate to the “image_storage” table in the target schema
  3. Click “+” button to add a record, and then Edit image stored in SQL Server VARBINARY field

Batch Insert Images to SQL Server VARBINARY

Step 3: Retrieve and Restore SQL Server VARBINARY to Image

Method 1: Using T-SQL to Export to File

-- Method A: Using xp_cmdshell (requires elevated privileges)
DECLARE @sql VARCHAR(8000), @image VARBINARY(MAX), @filepath VARCHAR(255) = 'C:\exported_photo.jpg';

SELECT @image = image_varbinary FROM image_storage WHERE image_id = 1;

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

-- Write binary data to file (via PowerShell)
SET @sql = 'Powershell -Command "[''System.IO.File'']::WriteAllBytes(''' + @filepath + ''', [System.Convert]::FromBase64String(''' + CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@image"))', 'VARCHAR(MAX)') + '''))"';
EXEC xp_cmdshell @sql;

-- Disable xp_cmdshell (best practice)
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

Method 2: DBBlobEditor Export

Retrieve and Restore Single Image

  1. Connect to SQL Server database in DBBlobEditor
  2. Query the “image_storage” table and locate the target image_varbinary column
  3. Double click to View image stored in SQL Server VARBINARY field
  4. Click menu “File” -> “Save” to save VARBINARY data as a local image file

Batch Export SQL Server VARBINARY to Images

SQL Server Specific Notes

  • Avoid using legacy IMAGE type (deprecated in SQL Server 2005+, replaced by VARBINARY(MAX))
  • Use FILESTREAM only for images > 2GB (overhead for smaller files)
  • Grant necessary permissions: INSERT/SELECT on tables, EXECUTE on xp_cmdshell (if using T-SQL export)
  • Enable PAGE compression on tables with VARBINARY(MAX) columns to reduce storage (ALTER TABLE image_storage REBUILD WITH (DATA_COMPRESSION = PAGE))
  • For high concurrency, use READ_COMMITTED_SNAPSHOT isolation level to avoid locks on VARBINARY columns

Related Guides