How to Store Text Reports in a Database [duplicate]

Question

I have dozens of text reports that come in every day and I need to store them. I cannot just zip them and put them on the hard drive because the users aren't sophisticated enough to figure that out. I need to be able to associate some data with the report so they can look up report ABC for any given date. These files range in size from 11K to 1GB. Mostly they are less than 3 MB and many are less than 1MB.

What is the best way to store these? The options I am looking at are using Filestream or storing it in a varchar(max) field or zipping it and storing it in a varchar(binary) then unzipping it to display. I am open to any other options as well. Space efficiency is a high priority.

Thank you for reading this. I would truly appreciate any help or advice.

** The other post is very interesting and I learned a lot from it - thank you for referencing it. This is different from the other post in that my files are plain text files -- they are not binary files.

Answer 1

Okay, I think that I'm beginning to understand the nuance of your question.

Given that space efficiency is a high priority, I think that you can strike the varchar(max) option from your list. Since your minimum file size is 11K, nothing is going to fit on a single page. All the files will be stored as BLOBs. SQL Server won't compress that (aside from compressing the backup), so you're not going to save any space with that approach.

Next, I'm going to make the assumption that processing time can be traded for space efficiency. In that case, I would recommend that you compress the files yourself before storing them and decompress before displaying them. This goes for any solution you end up going with. You should end up with better compression than letting the file system handle it (which applies if you use a Filestream).

So I think that your question boils down to Filestream (and possibly FileTable) or VARBINARY(MAX). The standard answer provided by Microsoft is:

When to Use FILESTREAM

In SQL Server, BLOBs can be standard varbinary(max) data that stores the data in tables, or FILESTREAM varbinary(max) objects that store the data in the file system. The size and use of the data determines whether you should use database storage or file system storage. If the following conditions are true, you should consider using FILESTREAM:

• Objects that are being stored are, on average, larger than 1 MB.
• Fast read access is important.
• You are developing applications that use a middle tier for application logic.

For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.

I think that you will need to reassess your figures using compressed file sizes, and then make a decision based off of that.