Following extensive performance testing and analysis, the following is the conclusion:
- It is more effective to save your images or documents in a database's VARBINARY column if they are normally less than 256 KB in size.
- Storage in the filesystem is more effective if your images or documents are frequently larger than 1 MB; also, thanks to SQL Server 2008's FILESTREAM characteristic, they remain subject to transactional control and a part of the database.
- Depending on your use, it's kind of a toss-up between those two.
If you choose to store your photos in a SQL Server table, I would strongly advise utilising a distinct table for that purpose. Do not maintain the employee photo in the employee table. If you don't always need to choose the employee photo as part of your queries, the Employee table can be slim, mean, and incredibly efficient.
For an introduction to filegroups, see Files and Filegroup Architecture. In essence, you would either design your database from the start with a separate filegroup for huge data structures or add a second filegroup later. Give it the moniker "LARGE DATA."
Now, whenever you have a new table to create which needs to store VARCHAR(MAX) or VARBINARY(MAX) columns, you can specify this file group for the large 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
I hope this helps you.
