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
- Connect to your SQL Server database in DBBlobEditor
- Navigate to the “image_storage” table in the target schema
- 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
- Connect to SQL Server database in DBBlobEditor
- Query the “image_storage” table and locate the target image_varbinary column
- Double click to View image stored in SQL Server VARBINARY field
- 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