Storing a Binary File in a Database

  • eric.notheisen

    SSCommitted

    Points: 1633

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

  • Jeff Moden

    SSC Guru

    Points: 994663

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • laurens-1042919

    SSC Veteran

    Points: 201

    Why not use native T-SQL commands?

    DECLARE @DataBinary VARBINARY(MAX)

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

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

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

    SELECT @DataBinary

  • fregatepllada

    SSCommitted

    Points: 1648

    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

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

  • sanjarani

    SSC Veteran

    Points: 259

    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

    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: 182268

    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: 1648

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

  • eric.notheisen

    SSCommitted

    Points: 1633

    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: 994663

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Robert Sterbal

    SSChampion

    Points: 10967

    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

    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: 12227

    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

    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.

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

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