SQL Server: Storing Images and Other BLOB types

In this post, we will see how to store BLOB (Binary Large Objects) such as files or images in SQL Server. In versions prior to SQL Server 2005, the image datatype was used to store files or images in a table. From SQL Server 2005 and later versions, the image datatype is replaced with varbinary(max) datatype.

To make it easier to demonstrate, I will show how to save a text file in SQL Server.

Create a text file named ‘test.txt’ in your drive with the following data

1,500
2,1000
3,834
4,578
5,290

Create a table variable with varbinary(max) datatype and insert this text file into the table. We have two options

Option 1 : single_blob

Here’s the same query for you to try out:

declare @file table(file_path varchar(150), file_storage varbinary(max))
insert into @file (file_path, file_storage)
select 'f:\test.txt' ,
* from openrowset(bulk n'f:\test.txt', single_blob) as document
select * from @file

The openrowset function with the bulk option will convert the entire file content to binary values. The option SINGLE_BLOB is used to store the file content as a single row and return a single column rowset.

Note: In order to retrieve the file content, FileStream method should be used in your front end application

Option 2 : single_clob

Here’s the same query for you to try out:

declare @file table(file_path varchar(150), file_storage varchar(max))
insert into @file (file_path, file_storage)
select 'f:\test.txt' ,
* from openrowset(bulk 'f:\test.txt', single_clob) as document
select * from @file
GO

Note that the datatype of the column file_storage is varchar. single_clob option is used to store the file content as characters.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan