Store file on file system or as varbinary (MAX) in SQL Server

Question

I understand that there is a lot of controversy as to whether it is wrong practice to store files as blob in the database, but I just want to understand if this makes sense in my case.

I am creating an ASP.NET application that is used internally in a large company, where users should be able to attach files to the "job" in the system. These files are usually PDF or Word documents, they may never exceed a couple mb.

I create a new table as follows:

ID (int) JobID (int) FileDescription (nvarchar(250)) FileData (varbinary(MAX) 

Is using varbinary(MAX) perfect here, or should I store the file path and just store the file in the file system somewhere?

Answer 1

There's a really nice article from Microsoft Research called In Blob or Not To Blob .

Their conclusion after a large number of performance tests and analysis is as follows:

• if your images or documents are usually below 256 KB, storing them in the VARBINARY column of the database is more efficient

• if your images or document are usually larger than 1 MB, they are more efficiently stored in the file system (and with the SQL Server 2008 FILESTREAM attribute, they are still under transaction control and part of the database)

• between these two, it depends a little on your use

If you decide to place your photos in a SQL Server table, I highly recommend using a separate table to store these images - do not store the employee photo in the employee table - keep them in a separate table. Thus, the Employee table can remain sparse, medium and very efficient, assuming that you do not always need to select an employee photo as part of your queries.

For filegroups, check out Files and Filegroup Architecture to log in. Basically, you will either create your database with a separate filegroup for large data structures from the very beginning, or add an additional filegroup later. Let me call it LARGE_DATA .

Now that you have a new table to create that should store the VARCHAR(MAX) or VARBINARY(MAX) columns, you can specify this group of files for big data:

  CREATE TABLE dbo.YourTable (....... define the fields here ......) ON Data -- the basic "Data" filegroup for the regular data TEXTIMAGE_ON LARGE_DATA -- the filegroup for large chunks of data 

Check out the MSDN entry in filegroups and play with it!