How to store images in SQL Server?

We can store images in SQL Server using a few different data types and storage methods. Here we will go through a couple of common methods to store images like using VARBINARY(MAX) datatype, FILESTREAM data type and FileTable.

A. Using VARBINARY(MAX) datatype

You can use VARBINARY(MAX) datatype for storing binary data, including images, of up to 2 GB in size. Here is an example on how to use the datatype to store images.

/* Create a table. */
CREATE TABLE MTB_Images (
    ImageID INT PRIMARY KEY,
    ImageData VARBINARY(MAX)
)

/* Read the file image and insert it to the as a BLOB  */
INSERT INTO MTB_Images
    (ImageID, ImageData)
	SELECT 1, BulkColumn
		FROM OPENROWSET(BULK 'C:\Path_To\Your_Image.jpg', SINGLE_BLOB) AS ImageData

SELECT * FROM MTB_Images

NOTE: Though VARBINARY(MAX) can store up to 2 GB size files, it is recommended to store images or documents which are below 256 KB using this data type for performance.

B. Using FILESTREAM datatype

Another method of storing images in SQL Server databases is by using the FILESTREAM datatype. Using this method you can store large binary data, such as images, in the file system while maintaining references in the database. Follow the below steps to store images using FILESTREAM.

1. To start with, enable FILESTREAM for your SQL Server instance through SQL Server Configuration Manager.
2. Then, create a FILESTREAM filegroup on a specific location. Below is the sample script. (Change the database name ‘MyTecBitsTestDB’ to your own, and folder name ‘E:\Program Files\Databases\MSSQL_FileStream’ to your desired location)

USE master;
GO

ALTER DATABASE MyTecBitsTestDB
ADD FILEGROUP FileStreamGroup CONTAINS FILESTREAM;

ALTER DATABASE MyTecBitsTestDB
ADD FILE (NAME = 'FileStreamData', FILENAME = 'E:\Program Files\Databases\MSSQL_FileStream')
TO FILEGROUP FileStreamGroup;
GO

3. Now, create a table with a FILESTREAM column to store the images.

USE MyTecBitsTestDB;
GO

CREATE TABLE MTB_Images_FileStream
(
    ImageID uniqueidentifier not null ROWGUIDCOL unique default newid(),
    ImageStream VARBINARY(MAX) FILESTREAM
)
GO

4. Finally, insert an image into this table.

INSERT INTO MTB_Images_FileStream
    (ImageStream)
    SELECT * FROM
        OPENROWSET(BULK N'E:\Beaulin-Temp\Image\Weighing-Scale.jpg' ,SINGLE_BLOB) AS Img
GO

5. Run the select statement to see the details stored in the table.

SELECT * FROM MTB_Images_FileStream
GO

6. If you see the image file inside the suborders of the filestream folder, it will be like the screenshot below. Open the file with paint to see the image.

C. Store images using FileTable

Another method of storing images in SQL Server database is using the FileTable feature. It combines the benefits of storing files in the Windows file system with the capability to access them using Transact-SQL. Follow the below steps to store images using the FileTable method:

1. Make sure, FILESTREAM is enabled in your SQL Server instance.
2. Then, create a database with FILESTREAM support.

USE master;
GO

CREATE DATABASE MTB_FilesDB
   ON PRIMARY
   ( NAME = MTB_FilesDB,
      FILENAME = 'E:\Program Files\Databases\MTB_FilesDB.mdf' ),
   FILEGROUP FileStreamGroup CONTAINS FILESTREAM
   ( NAME = FilestreamData,
      FILENAME = 'E:\Program Files\Databases\FilesTableData' )
    WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FilesTableData' );
GO

3. Now create a FileTable in the database.

USE MTB_FilesDB;

CREATE TABLE ImagesFileTable AS FileTable;
GO

4. Now you can insert images to this table. There are a couple of ways to insert images or other files to this table. Either by just copy pasting or by using an INSERT statement. Let’s see the copy-paste method.
5. To copy paste images or files, go to Object explorer, Expand the database, Expand the FileTables folder and right-click on the table you have just created. From the right-click menu press Explore FileTable Directory.
6. This will open the directory in file explorer. You can just copy paste your files to this folder.

7. On copy pasting, the details of the files are automatically added to the table.

SELECT * FROM ImagesFileTable
GO

Reference

• Read more about FileTable at Microsoft Docs.
• More about FILESTREAM at Microsoft Docs.