If I query the SQL Server VarBinary (MAX) columns, will the entire file in all memory be loaded?

Question

My application uses SQL Server 2008, and we need to add functionality for users to be able to save any file of any size in the database table. I created a table similar to this format:

• FileStorageID - int (indentity PK) (This is also a foreign key for another table)
• FileData - varbinary(max)
• FileName - varchar(1000)

The FileStorage table has a one-to-one relationship with another Documentation table. The idea is that users can write or write text, upload a file, or both.

This table is as follows:

• DocumentationID - int (identity PK , FK in table "IrrelevantInterestingObject")
• Text - varchar(max)
• FileStorageID - int (FK to the aforementioned FileStorage table)

My question is this: when I query the Documentation table using Entity Framework 5 and the file is present in the database, will the entire file be stored in memory? If so, what would be a reasonable threshold before a noticeable performance issue occurs?

Answer 1

If you query the Documentation table, the data from the associated FileStorage table FileStorage not be loaded until you load this relationship through either impatient, explicit, or lazy loading. After you use any such method or FileStorage request FileStorage direct integer FileData for each loaded record will be loaded into memory.