I need to develop a production system capable of storing a 100 kb textfile and a serial number for every piece produced. I know the approximate output over 2 years, the time the data has to be stored, and have calculated a need of around 25-30 TB of storage.

I have some experience working with databases, but handling that amount of data is new to me.

My first guess would be to simply store the files as a blob inside a table in the database. What are the problems of storing that amount of data in a single table? What are possible backup scenarios?

Another approach would be to store the data in several tables (e.g. a table per month).

A third approach would be to store references to the data in SQL Server, zip the files, and store them somewhere else.

The purpose of this storage system is to query the text file by using the serial number.

What would you do?

  • 5
    "query the textfile" -- do you mean search inside it, or just return it? If all you need is essentially a key/value store, maybe think about using FILESTREAM. Also, it's possible that SQL Server may not be the best solution for this problem. – Jon Seigel Oct 31 '12 at 16:33
  • 3
    some text files shrink to 1-2% when zipped. Have you tried to compress? – A-K Oct 31 '12 at 17:14

Historically, I'd shy away from storing files in the database and instead include pointers like in your alternative 3. With SQL Server 2012, however, you might look into the FileTable feature. The files are still stored within SQL Server (meaning that you'd have to be aware of the implications on your backup strategy), but also are easily accessible through the standard Windows API so you can manipulate them in Windows Explorer or PowerShell. If you actually need to search within the text files as well, FileTable can do that.

On the other hand, storing 25-30 "extra" TB of data within a SQL Server database will be a challenge to maintain. If this is just for compliance/auditing purposes (i.e., the probability of somebody actually looking at a particular file is low) and files aren't inserted very often, I'd probably stick with the pointers-to-files approach. But if you need access frequently or there are a lot of changes, the advantage goes to FileTable because it takes care of the pointer-handling part of the process.


If all you need is a key/value store, you might want to look at some of the NOSQL options as an RDBMS may be overkill. If you stay with SQL Server, you should investigate the "table partitions" feature, which can help organize very large tables, and table/data compression feature, which can reduce the amount of space that data takes on disk and in memory.


For SQL Server 2012 you should evaluate row compression:


If your typical 100kb file compresses well you could find yourself with a much smaller database.

  • 1
    No; LOB values are not compressed. From that link: "Because of their size, large-value data types are sometimes stored separately from the normal row data on special purpose pages. Data compression is not available for the data that is stored separately." The files would have to be compressed by some other means before being inserted into a row. – Jon Seigel Nov 1 '12 at 22:40
  • If the contents of the text file are stored as text in the database then compression will work fine. – Jimbo Nov 2 '12 at 11:42
  • 1
    Only data that fits on the data page. At 100 KB per column value, it will mostly row overflow. I tested with create table blarg(a int identity primary key, b varchar(MAX) default replicate(replicate('a', 8000), 8000)); set nocount on; GO insert into blarg default values; go 10000 (this is about 80 MB of data) then used the compression wizard in Management Studio: it gives roughly the same numbers before/after. – Jon Seigel Nov 2 '12 at 13:31
  • @Jimbo: sorry, but as Jon already pointed, the page compression is available only for the data page. Not for LOBs. – Marian Nov 6 '12 at 21:35

If you want to do efficient queries and have real time access to your records, perhaps you want to stay away from compression or references to external disk storage... ugh...

Here are some directives I was able to find, you might also find them useful:

Construct an index for each field that requires range queries. Use a B+ tree to implement the index. A B+ tree organizes sorted data for efficient insertion, retrieval and removal of records. Each record is identified by a key (for this problem, it is the field value). Since it is a dynamic, multilevel index, finding the beginning of the range depends only on the height of the tree, which is usually quite small. Record references are stored in the leaves, sorted by the key. Additional records can be found by following a next block reference. Records will be sequentially available until the key value reaches the maximum value specified in the query. Thus, runtimes will be dominated by the number of elements in a range. Avoid using trees that store data at interior nodes, as traversing the tree will be expensive since it won’t be resident in memory.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.