Storing a Binary File in a Database

Question

Post reply

Storing a Binary File in a Database

eric.notheisen

SSCommitted

Points: 1643
More actions
May 25, 2015 at 11:28 pm

#304281

Comments posted to this topic are about the item Storing a Binary File in a Database

Viewing 15 posts - 1 through 15 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 1

Nice article but I guess I just don't understand why people think they need to write a CLR for this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

laurens-1042919 SSC Veteran Points: 201 More actions · Answer 2

Why not use native T-SQL commands?

DECLARE @DataBinary VARBINARY(MAX)

DECLARE @DocumentFullPath VARCHAR(255) = 'd:\image.jpg'

DECLARE @SqlStringNVARCHAR(4000) = 'SELECT @DataBinary = DATA FROM OPENROWSET(BULK ''' + @DocumentFullPath + ''', SINGLE_BLOB) AS RESULT(DATA)'

EXECUTEsp_executesql @Sqlstring, N'@DataBinary VARBINARY(MAX) OUTPUT', @DataBinary = @DataBinary OUTPUT

SELECT @DataBinary

fregatepllada SSCommitted Points: 1700 More actions · Answer 3

Do you know that there is an option to store files in SQL Server via SQL Server FILESTREAM technology since SQL 2008?

mwpowellhtx Old Hand Points: 356 More actions · Answer 4

Ditto using filestrstream. Especially as it broadens support for larger files.

sanjarani SSC Veteran Points: 268 More actions · Answer 5

I used .dll program developed with C# a few years a go (SQL Server 2005) to store binary files, mainly .jpg file.

At the time it was said that a special permission was necessary to consume .dll in SQL Server and those permissions would compromise the security of the Server.

Is consuming .dll in SQL Server still a security concern?

mwpowellhtx Old Hand Points: 356 More actions · Answer 6

I've done that recently as well and I would suggest probably yes it does pose a security risk. At least put controls around the continuous delivery path.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 7

Thanks for taking the time to write the article. Given the alternatives I strongly prefer employing a SQLCLR for implementing file system access from T-SQL. However, in this case a custom solution for handling binaries may not be necessary. I see this as a great use case for FileTables. Unlike the C# solution in the article, the native T-SQL solution alluded to and using FILESTREAM directly, no coding is required to bring a binary into the database when using FileTables. FileTables leverage FILESTREAM but with the added advantage of offering us the option of managing files in the database via a UNC path accessible through Windows Explorer or other standard file-system tools.

FileTables

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

fregatepllada SSCommitted Points: 1700 More actions · Answer 8

FileTable does not support transactions, while Filestream does, plus in Filestream you have a choice to hide files from Windows 🙂

eric.notheisen SSCommitted Points: 1643 More actions · Answer 9

Several commenters recommended using the FILESTREAM option rather than use a CLR. The FILESTREAM option stores the BLOB on the file system itself which defeats the purpose of storing the file as a BLOB in the database.

From: https://technet.microsoft.com/en-us/library/bb933993(v=sql.105).aspx

FILESTREAM storage is implemented as a varbinary(max) column in which the data is stored as BLOBs in the file system. The sizes of the BLOBs are limited only by the volume size of the file system. The standard varbinary(max) limitation of 2-GB file sizes does not apply to BLOBs that are stored in the file system.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 10

For those cases where you're not going to violate the 2GB limit, you don't even need FileStream to load binaries.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Robert Sterbal SSChampion Points: 11047 More actions · Answer 11

I'm not sure this happened in this case, but small organizations often pay for the developers to construct things in a technically sophisticated way, when there are are other ways of solving the problem.

There aren't enough metrics here to determine if this kind of heavy lifting is appropriate for the situation.

412-977-3526 call/text

mwpowellhtx Old Hand Points: 356 More actions · Answer 12

Orlando Colamatteo (5/26/2015)
Thanks for taking the time to write the article. Given the alternatives I strongly prefer employing a SQLCLR for implementing file system access from T-SQL. However, in this case a custom solution for handling binaries may not be necessary. I see this as a great use case for FileTables. Unlike the C# solution in the article, the native T-SQL solution alluded to and using FILESTREAM directly, no coding is required to bring a binary into the database when using FileTables. FileTables leverage FILESTREAM but with the added advantage of offering us the option of managing files in the database via a UNC path accessible through Windows Explorer or other standard file-system tools.
FileTables

I'm not sure you understand FILESTREAM correctly. I may not either, but as I understand it, the OP's API would still be necessary. The only difference being, FILESTREAM permits large files, stored in the file system to be sure, transparently through the table. They still look and feel like a varbinary(max), but to be sure, the files are landing in a predetermined location. They are also very transactional in nature; that's one of the benefits of FILESTREAM; as contrasted with a RYO file tabling / streaming strategy.

sknox SSChampion Points: 12388 More actions · Answer 13

eric.notheisen (5/26/2015)
Several commenters recommended using the FILESTREAM option rather than use a CLR. The FILESTREAM option stores the BLOB on the file system itself which defeats the purpose of storing the file as a BLOB in the database.
From: https://technet.microsoft.com/en-us/library/bb933993(v=sql.105).aspx
FILESTREAM storage is implemented as a varbinary(max) column in which the data is stored as BLOBs in the file system. The sizes of the BLOBs are limited only by the volume size of the file system. The standard varbinary(max) limitation of 2-GB file sizes does not apply to BLOBs that are stored in the file system.

What is the purpose of storing the file as a BLOB in the database?

From your article:

Among the many reasons given for using a database to store images or files is the ability to search for descriptions and other details that may apply to an image. Another reason is that files stored on a file server may become corrupted.

Since FILESTREAM exposes the file as if it were a column in a table, it satisfies the reasons in the first sentence.

Since FILESTREAM data is not stored on a file server, but in a dedicated location on the SQL Server, which you can secure as needed, it satisfies the second reason.

What need does this solution satisfy that FILESTREAM cannot?

mwpowellhtx Old Hand Points: 356 More actions · Answer 14

Since FILESTREAM data is not stored on a file server, but in a dedicated location on the SQL Server, which you can secure as needed, it satisfies the second reason.

Not stored in a way that is useful apart from the BLOB interface, at any rate, without additional operations to extract it or what have you. The key selling point with FILESTREAM in my opinion is transactional / relational integrity.